MySQL常用数据类型介绍

本文使用的数据库为MySQL5.5，文章摘自《深入浅出MySQL》，仅作个人笔记，谢谢

任何数值的不符合精度的存储都要考虑到数据库模式的设置。

整形数据类型：

对于整型数据，MySQL 还支持在类型名称后面的小括号内指定显示宽度，例如 int(5)表示当数值宽度小于5 位的时候在数字前面填满宽度，如果不显示指定宽度则默认为int(11)。一般配合zerofill 使用，顾名思义，zerofill 就是用“0”填充的意思，也就是在数字位数不够的空间用字符“0”填满。

所有的整数类型都有一个可选属性UNSIGNED（无符号），如果需要在字段里面保存非负数或者需要较大的上限值时，可以用此选项，它的取值范围是正常值的下限取0，上限取原值的2 倍，例如，tinyint 有符号范围是-128～+127，而无符号范围是0～255。如果一个列指定为zerofill，则MySQL 自动为该列添加UNSIGNED 属性。

小数数据类型：

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

值得注意的是，浮点数后面跟“(M,D)”的用法是非标准用法，如果要用于数据库的迁移，则最好不要这么使用。float 和double 在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示，而decimal 在不指定精度时，默认的整数位为10，默认的小数位为0。

当插入数据的标度大于数据限制的标度时，浮点数据类型会四舍五入，然后将数据插入；定点数据类型会将数据truncate，然后插入数据，并且系统会出现一个警告，向我们报告数据是经过truncate后插入的。

如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；定点数如果不写精度和标度，则按照默认值decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则会报错。

注意：在今后关于浮点数和定点数的应用中，用户要考虑到以下几个原则：

 浮点数存在误差问题；

 对货币等对精度敏感的数据，应该用定点数表示或存储；

 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；

 要注意浮点数中一些特殊值的处理。

BIT（位）类型：

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

日期时间类型：

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

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

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

如果需要经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP 来表示。TIMESTAMP 值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串，显示宽度固定为19 个字符。如果想要获得数字值，应在TIMESTAMP 列添加+0。

如果只是表示年份，可以用YEAR 来表示，它比DATE 占用更少的空间。YEAR 有2 位或4 位格式的年。默认是4 位格式。在4 位格式中，允许的值是1901～2155 和0000。在2 位格式中，允许的值是70～69，表示从1970～2069 年。MySQL 以YYYY 格式显示YEAR值。

系统给MySQL表中第一个TIMESTAMP字段自动创建默认值CURRENT_TIMESTAMP（系统日期）。注意，MySQL只给表中的第一个TIMESTAMP字段设置默认值为系统日期，如果有第二个TIMESTAMP类型，则默认值设置为0值。可以将默认的0值修改为其他的常量日期，但是不能再修改为current_timestmap，因为MySQL规定TIMESTAMP类型字段只能有一列的默认值为current_timestmap，如果强制修改，系统会报错误提示，提示代码1293。

TIMESTAMP还有一个重要特点，就是和时区相关。当插入日期时，会先转换为本地时区后存放；而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大。

Char和Varchar：

MyISAM 存储引擎：建议使用固定长度的数据列代替可变长度的数据列。

MEMORY 存储引擎：目前都使用固定长度的数据行存储，因此无论使用CHAR 或VARCHAR 列都没有关系。两者都是作为CHAR 类型处理。

InnoDB 存储引擎：建议使用VARCHAR 类型。对于InnoDB 数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR 列不一定比使用可变长度VARCHAR 列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR 平均占用的空间多于VARCHAR，因此使用VARCHAR 来最小化需要处理的数据行的存储总量和磁盘I/O 是比较好的。

CHAR 和VARCHAR 很类似，都用来保存MySQL 中较短的字符串。二者的主要区别在于存储方式的不同：CHAR 列的长度固定为创建表时声明的长度，长度可以为从0～255 的任何值；而VARCHAR 列中的值为可变长字符串，长度可以指定为0～255（5.0.3 以前）或者65535（5.0.3以后）之间的值。在检索的时候，CHAR 列删除了尾部的空格，而VARCHAR 则保留这些空格。

ENUM 类型和SET 类型：

ENUM 中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，对1～255 个成员的枚举需要1 个字节存储；对于255～65535 个成员，需要2 个字节存储。最多允许有65535 个成员。ENUM 类型是忽略大小写的，对于插入不在ENUM 指定范围内的值时，并没有返回警告，而是插入了enum的第一值，这点用户在使用时要特别注意。另外，ENUM 类型只允许从值集合中选取单个值，而不能一次取多个值。

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

 1～8 成员的集合，占1 个字节。

 9～16 成员的集合，占2 个字节。

 17～24 成员的集合，占3 个字节。

 25～32 成员的集合，占4 个字节。

 33～64 成员的集合，占8 个字节。

Set 和ENUM 除了存储之外，最主要的区别在于Set 类型一次可以选取多个成员，而ENUM则只能选一个。

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

TEXT类型和BLOG类型

二者之间的主要差别是BLOB 能用来保存二进制数据，比如照片；而TEXT 只能保存字符数据，比如一篇文章或者日记。TEXT 和BLOB 中有分别包括TEXT、MEDIUMTEXT、LONGTEXT 和BLOB、MEDIUMBLOB、LONGBLOB3 种不同的类型。

BLOB 和TEXT 值会引起一些性能问题，特别是在执行了大量的删除操作时。删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMIZE TABLE 功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。

可以使用合成的（Synthetic）索引来提高大文本字段（BLOB 或TEXT）的查询性能。简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符是没有用处的）。可以使用MD5()函数生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在CHAR 或VARCHAR 列中，它们会受到尾部空格去除的影响。合成的散列索引对于那些BLOB 或TEXT 数据列特别有用。用散列标识符值查找的速度比搜索BLOB 列本身的速度快很多。

如果需要对BLOB 或者CLOB 字段进行模糊查询，MySQL 提供了前缀索引，也就是只为字段的前n 列创建索引。

在不必要的时候避免检索大型的BLOB 或TEXT 值。例如，SELECT * 查询就不是很好的想法，除非能够确定作为约束条件的WHERE 子句只会找到所需要的数据行。否则，很可能毫无目的地在网络上传输大量的值。这也是BLOB 或TEXT标识符信息存储在合成的索引列中对用户有所帮助的例子。用户可以搜索索引列，决定需要的哪些数据行，然后从符合条件的数据行中检索BLOB 或TEXT 值。

把BLOB 或TEXT 列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，可以得到固定长度数据行的性能优势。它还可以使主数据表在运行SELECT * 查询的时候不会通过网络传输大量的BLOB 或TEXT 值。