Mysql之数据类型详解

在进一步学习Mysql高级特性之前，先对Mysql支持的数据类型进行了解，这是使用Mysql最基础的能力，也会对后面所要了解的Mysql的高级特性产生根本性影响。所以，为每个字段选择合适的数据类型就显得至关重要，又常常被我们忽视。由于不同Mysql版本支持的数据类型种类不同且各种数据类型的特性也有所不同，所以这里我们以Mysql5.7版本为例来讲解常见的数据类型，大致可以分为以下几类：

数值类型

类型	字节	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	浮点数（单精度）
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	浮点数（双精度）
DECIMAL	M+2	最大值和Double相同，给定DECIMAL的有效取值范围由M和D决定		定点数
BIT(M)	1到8 bytes	BIT(1)	BIT(64)

整数类型中，按照取值范围和存储方式的不同，分为tinyint、smallint、mediumint、int和bigint五种类型。在选择数据类型时要根据应用的实际情况确定其取值范围，最后根据确定的结果慎重选择数据类型。

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

这里有个问题，设置了宽度限制后，如果插入大于宽度限制的值，数据库会怎么操作呢？答案是：不会对插入的数据有任何影响，还是按照类型的实际精度进行保存，这时，宽度格式实际已经没有意义了。

在设置字段的类型为整数类型时都有一个可选属性UNSIGNED（无符号），这会影响到字段的取值范围。还有另外一个属性AUTO_INCREMENT。在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型。

对于小数的表示，Mysql分为两种方式：浮点数和定点数。浮点数包括float（单精度）和double（双精度），而定点数只有decimal一种表示。定点数在Mysql内部以字符串形式存放，比浮点数更精确，适用于表示货币等精度高的数据。

浮点数和定点数都可以用类型名称后加(M,D)的方式来进行表示，“(M,D)”表示该值一共显示M位数字，其中D位位于小数点后面，M和D又称为精度和标度。需要注意的是，浮点数后面跟(M,D)是非标准用法，如果要用于数据库的迁移，则最好不要这么使用。另外，浮点数在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示；而定点数在不指定精度时，默认的整数位为10位，默认的小数位为0位。还有，当插入的数值超过了设置的精度时，浮点数都是使用四舍五入的方式截取小数点后面的数据保证插入成功；但对于定点数则要分情况考虑：在传统SQLMODE下是无法插入这样的数据的，在非传统SQLMODE下则通过四舍五入插入成功，但会出现一个警告信息。

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

日期类型

