浅析MySQL数据类型

本文基于MySQL5.6版本进行描述
从大的方面来讲，MySQL的数据类型分为4种

• 数值类型
• 日期和时间
• 字符串类型
• 空间数据类型

为便于后续文章标记，先罗列下类型描述。就像是数学公式一样，先描述字母代表的含义。

• M 对于数值类型，代表最大显示宽度;对于浮点型或者定点类型数据，代表可以存储的总位数(精度);对于字符串类型，代表最大长度。M的最大值取决于具体的数据类型。
• D 适用于浮点型或定点型数据，代表小数点后面的位数。最大取值为30，但不能超过M-2。
• fsp 适用于日期时间类型数据。表示小数秒精度，即秒小数点后面的数值位数。如果指定这个值，必须在0到6范围内。省略默认精度为0。(标准的SQL默认值为6)
• [] 中括号内的值代表可选项。

数值类型

数值类型中，M表示最大可显示的宽度。最大显示宽度是255。

BIT[(M)]

M的取值范围是[1,64]，如果不指定M的话，默认值为1。

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

一种微小的整型，有符号取值范围是[-128,127]，无符号取值范围是[0,256]。占用一字节。

BOOL BOOLEAN

这两个类型和TINYINT(1)是等价的。0是false，非0是true。请看例子

mysql> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false                  |
+------------------------+

mysql> SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

mysql> SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

大写的TRUE和FALSE代表1和0。请看例子

mysql> SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true                           |
+--------------------------------+

mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true                          |
+-------------------------------+

mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false                         |
+-------------------------------+

mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false                          |
+--------------------------------+

这后两个的结果是因为2既不等于1也不等于0

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

一种小整型。有符号取值范围是[-32768,32767]，无符号取值范围是[0,65535]。占用两字节。

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

一种中等大小的整型。有符号取值范围是[-8388608,8388607]，无符号取值范围是[0,16777215]。占用三字节。

INT[(M)] [UNSIGNED] [ZEROFILL]

一种正常大小的整型。有符号取值范围是[-2147483648,2147483647]，无符号取值范围是[0,4294967295]。占用四字节。

INTEGER[(M)] [UNSIGNED] [ZEROFILL]

这个类型等价于INT

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

长整型。有符号取值范围是[-9223372036854775808,9223372036854775807]，无符号取值范围是[0,18446744073709551615]。占用八字节。
需要注意以下几点

• 所有的数值运算是通过BIGINT或者DOUBLE实现的。不要使用超过9223372036854775807 (63 bits)的无符号整数，如果超过这个数值，可能有精度损失。
  在以下几种场景，mysql可以处理：

1. 在BIGINT列使用integers 存储大的无符号数；
2. 当col_name 指代BIGINT列时，使用函数MAX(col_name ), MIN(col_name )；
3. 在两个integers类型上使用四则运算(+,-,*,...)

• 你可以使用字符串给BIGINT列赋值，mysql会在写入时做类型转换。
• 在两个integers类型上使用四则运算时要注意类型溢出，当结果大于9223372036854775807时，会得到不可预期的结果。

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

定点类型。M代表所有数值的位数，D代表小数点后面的位数。M不包含小数点和负号。如果不指定D，表示没有小数部分。M的最大取值为65，默认值为10；D的最大取值为30，默认值为0。
如果指定了UNSIGNED，表示不允许负数。
所有在DECIMAL列上的四则运算（+，-，*，/）都是基于65位精度。

DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
这些类型与DECIMAL等价。其中FIXED类型可与其他数据库兼容。

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
单精度浮点型。M代表所有数值的位数，D代表小数点后面的位数。如果不指定M和D的话，存储的值默认值取决于硬件支持情况。单精度浮点数精确到小数点后7位。
如果指定了UNSIGNED，表示不允许负数。
使用FLOAT可能会遇到不可预期的问题，因为FLOAT的所有运算在MySQL内部使用的是双精度。

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

双精度浮点型。M代表所有数值的位数，D代表小数点后面的位数。如果不指定M和D的话，存储的值默认值取决于硬件支持情况。双精度浮点数精确到小数点后15位。
如果指定了UNSIGNED，表示不允许负数。

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]

这些类型与DOUBLE等价。有个例外是：如果开启了 REAL_AS_FLOAT SQL模式，REAL与FLOAT等价。

FLOAT(p) [UNSIGNED] [ZEROFILL]

浮点型数值。p表示精度，单位是比特。在MySQL中，这个值仅仅被当做区分FLOAT和DOUBLE。当p取值范围在[0,24]时，表示缺省M和D的FLOAT类型；当p取值范围在[25,53]时，表示缺省M和D的DOUBLE类型。
提供FLOAT(p)语法仅仅是为了适配ODBC。

日期和时间类型

