MySQL数据类型归纳

参考资料

MySQL官方文档
anxpp的博客

概述

以MySQL5.7为例，根据官方文档的分类，数据类型可分为以下3种：
1. 数值类型（Numeric Type）
2. 时间日期类型（Date and Time Type）
3. 字符串类型（String Type）

数值类型

• 关于UNSIGNED和ZEROFILL

UNSIGNED和ZEROFILL都用于数值类型。
UNSIGNED表示无符号，MySQL中默认都是用的UNSIGNED，如果插入一个负数，最终存储结果会是0。

When you use subtraction between integer values where one is of type UNSIGNED, the result is unsigned unless the NO_UNSIGNED_SUBTRACTION SQL mode is enabled.

对含有UNSIGNED类型的两个整型数值做减法，结果也是UNSIGNED的，除非将sql_mode设置为NO_UNSIGNED_SUBTRACTION，例子如下：

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';mysql> SELECT CAST(0 AS UNSIGNED) - 1;+-------------------------+| CAST(0 AS UNSIGNED) - 1 |+-------------------------+|                      -1 |+-------------------------+

ZEROFILL表示填充零。

• BIT(M)

A bit-value type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.

BIT(M)可用于存储位字段值，即二进制，M表示可以存储的位数，M的取值为1~64，默认为1。

If you assign a value to a BIT(M) column that is less than M bits long, the value is padded on the left with zeros. For example, assigning a value of b’101’ to a BIT(6) column is, in effect, the same as assigning b’000101’.

如果插入的位数比设定的位数小，就会在该二进制数的左侧补充0。例如像BIT(6)的字段插入一个101，最终表中存储的会是000101。

• TINYINT[(M)] [UNSIGNED] [ZEROFILL]
• SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
• MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
• INT[(M)] [UNSIGNED] [ZEROFILL]
• INTEGER[(M)] [UNSIGNED] [ZEROFILL] 与INT同义
• BIGINT[(M)] [UNSIGNED] [ZEROFILL]

以上6种数据类型都是用来存储整型数值的,各存储范围如下：

数据类型 字节数（Bytes） 最小值（signed） 最大值（signed） 最小值（unsigned） 最大值（unsigned） tinyint 1 -128 127 0 255 smallint 2 -32768 32767 0 65535 mediumint 3 -8388608 8388607 0 16777215 int 4 -2147483648 2147483647 0 4294967295 bigint 8 -9223372036854775808 9223372036854775807 0 18446744073709551615

- DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
- NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL] 与DECIMAL同义

A packed “exact” fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the - sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.

DECIMAL(M,D)与NUMERIC(M,D)同义，用于存储定点数，M表示整数部分与小数点部分的长度总和，D表示小数点部分的长度。M的取值范围是0 ~ 65，默认为10；D的取值范围是0 ~ 30，默认为0。
官方例子如下：

salary DECIMAL(5,2)

DECIMAL(5,2)的取值范围是-999.99 ~ 999.99。
存储时整数部分超出，MySQL就会报错；
存储时小数部分超出，分两种情况：
1. 四舍五入后，整数部分没有超出，警告但能成功操作，小数超出部分被舍去后存入数据库，如999.991保存为999.99；
2. 四舍五入后，整数部分超出，MySQL报错，如插入999.995。

• FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

A small (single-precision) floating-point number. Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38.

FLOAT(M,D)用于存储较小的浮点数，存储范围为-3.402823466E+38 ~ -1.175494351E-38和1.175494351E-38 ~ 3.402823466E+38。
M表示整数部分与小数部分的长度总和，D表示小数部分长度，例如向FLOAT(7,4)插入数值999.00009，保存结果将是999.0001。

• DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

A normal-size (double-precision) floating-point number. Permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308.

DOUBLE(M,D)用于存储常规大小的浮点数，存储范围为-1.7976931348623157E+308 ~ -2.2250738585072014E-308和2.2250738585072014E-308 ~ 1.7976931348623157E+308。

• REAL[(M,D)] [UNSIGNED] [ZEROFILL]

与DOUBLE(M,D)同义。