类型	大小( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

从上面的表格中我们很容易看出五种日期类型的区别，我们可以根据应用的需要选择日期类型，但由于DATETIME和TIMESTAMP这两种日期类型比较相似，它们重点区别：

• TIMESTAMP表示的时间范围比较小，DATETIME表示的时间范围比较大。两者都可以设置默认值和ON UPDATE CURRENT_TIMESTAMP属性，是的日期列可以随其他列的更新而自动更新为最新时间
• TIMESTAMP在Mysql5.6.6版本之后增加了控制参数explicit_defaults_for_timestamp，如果设置为on，则需要显示指定默认值和ON UPDATE CURRENT_TIMESTAMP属性；如果设置为off，这会自动设置默认值为CURRENT_TIMESTAMP（系统时间）和ON UPDATE CURRENT_TIMESTAMP属性，并且自动设置为not null。Mysql8.0版本此参数默认为on，之前版本为off。需要注意的是，在Mysql5.6版本之前TIMESTAMP类型的字段只能有一列的默认值为current_timestamp，但是在mysq5.6版本之后这个限制去掉了。
• 当explicit_defaults_for_timestamp设置为off时，表中的第一个TIMESTAMP列自动设置为系统时间。如果在TIMESTAMP列中插入NULL，则该列值将自动设置为当前的日期和时间。当不不明确给TIMESTAMP列赋值时，也会自动设置该列值为当前的日期和时间，当TIMESTAMP列插入的值超过取值范围时，Mysql认为该值溢出，使用“0000-00-00 00:00:00”进行填补。
• TIMESTAMP的插入和查询都受到当地时区的影响，更能反映出实际的日期。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。
• TIMESTAMP的属性收Mysql版本和服务器SQLMode的影响很大，我们这里的描述都是一Mysq5.7版本为例的。

字符串类型

我们这里以5.7版本为例来介绍Mysql的字符数据的存储类型，主要包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等多种字符串类型。

类型	字节/字符	描述
CHAR (M)	M个字符	M为0~255之间的整数
VARCHAR(M)	0~M个字符	M为0~65535之间的整数，值的长度+1个字节
BINARY(M)	M个字节	M为0~255之间的整数
VARBINARY(M)	0~M个字节	M为0~65535之间的整数，值的长度+1个字节
TINYBLOB		允许长度0~255字节，值的长度+1个字节
BLOB		允许长度0~65535字节，值的长度+2个字节
MEDIUMBLOB		允许长度0~16777215字节，值的长度+3个字节
LONGBLOB	允许长度0~4294967295字节，值的长度+4个字节
TINYTEXT		允许长度0~255字节，值的长度+1个字节
TEXT		允许长度0~65535字节，值的长度+2个字节
MEDIUMTEXT		允许长度0~16777215字节，值的长度+3个字节
LONGTEXT		允许长度0~4 294 967 295字节，值的长度+4个字节

这里我们主要讲解几个使用比较多的字符串类型及它们的区别。

CHAR和VARCLHAR类型

CHAR和VARCLHAR类型的区别：

• 两者的存储方式不同：CHAR列的长度固定为创建表时声明的长度，长度可以为从0 ~255的任意值；而VARCHAR列中的值为可变长字符串，长度可以指定为0 ~65535之间的值。
• 存储空间需求不同：CAHR存储需求大小为设置的固定长度，而VARCAHR存储需要为字符串实际长度加1或加2个字节，因为VARCHAR类型的字符串在存储时需要一到两个字节记录字节长度。
• 检索时处理方式不同：CHAR类型字段会删除尾部的空格，VARCHAR类型字段则保留这些空格。
• 由于CHAR是固定长度，会根据需要采用空格进行填充以方便比较，所以它的处理速度比VARCHAR快，缺点是有时会浪费存储空间；VARCHAR是变长的会根据实际字符长度来申请存储空间，相对节省空间，但仍要按需设置VARCHAR的长度，因为定义一个远超需求的长度可能影响应用程序的效率，甚至会触发Mysql在VARCHAR上存在的一些bug。

在使用VARCHAR(M)时我们还需要注意几点：

• VARCHAR(M)中的M指的是字符数不是字节数，当设置了某列为VARCHAR(M)后，这一列最终所占用的最大存储空间的字节数由M值和此列的字符集决定,因为字符集不同，每个字符存储时使用的字节数也不同。当然也不能超过最大行大小的限制，因为这是Mysql的硬性限制。
• VARCHAR最大长度65535字节，而65535字节也是行大小硬限制，也就是说这一行中所有的列的大小之和都不能超过这个值，无论这一行中有多少个VARCHAR类型的列。
• 设置恰当的M值是非常必要的，因为更长的列会消耗更多的内存，因为Mysql通过会分配固定大小的内存块保存内部值。尤其是用内存临时表进行排序或操作时会特别糟糕。再利用磁盘临时表进行排序是也同样糟糕。
• 由于VARCHAR是变长的，因为在update时可能使行变得更长，如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，这种情况下，在MyISAM中会将行拆成不同的片段存储，InnoDB中则需要分裂页来使行可以放进页内。

在Mysql中不同存储引擎中CHAR和VARCHAR的使用原则有所不同：

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

总结来说，CHAR适合的场景：1.存储很短的字符串，或者所有值都接近统一长度2.对于经常变更的数据，CHAR比VARCHAR更好，因为定长的CHAR类型不容易产生碎片3.对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。VARCHAR适合的场景：1.字符串列的最大长度比平均长度大很多2.列的更新很少，所以碎片不是问题

TEXT和BLOB

一般在保存少量字符串时的时候，我们会选择CHAR和VARCHAR；而在保存较大文本时，通常会选择TEXT或者BLOB。两者之间的主要差别是BLOB类型存储的是二进制数据，没有排除规则或字符集，而TEXT类型有字符集和排序规则。TEXT和BLOB中又细化了几种不同的类型，它们之间的主要区别是存储文本长度不同和存储字节不同，我们可以根据实际情况来选择最小存储类型。接下来，我们主要介绍一下使用TEXT和BLOB存在的一些问题：

• BLOB和TEXT值会引起一些性能问题，特别是在执行大量的删除操作时。
  删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。通过测试我们可以发现，数据文件的大小不会随着执行数据的删除操作而变小，因此对于有大量删除操作的表来说，要定期的进行碎片整理操作。
• 可以使用合成的索引来提高大文本字段的查询性能。
  合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精确匹配的查询。
• 在不必要的时候避免查询大量的BLOB或TEXT值。
• 把BLOB或TEXT列分离到单独的表中。
• 如非必要使用BLOB或TEXT，尽可能使用VARCHAR类型，VARCHAR类型最长支持65533字符长度，已经可以满足绝大多数的需求了。
• Mysql对BLOB和TEXT列进行排序与其他类型是不同的：它只有对每个列的最前max_sort_length字节而不是整个字符串做排序。

需要注意的是：与其他的类型不同，Mysql把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后再外部存储区域存储实际的值。

ENUM类型

枚举列可以把一些不重复的字符串存储成一个预定义的集合。列的取值必须在这个集合中，且只能取其中的一个值，否则就会报错。枚举列有以下特点：

• 取值必须在预定集合中，且只能取预定集合中的一个值
• 枚举类型忽略大小写，例如：预定集合（‘M’,‘F’）,给此列赋值m也是可以的并且在存储到数据库时会自动转成M；还可以插入数字来替代对应的值，并会自动转成数字对应的元素存入数据库中
• 应尽量避免使用数字作为ENUM枚举常量。因为Mysql在内部会将每个枚举值在集合中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的查询表。如果使用数字作为Enum枚举常量，这种双重性很容易导致混乱。
• 枚举字段是按照内部存储的“整数”而不是字符串进行排序的。因此我们在设置枚举常量集合时一定要注意元素的顺序。
• 枚举最不好的地方是，字符串集合是固定的，添加或删除字符串必须使用alter table。因此，对于一系列未来可能会改变额字符串，使用枚举不是个好主意，除非能够接受从集合的末尾添加元素。
• 由于Mysql把每个枚举值保存为整数，并且必须进行查找才能转换为字符串，所以枚举列会有一些额外的开销。通常枚举的列表都比较小，所以开销还可以控制，但并不是一直如此。如果枚举字段和VARCHAR字段进行关联时往往会比VARCHAR字段之间或枚举字段之间的关联要慢一些。

SET类型

SET和ENUM类型非常类似，但是也有区别：

• SET类型字段的值可以是预设集合中的任意元素的组合，而不仅限一个元素。
• 存储上也有所不同，也就是说成员数量不同所占用空间也不同，如：1~8成员的集合，占用1个字节；9 ~16成员的集合，占2个字节，以此类推，最多占用8个字节。

JSON类型

JSON类型是Mysql5.7.8版本起支持的数据类型。JSON类型比字符类型有如下优点：

• JSON类型会自动校验数据是否为Json格式，如果不是json格式，则会报错。
• Mysql提供了一组操作JSON类型数据的内置函数，可以方便的提取各类数据，可以修改特定的键值
• 优化的存储格式，存储在JSON列中的JSON数据被转换成内部的存储格式，允许快速读取。

JSON中支持的数据类型包括ENUM、STRING、BOOLEAN、NULL、ARRAY、OBJECT六种，一个JSON中元素可以是这6种类型元素的任意组合，其中BOOLEAN使用true/false的字面文本表示；NULL使用null的文本表示；字符串和日期类型都用双引号引起来表示；ARRAY要用中括号引起来；OBJECT保存的KV对要用大括号引起来，其中的K也要用双引号引起来。