MySQL数据类型详解

MySQL中定义数据字段的类型对你数据库的优化是非常有意义的。
MySQL支持多种类型，大致可以分为三类：数值、日期(时间)和字符(字符串)类型。
一、数值类型
1、整数类型(精确值)
MySQL支持SQL标准整数类型 INTEGER（或INT）和 SMALLINT。作为一个可扩展标准，MySQL也支持整数类型 TINYINT，MEDIUMINT和 BIGINT。
整数类型的存储范围如下图：

2、定点类型（精确值）
DECIMAL和NUMERIC 类型用来存储精确的数值数据。如：货币类数据精度要求非常重要，常使用 DECIMAL。在MySQL中，NUMERIC被实现为DECIMAL，所以DECIMAL的用法同样适用于 NUMERIC。

DECIMAL声明列的精度和标度通常是指定的，例如：

salary DECIMAL(5,2)

如上，5是精度， 2是规模。精度代表的被存储为值显著数字的数目，标度表示的可以存储小数点后面的位数。MySQL的默认精度为10，默认刻度为0。

标准SQL要求，DECIMAL(5,2)能够存储任何一个五位数和两位小数，所以值可以存储在salary 列范围-999.99至 999.99。

3、浮点数类型（近似值）
FLOAT和DOUBLE类型代表近似数字数据值。
FLOAT：单精度(四个字节)、DOUBLE：双精度值(四个字节)

4、位值类型
BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1~64，默认为1。BIT其实就是存入二进制的值，类似010110。

如果存入一个BIT类型的值，位数少于M值，则左补0.
如果存入一个BIT类型的值，位数多于M值，MySQL的操作取决于此时有效的SQL模式： 溢出处理

• 如果模式未设置，MySQL将值裁剪到范围的相应端点，并保存裁减好的值。
• 如果模式设置为traditional(“严格模式”)，超出范围的值将被拒绝并提示错误，并且根据SQL标准插入会失败。

二、日期类型
日期和时间类型有：date、time、year、datetime、timestamp。如图：

datetime与timestamp两种类型的区别：

• 如图，datetime占8个字节，timestamp占4个字节；
• 由于大小的区别，datetime与timestamp能存储的时间范围也不同，datetime的存储范围为1000-01-01 00:00:00——9999-12-31 23:59:59，timestamp存储的时间范围为19700101080001——20380119111407
• datetime默认值为空，当插入的值为null时，该列的值就是null；timestamp默认值不为空，当插入的值为null的时候，mysql会取当前时间
• datetime存储的时间与时区无关，timestamp存储的时间及显示的时间都依赖于当前时区。

三、字符串类型
字符串类型有：CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM 和 SET。
1、CHAR 和 VARCHAR

• char是固定长度字符串，其长度范围为0~255且与编码方式无关，无论字符实际长度是多少，都会按照指定长度存储，不够的用空格补足；varchar为可变长度字符串，在utf8编码的数据库中其长度范围为0~21844
• char实际占用的字节数即存储的字符所占用的字节数，varchar实际占用的字节数为存储的字符+1或+2或+3
• MySQL处理char类型数据时会将结尾的所有空格处理掉而varchar类型数据则不会

2、BLOB和TEXT类型
BLOB，二进制大对象，二进制字符串（字节字符串）。用于存储图片、音频、视屏等二进制数据。
四个BLOB 类型：TINYBLOB，BLOB， MEDIUMBLOB，和LONGBLOB。区别在于他们能够保存的值的最大长度。
TEXT，非二进制字符串（字符串）。
四个TEXT类型： TINYTEXT，TEXT， MEDIUMTEXT，和LONGTEXT。它们和对应的BLOB具有相同的长度和存储需求。
text和varchar的区别和联系
联系在于当varchar(M)的M大于某些数值时，varchar会自动转为text：

• M>255时转为tinytext
• M>500时转为text
• M>20000时转为mediumtext

所以过大的内容varchar和text没有区别，varchar(M)和text的区别在于：

单行64K即65535字节的空间，varchar只能用63352/65533个字节，但是text可以65535个字节全部用起来。
text可以指定text(M)，但是M无论等于多少都没有影响；text不允许有默认值，varchar允许有默认值。

varchar和text，建议使用是能用varchar就用varchar而不用text（存储效率高），varchar(M)的M有长度限制，如果大于限制，可以使用mediumtext（16M）或者longtext（4G）。