时间日期类型

• DATE

A date. The supported range is ‘1000-01-01’ to ‘9999-12-31’. MySQL displays DATE values in ‘YYYY-MM-DD’ format, but permits assignment of values to DATE columns using either strings or numbers.

DATE用于存储不包含具体时间的日期，范围为1000-01-01 ~ 9999-12-31。
允许使用字符串或者数字对DATE类型的字段进行赋值，例如要存入一个1000-01-01，可以使用1000-01-01的字符串或者是10000101的数字。

• DATETIME[(fsp)]

A date and time combination. The supported range is ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’. MySQL displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS[.fraction]’ format, but permits assignment of values to DATETIME columns using either strings or numbers.

DATETIME(fsp)用于存储具体时间与日期，范围为1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999，同样允许用字符串或数字对DATETIME类型的字段进行赋值。

An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.

fsp表示时间部分的小数点长度，取值范围为0 ~ 6，默认为0。例如DATETIME(0)表示秒部分不含小数，其存储范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。

• TIMESTAMP[(fsp)]

A timestamp. The range is ‘1970-01-01 00:00:01.000000’ UTC to ‘2038-01-19 03:14:07.999999’ UTC. TIMESTAMP values are stored as the number of seconds since the epoch (‘1970-01-01 00:00:00’ UTC). A TIMESTAMP cannot represent the value ‘1970-01-01 00:00:00’ because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing ‘0000-00-00 00:00:00’, the “zero” TIMESTAMP value.

TIMSTAMP(fsp)用于存储时间戳，范围为1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999。TIMESTAMP类型的数据会被存储与1970-01-01 00:00:00 UTC相差的秒数。

An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.

fsp表示时间部分的小数点长度，取值范围为0 ~ 6，默认为0。

• 用INT存储时间戳

首先，INT的字节数为4，DATETIME为8，TIMESTAMP为4（MySQL5.6.4之后，DATETIME和TIMESTAMP的存储需求视时间精度而定，但分别至少需要5个字节和4个字节），从空间占用的角度来考虑，优先选择INT和TIMESTAMP；
再从可存储的范围来看，TIMESTAMP最多能存2038-01-19 03:14:07.999999，INT的存储范围要比这个大得多；
此外，INT类型在进行条件查询的时候可以使用between，且计算方便，但可读性较差，需要用date函数进行转换。

• TIME[(fsp)]

A time. The range is ‘-838:59:59.000000’ to ‘838:59:59.000000’. MySQL displays TIME values in ‘HH:MM:SS[.fraction]’ format, but permits assignment of values to TIME columns using either strings or numbers.

TIME(fsp)用于存储时间，范围为-838:59:59.000000 ~ 838:59:59.000000，允许使用字符串或数字进行赋值。

An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.

fsp表示小数点部分长度，取值范围为0 ~ 6，默认为0。

• YEAR[(4)]

A year in four-digit format. MySQL displays YEAR values in YYYY format, but permits assignment of values to YEAR columns using either strings or numbers. Values display as 1901 to 2155, and 0000.

YEAR(4)用于存储年份，存储范围为1901 ~ 2155 以及 0000。
在MySQL5.7.5之后，只支持YEAR(4),之前版本可以使用YEAR(2)。

字符串类型

• CHAR[(M)]

A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length in characters. The range of M is 0 to 255. If M is omitted, the length is 1.

CHAR(M)用于存储定长的字符串，存入的字符串长度不足M时，右侧会用空格填充。M表示存储的字符串长度，取值范围为0 ~ 255，默认为1。

Trailing spaces are removed when CHAR values are retrieved unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

当CHAR类型的数据被检索时，右侧的空格会被删除，除非将sql_mode设置为PAD_CHAR_TO_FULL_LENGTH，例子如下：

