最强干货｜你真的对MySQL数据类型了解吗？

沃趣数据库管理平台

于 2022-06-23 10:38:36 发布

阅读量250

点赞数

文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/weixin_48804451/article/details/125420772

版权

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。本文将介绍MySQL的数据类型，以及通过数据类型简单介绍对应的开发规范。

注：在本章节中所提到的严格模式，指的是STRICT_TRANS_TABLES和STRICT_ALL_TABLES两个中的一个启用或者都启用。

1.选择优化的数据类型

MySQL支持的数据类型有很多，选择正确的数据类型对于获得高性能至关重要。我们在选择数据类型上，有几个简单的原则。

更小的通常更好

一般情况下，应该尽量使用可以正确存储数据的最小数据类型。例如，只需要存100以内的整数，TINYINT更好。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，处理时需要的CPU周期也更少。

但是要确保没有低估需要存储的值的范围。因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型（如果系统不是很忙或者存储的数据量不多，或者是在可以轻易修改设计的早期阶段，这时候修改数据类型比较容易）。

简单就好

简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。比如：应该使用MySQL内建的类型（DATE，TIME，DATETIME）而不是字符串来存储日期和时间；以及应该用整型存储IP地址。

尽量避免NULL

很多表都包含可为NULL（空值）的列，即使应用程序并不需要保存NULL也是如此。这是因为可为NULL是列的默认属性（如果定义表结构时没有指定列为NOT BULL，默认都是允许为NULL的）。通常情况下，最好指定列为NOT NULL，除非真的需要存储NULL值。

如果查询中包含可为NULL的列，对MySQL更难优化。因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以（调优时）没有必要首先在现有schema中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。

当然也有例外，例如，InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据（很多值为NULL,只有少数行的列有非NULL值）有很好的空间效率。但这一点不适用于MyISAM。

下一步是选择具体类型。很多MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间（磁盘和内存空间）不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性。

例如，DATETIME和TIMESTAMP列都可以存储相同类型的数据：时间和日期，精确到秒。然而TIMESTAMP只使用DATETIME一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。另一方面，TIMESTAMP允许的时间范围要小得多，有时候它的特殊能力会成为障碍。

2.整数类型

整数类型是数据库中最基本的数据类型，包括整数和实数。如果存储整数，可以使用：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别使用8、16、24、32、64位存储空间。它们可以存储的值的范围从-2(N-1)到2(N-1)-1,其中N是存储空间的位数。