本节对于DATE和DATETIME的取值范围描述时，支持仅仅意味着这些值可以工作，但是不完全保证。
在MySQL 5.6.4及以上版本，对于 TIME, DATETIME以及TIMESTAMP类型支持小数秒，可达到微秒(秒小数点后6位)级别的精度。使用type_name(fsp)这种语法定义小数秒，其中type_name可以是TIME, DATETIME以及TIMESTAMP，fsp代表小数位的精度。例子：

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

fsp的有效取值范围是[0,6]，如果不指定的话，默认值是0，代表没有小数秒部分（和标准的SQL不一样，这是为了适配老版本的MySQL）。

MySQL 5.6.5引入了扩展的可自动初始化和更新的时间类型。表中的任何TIMESTAMP列都可以自动初始化和更新，而不是老版本MySQL的仅仅支持一个。另外，DATATIME也支持这些特性(可自动初始化和更新)。

DATE

日期。支持的取值范围是'1000-01-01' 到'9999-12-31'。MySQL使用'YYYY-MM-DD'格式来显示DATE类型的值，可以通过字符串或者是整数进行赋值。

DATETIME[(fsp)]

日期和时间的结合。其支持的取值范围是 '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。MySQL使用'YYYY-MM-DD HH:MM:SS[.fraction]'格式来显示DATETIME类型的值，可以通过字符串或者是整数进行赋值。
对于MySQL 5.6.4版本，fsp是可选值，取值0到6之间，代表小数秒的精度。缺省默认是0，代表没有小数秒。
对于MySQL 5.6.5版本，可以使用DEFAULT以及ON UPDATE来修饰DATETIME，表示支持自动初始化和更新。

TIMESTAMP[(fsp)]

时间戳。取值范围是'1970-01-01 00:00:01.000000' UTC 到 '2038-01-19 03:14:07.999999' UTC。TIMESTAMP是用整型秒存储，代表'1970-01-01 00:00:00' UTC到现在经历的时间。
注意TIMESTAMP无法表示时间'1970-01-01 00:00:00'，因为根据上面的描述，这个时间应该是等价于0（经过了0秒），但是0又被'0000-00-00 00:00:00'占用了。所以这是个悖论，干脆定义为无法表示'1970-01-01 00:00:00'。
自MySQL 5.6.4版本，MySQL可以使用fsp表示小数秒。取值0到6，默认值为0表示没有小数秒。
MySQL服务端处理TIMESTAMP取决于explicit_defaults_for_timestamp这个系统参数。
如果这个explicit_defaults_for_timestamp系统参数被激活，表示对于任何使用DEFAULT CURRENT_TIMESTAMP 或者
ON UPDATE CURRENT_TIMESTAM修饰的TIMESTAMP列，都没有自动赋值功能。必须手动赋值，另外任何未显式指定 NOT NULL的列，都允许NULL。
如果这个explicit_defaults_for_timestamp系统参数未被激活，MySQL服务端对TIMESTAMP 做如下处理：

• 除非指定值，否则表中的首个定义了自动赋值的TIMESTAMP列，会被自动赋值为服务端最近修改的时间。当然你也可以明确指定NULL对其赋值来把这个列更新为当前时间，但是这个是取决于TIMESTAMP列没有指定允许NULL值的情况下。
• 可以使用DEFAULT CURRENT_TIMESTAMP and ON UPDATE为TIMESTAMP列定义自动初始化和自动更新。默认情况下，第一列TIMESTAMP肯定有这些特性。对于MySQL 5.6.5版本，任何TIMESTAMP列都可以有这些特性。MySQL 5.6.5之前版本，最多只能有一列才有这个特性。可以为第一列禁止此特性，而将它们分配给其他的TIMESTAMP列。
• explicit_defaults_for_timestamp这个系统参数只有在MySQL 5.6.5版本。

TIME[(fsp)]

时间。取值范围是'-838:59:59.000000' 到 '838:59:59.000000'。MySQL使用'HH:MM:SS[.fraction]'格式显示。支持字符串和数字为其赋值。
自MySQL 5.6.4版本，MySQL可以使用fsp表示小数秒。取值0到6，默认值为0表示没有小数秒。

YEAR[(2|4)]

两位或者是四位表示的年，默认是四位表示。
四位的取值是1901 到 2155，还有0000。
两位的取值是70到69。代表1970到2069.
MySQL使用 YYYY 或者 YY格式表示年，可以使用字符串或者整数赋值。

• 对于时间类型， SUM() 和 AVG() 函数不起使用。如果要用，得转换格式：

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

字符串类型

在任何场景，MySQL在使用CREATE TABLE 以及 ALTER TABLE 操作的时候，会自动把string类型转换为目标数据类型。
许多字符串类型字段定义的时候，可以包含属性描述：字符集或列的排序规则。这些属性也可用于CHAR, VARCHAR, TEXT, ENUM, SET。

CREATE TABLE t
(
    c1 VARCHAR(20) CHARACTER SET utf8,
    c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);