mysql> CREATE TABLE t1 (c1 CHAR(10));Query OK, 0 rows affected (0.37 sec)mysql> INSERT INTO t1 (c1) VALUES('xy');Query OK, 1 row affected (0.01 sec)mysql> SET sql_mode = '';Query OK, 0 rows affected (0.00 sec)mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;+------+-----------------+| c1   | CHAR_LENGTH(c1) |+------+-----------------+| xy   |               2 |+------+-----------------+1 row in set (0.00 sec)mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';Query OK, 0 rows affected (0.00 sec)mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;+------------+-----------------+| c1         | CHAR_LENGTH(c1) |+------------+-----------------+| xy         |              10 |+------------+-----------------+1 row in set (0.00 sec)

• VARCHAR(M)

A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters.

VARCHAR(M)用于存储可变长的字符串。M表示可存储的最大字符串长度，其取值范围为0 ~ 65535。实际有效的长度取决于这一行数据的存储大小（即由一行的所有字段共享的65535bytes）与使用的字符编码。比如，utf8编码下的一个字符可以占用3bytes，那么一个VARCHAR类型的字段最多可以存储21844个utf8编码的字符。

MySQL follows the standard SQL specification, and does not remove trailing spaces from VARCHAR values.

MySQL遵循标准SQL规范，VARCHAR类型的字段在存储和检索的时候不会移除尾部的空格。

与CHAR相比，VARCHAR类型的字段在存储时不会填充空格，并且除了保存需要的字符串之后，还要用一个字节来存储该字段的长度（如果长度超过255，就需要用两个字节）。

• BINARY(M)

The BINARY type is similar to the CHAR type, but stores binary byte strings rather than nonbinary character strings. M represents the column length in bytes.

BINARY与CHAR类似，但存储的不是字符串，而是二进制串。M表示存储的字节数。
相同的，BINARY存储的是定长的二进制串，当长度不足时，在尾部用0x00填充，例如对BINARY(3)插入一个’a’，存储结果为’a\0\0’，如下：

mysql> CREATE TABLE t (c BINARY(3));Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO t SET c = 'a';Query OK, 1 row affected (0.01 sec)mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t;+--------+---------+-------------+| HEX(c) | c = 'a' | c = 'a\0\0' |+--------+---------+-------------+| 610000 |       0 |           1 |+--------+---------+-------------+1 row in set (0.09 sec)

• VARBINARY(M)

The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than nonbinary character strings. M represents the maximum column length in bytes.

VARBINARY与VARCHAR类似，但存储的是变长的二进制串。M表示可以存储的最大字节数。

• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLOB

BLOB用于存储二进制字符串，相当于更大集合的BLOB，四种BLOB类型的区别在于可存储的最大字节数，依次为2^8 - 1、2^16 - 1、2^24 - 1、2^32 - 1。

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

TEXT用于存储字符字符串，相当于更大集合的VARCHAR，四种TEXT类型的区别在于可存储的最大字符数，依次为2^8 - 1、2^16 - 1、2^24 - 1、2^32 - 1，实际长度还取决于字符编码的类型。

• ENUM(‘value1’,’value2’,…)

ENUM类型用于存储字符串的数据，其值来自于value1、value2、……、NULL、”（空字符串），其中”被视为错误值。关于NULL与”需要注意：
1. 如果插入一个非法值（value1、value2、……、NULL、”之外的字符串），插入的结果将是一个”，这个空字符串与普通的空字符串不同，其对应的索引是0。
2. 如果将ENUM字段设置为允许NULL，NULL也将作为该列的一个有效值，且默认值为NULL。如果ENUM字段设置为NOT NULL，默认值将是第一个有效值，也就是value1。

如何使用ENUM：

CREATE TABLE shirts (    name VARCHAR(40),    size ENUM('x-small', 'small', 'medium', 'large', 'x-large'));INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),  ('polo shirt','small');SELECT name, size FROM shirts WHERE size = 'medium';+---------+--------+| name    | size   |+---------+--------+| t-shirt | medium |+---------+--------+UPDATE shirts SET size = 'small' WHERE size = 'large';COMMIT;

关于ENUM的索引：

值 索引 NULL NULL ”（空字符串） 0 ‘Mercury’ 1 ‘Venus’ 2 ‘Earth’ 3

1. ENUM类型的值在进行数学运算时，用的是其对应的索引值，例如：

mysql> SELECT enum_col+0 FROM tbl_name;

