Mariadb learning summary (3): data types

Data type

Data type-> is an abstraction of a data classification with the same attributes and properties.

For example:
A string is a string composed of characters... In computers, substrings can be divided and new characters can be added at the end of the string. However, such operations can only operate on string data, not on string data. Operate on integers.
Numeric type, the Arabic numerals we are most exposed to, can be used for arithmetic operations, logical operations and other operations

Data types in Mysql

MySQL supports multiple types, which can be roughly divided into three categories: numerical, date/time and string (character) types.

Numeric type

1. For integer types, you can limit their length, the format is as follows:

整数类型[(M)] [SIGNED | UNSIGNED | ZEROFILL]

M is the number of digits in the number. For example, TINYINT(3) can only store three digits, and the number of digits should not exceed the range it can represent
SIGNED: The default is signed number
UNSIGNED: specified as an unsigned number
ZEROFILL: When M bits are not satisfied, the front is filled with 0 and becomes an unsigned number

2. For floating point numbers, the overall number of digits and the number of decimal places can be limited

（FLOAT|DOUBLE）[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]

M is the total number of digits, D is the number of digits after the decimal point
ZEROFILL has the same effect as UNSIGNED for floating point types

MariaDB [mydb]> DESC t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| c1    | float(5,2)   | YES  |     | NULL    |       |
| c2    | double(10,3) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [mydb]> INSERT INTO t1 VALUES(23.5,12.34566777);
Query OK, 1 row affected (0.01 sec)

MariaDB [mydb]> SELECT * FROM t1;   //这里可以看到，在其后补了0
+-------+--------+
| c1    | c2     |
+-------+--------+
| 23.50 | 12.346 |
+-------+--------+
1 row in set (0.00 sec)

Date and Time Type

Format abbreviation: Date and time format, in addition to the standard format, it also supports loose formats.

Create a test table with the following structure:

MariaDB [mydb]> desc datetable;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| c1    | date      | YES  |     | NULL              |                             |
| c2    | time      | YES  |     | NULL              |                             |
| c3    | datetime  | YES  |     | NULL              |                             |
| c4    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+

For DATE type: YY-MM-DD, YYMMDD, YYYY/MM/DD

MariaDB [mydb]> INSERT INTO datetable(c1) VALUES('2018-01-01'),('18-01-01'),
    -> ('180101'),('2018/01/01'),(180101);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [mydb]> SELECT c1 FROM datetable;
+------------+
| c1         |
+------------+
| 2018-01-01 |
| 2018-01-01 |
| 2018-01-01 |
| 2018-01-01 |
| 2018-01-01 |
+------------+
5 rows in set (0.00 sec)

For TIME type: 'D HH:MM:SS', 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH', 'SS', 'HHMMSS'
D stands for day, which is TIME+D*24

MariaDB [mydb]> INSERT INTO datetable(c2) VALUES
    -> ('12:20:20'),('1 12:20:20'),
    -> ('12:20'),('1 12'),('20'),('122020'), 
    -> (122020);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

MariaDB [mydb]> SELECT c2 FROM datetable;
+----------+
| c2       |
+----------+
| 12:20:20 |
| 36:20:20 |
| 12:20:00 |
| 36:00:00 |
| 00:00:20 |
| 12:20:20 |
| 12:20:20 |
+----------+
7 rows in set (0.00 sec)

For the two formats DATETIME and TIMESTAMP, they are the abbreviations of the above time abbreviation format, for example:

20180101122020 -> '2018-01-01 12:20:20'

For the format TIMESTAMP, which is more commonly used, it should be said that it stores the number of milliseconds from '1970-01-01 00:00:00' to the storage time. Its default value can be CURRENT_TIMESTAMP or its synonym: CURRENT_TIMESTAMP() , NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP,LOCALTIMESTAMP()

String type

CHAR and VARCHAR types are similar, but they are saved and retrieved differently. They also differ in terms of their maximum length and whether trailing spaces are preserved. No case conversion is performed during storage or retrieval.

BINARY and VARBINARY classes are similar to CHAR and VARCHAR, except that they contain binary strings instead of non-binary strings. That is, they contain byte strings rather than character strings. This means that they do not have a character set, and sorting and comparison are based on the numeric value of the column value bytes.

A BLOB is a binary large object that can hold a variable amount of data:
There are 4 BLOB types: TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB. They only differ in the maximum length they can hold a value.
There are 4 TEXT types: TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. These correspond to 4 BLOB types, with the same maximum length and storage requirements.

Other types

Enumeration type: ENUM('value1','value2',...)
Storage bytes: Because it stores element numbers, elements 0-255 only occupy one byte, while 255-65535 occupy two elements

The above is the detailed content of Mariadb learning summary (3): data types. For more information, please follow other related articles on the PHP Chinese website!