MySQL支持的数据类型

数值类型

整数

整数类型	字节	最小值	最大值
TINYINT	1	有符号 -128,无符号 0	有符号 127,无符号255
SMALLINT	2	有符号 -32768,无符号 0	有符号 32767,无符号 65536
MEDIUMINT	3	有符号 -8388608,无符号 0	有符号 8388607, 无符号 1677215
INT,INTEGER	4	有符号 -2147483648, 无符号 0	有符号 2147483647,无符号 4294967295
BIGINT	8	有符号 -9223372036854775808, 无符号0	有符号 9223372036854775807, 无符号 18446744073709551615

显示宽度

MySQL支持在整数类型名称后面的小括号内指定显示宽度，一般配合zerofill使用。也就是在数字位数不够的空间用字符'0'填满。如果插入大于宽度限制的值，不会对插入的数据有热呢影响，还是按照类型的实际精度进行保存。

mysql> alter table t1 modify id1 int(5) zerofill

mysql> select * from t1;
+---------+
| id1     |
#---------+
|   00001 |
| 1111111 |
+---------+

符号

所有的整数类型都有一个可选属性UNSIGNED(无符号)，如果需要在字段里面保存非负数或者需要较大的上限值时，可选用此选项。

如果一个列指定位zerofill，则MySQL自动为该列添加UNSIGNED属性。

自增

整数类型在需要产生唯一标识符或顺序值时，可使用AOTU_INCREMENT属性。值一般从1开始，每行增加1.

一个表最多只能有一个AUTO_INCREMENT列。对于任何想要使用AUTO_INCREMENT的列，应该定义为NOT NULL，并定义为PRIMARY KEY或定义为UNIQUE键。

小数

浮点数

浮点类型	字节	最小值	最大值
FLAOT	4	±1.175494315E-38	±3.402823466E+38
DOUBLE	8	±2.2250738585072014E-308	± 1.7976931348623157E+308

定点数

定点数类型	字节	描述
DEC(M,D),DECIMAL(M,D)	M+2	最大取值范围与DOUBLE相同，给定DECIMAL的有效取值范围由M和D决定

精度

浮点数和定点数都可以用类型名称后加"(M,D)"的方式来进行表示，"(M,D)"表示该值一共显示M位数字(整数位+小数位)，其中D位位于小数点后面，M和D又称为精度和标度。

浮点数后面跟"(M,D)"的用法是非标准用法。

float和double在不制定精度时，默认会按照实际的精度(由实际的硬件和操作系统决定)来显示，而decimal在不指定精度时，默认的整数位为10，默认的小数位为0。

数据存放

定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

位类型

位类型	字节	最小值	最大值
BIT(M)	1-8	BIT(1)	BIT(8)

对于BIT类型，用于存放位字段值(二进制)，BIT(M)可以用来存放多位二进制数，M范围从1-64，如果不写则默认为1位。对于位字段，直接使用SELECT命令将不会看到结果，可以用bin()或者hex()函数进行读取。

select hex(column) from table;

日期时间类型

日期和时间类型	字节	最小值	最大值
DATE	4	1000-01-01	9999-12-31
DATETIME	8	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	4	19700101080001	2038年的某个时刻
TIME	3	-838:59:59	838:59:59
YEAR	1	1901	2155

这些数据类型的主要区别如下

• 如果只表示年，通常用YEAR表示

• 如果要用来表示年月日，通常用DATE表示

• 如果要用来表示年月日时分秒，通常用DATETIME表示

• 如果只用来表示时分秒，通常用TIME来表示

• 如果需要经常插入或者更新日期为当前系统时间，通常使用TIMESTAMP

TIMESTAMP和DATETIME的区别

• TIMESTAMP支持的时间防范围小

• TIMESTAMP的插入和查询都受当地时区的影响，更能反映出实际的日期。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差。

• TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大。

允许插入的格式

• YYYY-MM-DD HH:MM:SS或YY-MM-DD HH:MM:SS格式的字符串。允许不严格语法，即任何标点符都可以用做日期部分或时间部分之间的间隔符。

• YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的没有间隔符的字符串

• YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字

• 函数返回的结果，其值适合DATETIME、DATE或TIMESTAMP上下文。

字符串类型

基于MySQL5.0版本

字符串类型	字节	描述及存储需求
CHAR(M)	M	M为0-255之间的整数
VARCHAR(M)		M为0-65535之间的整数，值的长度+1个字节
TINYBLOB		允许长度0-255字节，值的长度+1个字节
BLOB		允许长度0-65535字节，值的长度+2个字节
MEDIUMBLOB		允许长度0-167772150字节，值的长度+3个字节
LONGBLOB		允许长度0-4294967295字节，值的长度+4个字节
TINYTEXT		允许长度0-255字节，值的长度+2字节
TEXT		允许长度0-65535字节，值的长度+2字节
MEDIUMTEXT		允许长度0-167772150字节，值的长度+3字节
LONGTEXT		允许长度0-4294967295字节，值的长度+4字节
VARBINARY(M)		允许长度0-M个字节的边长字节字符串，值的长度+1个字节
BINARY(M)		允许长度0-M个字节的定长字节字符串

TEXT和BLOB

BLOB保存二进制数据，TEXT保存字符数据。

CHAR和VARCHAR类型

CHAR列的长度固定为创建表时声明的长度，长度可以为从0-255的任何值，不论存储多少个字符，都会占用声明时的长度空间。

VARCHAR列的值为可变长字符串，长度可以制定为0-65535之间的值。存储的长度按实际字符数+1。

在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格。

mysql> CREATE TABLE vc(v VARCHAR(4), c CHAR(4));
mysql> INSERT INTO vc VALUES ('ab  ','ab  ');
mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+

BINARY和VARBINARY类型

BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。

ENUM类型

枚举类型的值范围需要在创建表时通过枚举方式显式指定，对1-255个成员的枚举需要1个字节存储;对于255-65535个成员，需要2个字节存储。最多允许又65535分成员。ENUM类型只允许从值集合中选取单个值，而不能一次取多个值。

mysql> create table t (gender enum('M','F'));
mysql> INSERT INTO t VALUES('M'),('1'),('f'),(NULL);
mysql> select * from t;
+--------+
| gender |
+--------+
| M      |
| M      |
| F      |
| NULL   |
+--------+

从上面例子中，可以看出ENUM类型是忽略大小写的。

SET类型

SET和ENUM类型非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。

• 1-8成员的集合，占1个字节

• 9-16成员的集合，占2个字节

• 17-24成员的集合，占3个字节

• 25-32成员的集合，占4个字节

• 33-64成员的集合，占8个字节

mysql> CREATE TABLE t3(col set ('a','b','c','d'));
mysql> INSERT INTO t3 values('a,b'),('a,d,a'),('a');
mysql> select * from t3;
+------+
| col  |
+------+
| a,b  |
| a,d  |
| a    |
+------+

SET类型可以从允许值集合中选择任意1个或多个元素进行组合，所以对于输入的值只要是在允许值的组合范围内，都可以正确地注入到SET类型的列中。对于包含重复成员的集合将只取一次。