上述语句可以查询出ENUM列对应的的索引值。
2. 如果在存储一个ENUM值时，使用的是数字，最终存储结果是该数字对应的有效值，例如：

mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');mysql> SELECT * FROM t;+---------+| numbers |+---------+| 1       || 2       || 2       |+---------+

其中第一个插入的2是"1"这个字符对应的索引值。

• SET(‘value1’,’value2’,…)

SET用于存储字符串类型数据，可以有0个或多个值，其有效值包括value1、value2、……、NULL、”。例如对于一个SET(‘one’, ‘two’)，其有效值如下：

NULL'''one''two''one,two'

SET最多可以包含64个有效值，SET有效值成员尾部的空格在创建表的时候会被删除。
与ENUM类似，SET中的每个有效值都有对应的数值。
对于一个SET(‘a’,’b’,’c’,’d’)，各个有效值成员对应的数值如下：

有效值成员 十进制数 二进制数 ” 0 0000 ‘a’ 1 0001 ‘b’ 2 0010 ‘c’ 4 0100 ‘d’ 8 1000

1. 用SUM和AVG分别可以计算SET列中的有效值成员总数与平均值；
2. 对SET列进行数学运算时使用其对应的数值进行计算；
3. 若对SET列插入数字，存储结果将是该数字对应的有效值集合，例如插入9的结果为’a,d’。

例子如下：

mysql> SELECT set_col FROM test;+---------+| set_col |+---------+|         || d       || a,b,c   |+---------+3 rows in setmysql> INSERT INTO test(set_col) VALUES (1),(9),('a,c');Query OK, 3 rows affectedRecords: 3  Duplicates: 0  Warnings: 0mysql> SELECT set_col FROM test;+---------+| set_col |+---------+|         || d       || a,b,c   || a       || a,d     || a,c     |+---------+6 rows in setmysql> SELECT set_col+0 FROM test;+-----------+| set_col+0 |+-----------+|         0 ||         8 ||         7 ||         1 ||         9 ||         5 |+-----------+6 rows in setmysql> SELECT SUM(set_col) FROM test;+--------------+| SUM(set_col) |+--------------+|           30 |+--------------+1 row in setmysql> SELECT AVG(set_col) FROM test;+--------------+| AVG(set_col) |+--------------+|            5 |+--------------+1 row in set

如果插入重复的有效值，MySQL会忽略重复值：

mysql> INSERT INTO myset (col) VALUES -> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');Query OK, 5 rows affected (0.01 sec)Records: 5  Duplicates: 0  Warnings: 0mysql> SELECT col FROM myset;+------+| col  |+------+| a,d  || a,d  || a,d  || a,d  || a,d  |+------+5 rows in set (0.04 sec)

如果插入一个非法值（不在创建表时设定的有效值序列内），MySQL会发出警告并忽略该非法值：

mysql> INSERT INTO myset (col) VALUES ('a,d,d,s');Query OK, 1 row affected, 1 warning (0.03 sec)mysql> SHOW WARNINGS;+---------+------+------------------------------------------+| Level   | Code | Message                                  |+---------+------+------------------------------------------+| Warning | 1265 | Data truncated for column 'col' at row 1 |+---------+------+------------------------------------------+1 row in set (0.04 sec)mysql> SELECT col FROM myset;+------+| col  |+------+| a,d  || a,d  || a,d  || a,d  || a,d  || a,d  |+------+6 rows in set (0.01 sec)

对SET列进行条件查询时，可以使用FIND_IN_SET()函数或者LIKE比较符：

mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';

第一个语句表示查询SET列包含’value’的行；第二个语句类似，同样查询SET列包含’value’的行，但不一样的是SET列包含类似’value’的成员的行也会被检索到，例如查询’a’时，成员’sa’同样满足条件。

此外还可以使用如下条件查询：

mysql> SELECT * FROM tbl_name WHERE set_col & 1;mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';

其中第一个语句表示查询SET列包含数字1对应的有效值成员的行；使用第二个语句时需要注意，’val1,val2’与’val2,val1’是不同的。