4. Data types and operators of MySQL
4.1. introduction of MySQL data types
MySQL supports a variety of data types, mainly numeric, date / time, and string types.
Numeric data type:
Include integer types TINYINT, SMALLINT, MEDIUMINT, INT, BIFINT,
Floating point small data types FLOAT, DOUBLE
Fixed point decimal type DECIMAL
Date / time type:
YEAR 、 TIME 、 DATE 、 DATETIME 、 TIMESTAMP
String type:
CHAR 、 VARCHAR 、 BINARY 、 VARBINARY 、 BLOB 、 TEXT 、 ENUM 、 SET
Integer class
Integer data types in MySQL
The type name indicates that the storage requirement is 1 byte SMALLINT for integers with very small TINYINT
Small integer 2 bytes MEDIUMINT
Medium-sized integer 3 bytes INT
Normal size integer 4 bytes BIGINT
Large integer 5 bytes
The range of values for different integer types
Data type signed unsigned TINYINT-128~1270-255SMALLINT-32768~327670~65535MEDIUMINT-8388608~83886070~16777215INT-2147483648~21474836470~4294967295BIGINT-9223372036854775808~92233720368547758070~18446744073709551615mysql > CREATE TABLE tmp1 (x TINYINT,y SMALLINT,z MEDIUMINT,m INT,n BIGINT); Query OK, 0 rows affected (0.05sec) mysql > DESC tmp1 +-+ | Field | Type | Null | Key | Default | Extra | +-+-- -+ | x | tinyint (4) | YES | | NULL | | y | smallint (6) | YES | | NULL | | z | mediumint (9) | YES | | NULL | | m | int (11) | YES | | NULL | n | bigint (20) | YES | NULL | | + -+-+ 5 rows in set (0.00 sec)
Floating point number type and fixed point number type
Data type description storage requirements FLOAT
Single-precision floating point 4-byte DOUBLE
Double-precision floating-point 8-byte DECIMAL (MMagne D)
Compressed "strict" fixed points M 2 bytes mysql > CREATE TABLE tmp2 (x FLOAT (5 sec 1), y DOUBLE (5 pen 1), z DECIMAL (5 pen 1)); Query OK, 0 rows affected (0.02 sec) mysql > DESC tmp2 +-+ | Field | Type | Null | Key | Default | Extra | +-+-- -+ 3 rows in set (0.00 sec)
Date and time type
Type name date format date range storage requirements YEARYYYY1901~21551 bytes TIME
HH:MM:SS-838:59:59~838:59:593 byte DATEYYYY-MM-DD1000-01-01-01 "9999-12-313 byte DATETIME
YYYY-MM-DD HH:MM:SS
1000-01-01 00VOG 0000VOUR
9999-12-31 23:59:59
8-byte TIMESTAMP
YYYY-MM-DD HH:MM:SS
1970-01-01 00:00:01 utc~
2038-01-19 03:14:07 utc
4 byt
Text string type
Type name description Storage requirements CHAR (M) fixed length non-binary string M bytes, 1