这段代码创建了表。此表定义创建一个名为c1的列，该列具有字符集utf8，该字符集具有默认排序规则，名为c2的列具有latin1字符集和区分大小写的排序规则。
指定 CHARACTER SET为binary属性时，会导致类型转变。
假设有如下代码

CREATE TABLE t
(
  c1 VARCHAR(10) CHARACTER SET binary,
  c2 TEXT CHARACTER SET binary,
  c3 ENUM('a','b','c') CHARACTER SET binary
);

这段代码创建表之后，结果等价于下面这段代码

CREATE TABLE t
(
  c1 VARBINARY(10),
  c2 BLOB,
  c3 ENUM('a','b','c') CHARACTER SET binary
);

• 即 CHAR 变成BINARY；VARCHAR 变成VARBINARY；TEXT 变成BLOB。
• BINARY是这个的简写：默认字符集，同时指定binary排序规则。
• ASCII是这个的简写：CHARACTER SET latin1
• UNICODE是这个的简写： CHARACTER SET ucs2
  字符列的比较和排序规则取决于列排序属性。

[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

一个固定长度的字符串，在存储时总是用空格填充指定的长度。 M代表字符的列长度。 M的范围是0到255.如果省略M，则长度为1。

• 除非启用了PAD_CHAR_TO_FULL_LENGTH SQL模式，否则在获取CHAR值时删除尾随空格。
  MySQL 允许你创建CHAR(0)。这个可以用于如下场景：必须有这一列，但是不使用这个列。比如预留列可以使用这个。这样仅仅占用一个比特位的存储空间，取值有：NULL 和''(空字符串)。

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

可变长度的字符串。 M代表字符中的最大列长度。 M的范围是0到65535。 VARCHAR的有效最大长度取决于最大行大小（65,535字节，在所有列中共享）和使用的字符集。 例如，utf8字符每个字符最多可能需要三个字节，因此使用utf8字符集的VARCHAR列可以被声明为最多21844个字符。
MySQL使用1到2字节前缀存储字符串长度。当长度小于255时采用1字节存储长度；当长度大于255时使用2字节存储长度。

• MySQL并不移除VARCHAR列的结尾空格，VARCHAR默认预定义的字符集为utf8。

BINARY[(M)]

BINARY和CHAR类似。只不过这个类型用于存储二进制格式的字节串。可选的M标识字节的长度，默认为1.

VARBINARY(M)

VARBINARY和VARCHAR类似。只不过这个类型用于存储二进制格式的字节串。可选的M标识字节的长度，默认为1.

TINYBLOB

TINYBLOB列的最大长度是255字节.每个TINYBLOB值使用1字节的长度前缀标识串的长度。

TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

TINYTEXT 列的最大长度是255字符.有效的长度可能小于255，因为可能包含多字节字符。每个TINYTEXT 值使用1字节的长度前缀标识串的长度。

BLOB[(M)]

BLOB列的最大长度是65535字节.每个BLOB值使用2字节的长度前缀标识串的长度。
可选的长度可以指定。指定长度之后，MySQL会使用最小能足够支撑指定长度的BLOB类型来创建列。

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

TEXT列的最大长度是65535字符。有效的长度可能小于65535，因为可能包含多字节字符。每个TEXT值使用2字节的长度前缀标识串的长度。
可选的长度可以指定。指定长度之后，MySQL会使用最小能足够支撑指定长度的TEXT类型来创建列。

MEDIUMBLOB

MEDIUMBLOB列的最大长度是16777215字节.每个MEDIUMBLOB值使用3字节的长度前缀标识串的长度。

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

MEDIUMTEXT 列的最大长度是16777215字符。有效的长度可能小于16777215，因为可能包含多字节字符。每个MEDIUMTEXT 值使用3字节的长度前缀标识串的长度。

LONGBLOB

LONGBLOB列的最大长度是4294967295 or 4GB字节.最大长度取决于包的大小，协议，以及内存。每个LONGBLOB值使用4字节的长度前缀标识串的长度。

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

LONGTEXT 列的最大长度是4294967295 or 4GB字符.有效的长度可能小于4294967295 ，因为可能包含多字节字符。最大长度取决于包的大小，协议，以及内存。每个LONGTEXT 值使用4字节的长度前缀标识串的长度。

ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

枚举值。一个字符串只能有一个取值。为这些其中之一： 'value1', 'value2', ..., NULL 或者是''错误值。
枚举值在MySQL内存采用整型表示。
枚举值最多有65535个元素（实际是少于3000）。一个表在其ENUM和SET列中可以有多于255个唯一的元素列表定义，被视为一个组。

SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

集合。一个字符串对象可以有0个或者多个值。为这些其中之一：'value1', 'value2', ...
集合在MySQL内部以整型表现。
SET列最多可以有64个不同的成员。 一个表在其ENUM和SET列中可以有多于255个唯一的元素列表定义，被视为一个组。

本文只是简要罗列MySQL的基本数据类型，空间类型未做描述。