MySQL可以为整数类型指定宽度。例如INT(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了用来显示字符的宽度。对于存储和计算来说，INT(1)和INT(20)是相同的。

整数类型有两个属性：UNSIGNED和ZEROFILL。

整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0 ~ 255，而TINYINT的存储范围是-128 ~ 127。

ZEROFILL是数字前需要填充一些0值的时候使用的。例如‘0001’，‘0002’，比如我们经常看到的股票代码。在使用ZEROFILL参数时，MySQL会自动为该列添加UNSIGNED属性。ZEROFILL也只是一个显示属性，底层存储仍是普通整数。如果存储的数据长度超过ZEROFILL定义的宽度时，此数据会被完整的显示出来而不进行0的填充；如果存储的数据长度小于设定的宽度，则自动填充0。

3.实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。

FLOAT和DOUBLE类型是用来表示近似数值的数据类型，使用标准的浮点运算进行近似计算。单精度浮点数（FLOAT）使用4个字节存储，双精度浮点数（DOUBLE）使用8个字节存储。

MySQL允许非标准语法：FLOAT(M,D),DOUBLE(M,D)。M和D分别表示精度和标度。M是数据的总长度，D是小数点后的保留长度。比如，定义为FLOAT(7,4)的一个列可以显示为-999.9999。MySQL在保存值时会进行四舍五入。因此在FLOAT(7,4)列内插入999.00009的近似结果是999.0001。

如果插入值的精度高于实际定义的精度，系统会自动进行四舍五入处理，使插入的值符合我们的定义。所以在一些需要精确小数的情况下，比如：财务工资类型这种场景，请不要使用FLOAT和DOUBLE。

从MySQL 8.0.17开始，不建议使用非标准语法，并且在将来的MySQL版本中将删除对FLOAT(M,D)和DOUBLE(M,D)的支持。

SQL标准允许在关键字FLOAT后面的括号内用来指定精度（但不能为指数范围），就是FLOAT(p)。FLOAT(p)中的p也是表示精度（以位数表示），但MySQL只使用该值来确定列的数据类型为FLOAT或DOUBLE。当 0≤p≤24 时，MySQL 把它当成 FLOAT 类型，当 25≤p≤53 时，MySQL 把它当成 DOUBLE 型。

我们发现：此种类型的FLOAT只能保证前6位整数不四舍五入，而FLOAT(M,D)则不会这样。

DECIMAL和NUMERIC类型存储精确的数值类型，比如财务数据、足球比赛中的赔率等等。在MySQL中，NUMERIC是以DECIMAL来实现的。因此有关DECIMAL的说明同样适用于NUMERIC。

MySQL中DECIMAL以二进制格式存储值。每4个字节存9个数字。这种存储方式对于整数与小数部分是分开存储的。每9个数字需要4个字节，剩下的数字所需的存储空间如下所示：

图4-1

举例来说，DECIMAL(18,9)小数点两边各有9个数字，因此整数和小数部分分别各需要4个字节，小数点本身占1个字节。DECIMAL(20,6)有14个整数和6个小数，整数部分中的9个数字需要4个字节，剩下的5个数字需要3个字节；小数部分6个数字需要3个字节。

在DECIMAL列声明中，可以（通常是）指定精度和小数位数。例如：salary DECIMAL(5,2)。其中，5是精度，2是小数位数。精度表示值存储的有效位数，小数位数表示小数点后可以存储的位数。

标准语法要求DECIMAL(5,2)能够存储具有五位数字和两位小数的任何值。因此可以存储在salary列中的值的范围是从-999.99到999.99。

在标准语法中，语法DECIMAL(M)等价于DECIMAL(M,0)。MySQL也支持这种变体。默认的M是10。

如果小数位数是0，表明DECIMAL不含小数部分。

小数点和负数的‘-’符号不包括在M中。DECIMAL支持的M为65，D是30。如果分配给此类型的值小数点后位数超过指定的标度D允许的范围，值将按标度D进行转换（精确的行为是特定于操作系统的，但是通常是将其截断为允许的位数）。

DECIMAL列不存储开头的+、-和0数字。如果你向DECIMAL(5,1)的列中插入+0003.1，MySQL会存储为3.1。对于负数，‘-’字符不会被存储。

NUMERIC和FIXED都是DECIMAL的同义词。

我们可以通过实验来看下DECIMAI数据类型。

官方介绍的DECIMAL是高精度类型，但当发生截断时，也会出现四舍五入的现象。所以在设置精度和标度的时候要足够长，不让它发生截断数据的操作。

因为CPU不支持对DECIMAL的直接计算，所以在MySQL5.0及更高版本中，MySQL服务器自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL，例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT中，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

4.位类型

位类型用来保存BIT值。BIT(M)表示允许存储M位数值，M范围从1到64。

如果要特别表明是位值，可以使用b'value'的方式。value由0和1组成。比如，b'111'和b'10000000'表示7和128。

如果为BIT(M)分配的值的长度小于M位，在值的左边用0填充。例如，为BIT(6)列分配一个值b'101'，实际上，效果与分配b'000101'相同。

下面来看一些实验：

5.字符串类型

字符串类型是在数据库种存储字符串的数据类型。字符串类型包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

对于字符串列（CHAR、VARCHAR和TEXT类型）的定义，MySQL以字符单位解释长度规范。对于二进制字符串列（BINARY、VARBINARY和BLOB类型）的定义，MySQL以字节为单位解释长度规范。

图4-2 字符串类型的存储需求（latin1字符集为例）

5.1 CHAR和VARCHAR

CHAR和VARCHAR是日常使用最多的字符类型。CHAR和VARCHAR类型的声明，其长度指的是要存储的最大字符数，而不是字节数。比如，CHAR(30)最多可容纳30个字符。

一个定义为CHAR列的长度被固定在创建时声明的长度。长度可以是0到255之间的任何值。CHAR存储值时，它们会用空格右填充到指定的长度。当CHAR被检索到的值，拖尾的空格被删除，除非启用了PAD_CHAR_TO_FULL_LENGTH的SQL模式。

VARCHAR(M)列中的值是可变长度的字符串。长度可以是0到65535之间的值，只存储字符串实际需要的长度。一个CARCHAR列的有效最大长度取决于最大行大小（65535字节，所有列共享）和所使用的字符集。

与CHAR相比，VARCHAR使用额外的1~2字节来存储值的长度（字符串本身的长度）。如果列的最大长度（即M）小于或等于255，则使用1字节表示，否则就是2字节。

如果未启用严格的SQL模式，并且为CHAR或VARCHAR列分配的值超过了列的最大长度，则该值将被截断并生成警告。对于非空格字符的截断，可以使用严格的SQL模式从而发生错误（而不是警告）并阻止该值的插入。

对于VARCHAR列，无论使用哪种SQL模式，插入前都会截断超出列长度的尾随空格，并生成警告。对于CHAR列，无论SQL模式如何，都将以静默方式从插入值中截断多余的尾随空格。

VARCHAR值在存储时不会填充。根据标准SQL，在存储和检索值时保留尾随空格。

CHAR和VARCHAR跟字符集编码有密切联系。比如，当存储英文字母或数字时，无论latin1、gbk或utf8字符集，1个字符占用1个字节；当存储汉字时，latin1字符集不支持存储汉字，gbk字符集下1个汉字占用2个字节，utf8字符集下1个汉字占用3个字节

表7-1 latin1字符集存储字节表

值	CHAR(4)	需要存储	VARCHAR(4)	需要存储
‘’	‘ ’	4个字节	‘’	1个字节
‘ab’	‘ab ’	4个字节	‘ab’	3个字节
‘abcd’	‘abcd’	4个字节	‘abcd’	5个字节
‘abcdefgh’	‘abcd’	4个字节	‘abcd’	5个字节

表7-2 gbk字符集存储字节表

值	CHAR(4)	需要存储	VARCHAR(4)	需要存储
‘’	‘ ’	4个字节	‘’	1个字节
‘ab’	‘ab ’	4个字节	‘ab’	3个字节
‘abcd’	‘abcd’	4个字节	‘abcd’	5个字节
‘abcdefgh’	‘abcd’	4个字节	‘abcd’	5个字节
‘数据类型’	‘数据类型’	8个字节	‘数据类型’	9个字节

表7-3 utf8字符集存储字节表

值	CHAR(4)	需要存储	VARCHAR(4)	需要存储
‘’	‘ ’	4个字节	‘’	1个字节
‘ab’	‘ab ’	4个字节	‘ab’	3个字节
‘abcd’	‘abcd’	4个字节	‘abcd’	5个字节
‘abcdefgh’	‘abcd’	4个字节	‘abcd’	5个字节
‘数据类型’	‘数据类型’	12个字节	‘数据类型’	13个字节

注：当字符个数超过定义的长度时仅在不使用严格模式时适用；如果MySQL在严格模式在运行，则不会存储超过列长度的值，而是直接报错。

CHAR和VARCHAR的使用场景：

VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在update时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎处理方式是不一样的。例如，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。下面这种情况使用VARCHAR是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比CARCHAR在存储空间上也更有效率。例如使用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。

5.2 BINARY和VARBINARY

BINARY和VARBINARY与CHAR和VARCHAR类型还是有点类似的，不同的是，BINARY和VARBINARY存储的是二进制的字符串，而非字符型字符串。也就是说，BINARY和VARBINARY没有字符集的概念，对其排序和比较都是按照二进制的值进行。

BINARY(M)和VARBINARY(M)中的M指的是字节长度，而非CHAR(M)和VARCHAR(M)中的字符长度。对于BINARY(10)，可存储的字节固定为10，对于CHAR(10)，可存储的字节视字符集的情况而定。

如果未启用严格的SQL模式，并且为BINARY或VARBINARY列分配的值超过了列的最大长度，则该值将被截断并生成警告。对于截断的情况，可以使用严格的SQL模式从而发生错误（而不是警告）并阻止该值的插入。

BINARY存储值时，它们会用0x00(零字节)右填充到指定的长度。并且在检索的时候，拖尾的0x00填充字节不会被删除。所有字节在比较中都有效，包括ORDER BY和DISTINCT操作。0x00和空格在比较中是不同的，并且0x00会先于空格进行排序。

示例：对于列BINARY(3)，‘a’在插入的时候会变成‘a \0’， ‘a\0’在插入的时候变成‘a\0\0’。两个插入的值在检索的时候保持不变。

对于VARBINARY，没有用于填充的插入，也没有剥离任何字节以进行检索。所有字节在比较中都有效，包括ORDER BY和DISTINCT操作。0x00和空格在比较中是不同的，并且0x00会先于空格进行排序。

对于剥离尾随字节或比较时忽略它们的情况，如果一列具有要求唯一性的索引，则将仅尾随字节不同的值插入该列会导致重复值错误。例如，如果表包含‘a’，则尝试插入‘a\0’会导致重复键错误。

如果使用BINARY类型存储二进制数据并且要求检索的值与存储的值完全相同，则应仔细考虑上述填充和剥离的特性。以下示例说明了0x00的右填充如何影响BINARY列的值比较：

如果检索的值必须与存储的值相同且没有填充，最好使用VARBINARY或BLOB数据类型来替代。

对于CHAR和VARCHAR来说，比较的是字符本身存储的值；对于BINARY和VARBINARY来说，比较的是二进制的值。

当需要存储二进制数据，并且希望MySQL使用字节码而不是字符进行比较时，这些类型是非常有用的。二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单很多，所以也就更快。

5.3 BLOB和TEXT

BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

BLOB有TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB，L指字节数。存储二进制字符串，没有字符集的概念，对其排序和比较都是按照二进制的值进行。

TEXT有TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT，L指字符数。和VARCHAR相似，存储字符串，它们具有BINARY以外的字符集，并且根据字符集的排序规则对值进行排序和比较。

BLOB和TEXT的相同点：

两个数据类型都是大对象，用来存储一些超长的数据，比如：新闻、文件等
存储和检索时都区分大小写
存储和检索时不填充、删除尾部空格
存储时如果数据超过长度会被截断（严格模式下拒绝存储并抛出错误）
BLOB和TEXT的不同点：
BLOB存储二进制字符串，没有字符集
TEXT存储字符，有字符集

在大多数情况下，可以将BLOB类型的列视为足够大的VARBINARY类型的列。同样，也可以将TEXT类型的列视为足够大的VARCHAR类型的列。然而，BLOB和TEXT在以下几个方面又不同于VARBINARY和VARCHAR：

在BLOB和TEXT类型的列上创建索引时，必须指定索引前缀的长度；而VARCHAR和VARBINARY的前缀长度是可选的
BLOB和TEXT类型的列不能有默认值
排序时仅使用列的前max_sort_length个字节

另外，在一些存储引擎内部，比如InnoDB存储引擎，会将大的VARCHAR类型字符串（VARCHAR(65535)）自动转换为TEXT。

5.4 ENUM

ENUM是按字节存储。

ENUM是一个字符串对象，其值是从定义时允许值的列表中选择的值。这些值在表创建时在列定义时指定。具有以下优点：

在列的一组可能值有限的情况下，压缩数据存储。你指定输入值的字符串会自动编码为数字
可读的查询和输出。这些数字在查询结果中会转换成相应的字符串

ENUM对象的大小由不同的枚举值的数目确定。枚举用一个字节时，最大能枚举255个元素；枚举用两个字节时，能枚举256到65535个元素。

因此，MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。

我们来看个例子：

创建一张表enum_test，含有一个ENUM枚举列e，并插入三行数据。

这三行数据实际存储为整数，而不是字符串。可以通过在数字上下文环境检索看到这个双重属性：

因此，如果使用数字作为ENUM枚举常量，这种双重性很容易导致混乱。例如，ENUM(‘1’,’2’,’3’)。建议尽量避免这么做。

另外一个比较特别的地方是，枚举字段是按照内部存储的整数而不是定义的字符串进行排序的：

一种绕过这种限制的方式是按照需要的顺序来定义枚举列。另外也可以在查询中使用FIELD()函数显示地指定排序顺序，但这会导致MySQL无法利用索引消除排序。

如果在定义时就是按照字母的顺序，就没有必要这么做了。

枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。因此，对于一系列未来可能会改变的字符串，使用枚举不是一个好主意，除非能接受只在列表末尾添加元素。这样就可以不用重建整个表来完成修改。

当然枚举也有好处。比如上述图中这种表，如果将100万行’apple’插入此表需要100万字节的存储空间，而如果将字符串’apple’存储在VARCHAR列中则需要500万字节的存储空间。可以通过SHOW TABLE STATUS命令输出结果中的Data_length列的值，来观察表的缩小程度。

看另一个例子。

当插入的值不符合ENUM列定义时，会直接报错。注：这里小写的f插入会报错是因为校对规则是utf8_bin，此校对规则会区分大小写。

ENUM有以下特点：

需要提前定义取值范围
创建表时，表定义中ENUM成员值的末尾空格会被自动删除
检索到时，ENUM将使用列定义中使用的字母大小写显示存储在列中的值。请注意，ENUM可以为列分配一个字符集和排序规则。对于二进制或区分大小写的归类，在为列分配值时考虑字母大小写。
如果在ENUM中插入无效值（即，在定义值列表中不存在的值），则会插入空字符串，而不是将其作为特殊错误值。此字符串可以通过将数字值设为0来与正常的空字符串区分开。如果启用了严格的SQL模式，则尝试插入无效的ENUM值将导致错误。

如果ENUM声明某列允许NULL，则该NULL值为该列的有效值，默认值为NULL。如果ENUM声明了列NOT NULL，则其默认值是允许值列表的第一个元素。

5.5 SET

SET是按字节存储。

SET是具有零个或多个值的字符串对象，每个值都必须从创建表时指定的允许值列表中选择。例如，指定为的列SET('one', 'two') NOT NULL可以具有以下任何值：

‘’

‘one’

‘two’

‘one,two’

一个SET列最多可包含64个不同的成员。

SET有以下特点：

需要提前定义取值范围
创建表时，表定义中SET成员值的末尾空格会被自动删除
检索到后，SET将使用列定义中使用的字母大小写来显示存储在列中的值。请注意，SET可以为列分配一个字符集和排序规则。对于二进制或区分大小写的归类，在为列分配值时考虑字母大小写。
MySQL以数字来存储SET值。存储值的地位对于第一个SET成员。如果在数字上下文中检索SET值，则检索到的值具有与组成列值的集合成员相对应的位集合。可以通过如下方式来检索值：

mysql> SELECT set_col + 0 FROM tbl_name;

对于指定的列SET(‘a’,’b’,’c’,’d’)，成员具有以下十进制和二进制值：