MySQL 数据类型

一些字段类型方面的使用建议。

整数

TINYINT 1B
SMALLINT 2B
MEDIUMINT 3B
INT 4B
BIGINT 8B

MySQL 允许为整数类型指定宽度，例如 INT(11)，但这没什么用，只是规定一些交互工具用来显示字符的个数。

实数

FLOAT 4B
DOUBLE 8B
DECIMAL(M,D) 占用 max(M,D)+2 字节

由于额外的空间需求和计算成本，应该尽量只在对小数进行精确计算时才使用DECIMAL。可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

字符串类型

VARCHAR

VARCHAR 用于存储变长字符串，比 CHAR 更节省空间。VARCHAR 需要额外的空间记录字符串的长度：列最大长度 <=255B，则使用 1B 存储长度，否则使用 2B。

注意是字符的长度，像 UTF-8，每个字符可能有不同的字节数

如果 VARCHAR 列更新后超过原位置的空间，则需要额外的工作。对于 InnoDB 来说需要分割页面来容纳行。其他存储引擎可能不在原位置更新数据。

VARCHAR 适用于：

字符串列的最大长度远大于平均长度
列的更新很少，所以碎片不是问题
使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

CHAR

CHAR 使用固定长度存储字符串。当存储 CHAR 时，MySQL 会删除尾随空格。如果需要比较，值会用空格填充。

CHAR 适用于：

存储非常短的字符串
所有值的长度几乎都相同
经常修改，因为不容易出现碎片

BINARY 和 VARBINARY

它们存储的是二进制字符串。二进制字符串与常规字符串非常相似，但它们存储的是字节而不是字符。填充也不同：MySQL填充BINANRY用的是\0而不是空格，并且在检索时不会去除填充值。

MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单得多，因此速度更快。

使用 VARCHAR(5) 和 VARCHAR(200) 存储 'hello' 的空间开销是一样的。那么使用更短的列有什么优势吗？

事实证明有很大的优势。较大的列会使用更多的内存，因为MySQL通常会在内部分配固定大小的内存块来保存值。这对于使用内存临时表的排序或操作来说尤其糟糕。在利用磁盘临时表进行文件排序时也同样糟糕。最好的策略是只分配真正需要的空间。

BLOB和TEXT类型

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

与其他类型不同，MySQL 把每个 BLOB 和 TEXT 值当作一个具有标识的对象来处理。当 BLOB 和 TEXT 值太大时，InnoDB 会使用其他存储区域，此时每个值在行内需要1~4字节的空间。

BLOB和TEXT家族之间的唯一区别是，BLOB类型存储的是二进制数据，没有排序规则或字符集，但TEXT类型有字符集和排序规则。MySQL对BLOB和TEXT列的排序与其他类型不同：它只对这些列的最前max_sort_length字节而不是整个字符串做排序。

创建 blob 索引时需要指定长度，否则会报BLOB/TEXT column 'blob_column' used in key specification without a key length错误。

sql
CREATE INDEX blob_column_IDX USING BTREE ON test.my_tb (blob_column(10));

也就是说，MySQL不能将BLOB和TEXT数据类型的完整字符串放入索引，也不能使用索引进行排序。

不要在数据库中存储图片。

ENUM

ENUM 在 MySQL 内部使用整数存储，占用1或2字节，可以替代字符串类型。尽量避免存储ENUM('1','2','3')这样会造成混乱的值。ENUM 类型根据内部的整数值进行排序，而非字面字符串。可以使用FIELD()函数显式指定排序顺序，但这会导致MySQL无法利用索引消除排序。

MySQL将每个枚举值存储为整数，并且必须进行查找以将其转换为字符串表示，因此ENUM列有一些开销。

连接VARCHAR和ENUM列的速度

测试	QPS
VARCHAR 连接 VARCHAR	2.6
VARCHAR 连接 ENUM	1.7
ENUM 连接 VARCHAR	1.8
ENUM 连接 ENUM	3.5

虽然ENUM类型在存储值的方式上非常有效，但更改ENUM中的有效值会导致需要做schema变更。

日期和时间类型

MySQL可以存储的最小时间粒度是微秒。

DATETIME

可存储的时间范围从1000年到9999年，精度为1微秒。它以YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间，且与时区无关。占用8字节的空间。

TIMESTAMP

TIMESTAMP类型存储自1970年1月1日格林尼治标准时间（GMT）午夜以来经过的秒数——与UNIX时间戳相同。占用4字节空间。只能表示从1970年到2038年1月19日。

位压缩数据类型

MySQL有几种使用值中的单个位来紧凑地存储数据的类型。所有这些位压缩类型，不管底层存储和处理方式如何，从技术上来说都是字符串类型。

BIT

BIT 用于存储位数据，长度范围1~64位。MySQL在处理时会将BIT视为字符串类型，而不是数字类型。

shell
mysql> CREATE TABLE bittest(a bit(8));
mysql> INSERT INTO bittest VALUES(b'00111001');
mysql> SELECT a, a + 0 FROM bittest;
+ - + - - - +
| a | a + 0 |
+ - + - - - +
| 9 |    57 |
+ - + - - - +

SET

SET 也是用于存储位数据，在定义 SET 类型的列时类似 ENUM，但是对于某行记录的 SET 类型的值来说，其可以在不同位上存储多个所定义值。

shell
mysql> CREATE TABLE acl (
    -> perms SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL
    -> );
mysql> INSERT INTO acl(perms) VALUES ('CAN_READ,CAN_DELETE');

JSON 数据类型

使用 json 存储数据还是展开为列也是个看需求的问题，不过 josn 在存储空间方面确实不如展开后的列。

主键的类型

主键的类型应在满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。选择类型后，要确保在所有相关表中使用相同的类型，包括是否为UNSIGNED。混合不同的数据类型可能导致性能问题，即使没有性能影响，在进行比较操作时，隐式类型转换也可能会产生难以发现的错误。

最好是使用整数，但要注意整数意外耗尽所导致的系统停机问题。
对于标识符来说，ENUM和SET类型通常是糟糕的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能是没有问题的。ENUM和SET列适用于保存订单状态或产品类型等信息。
如果可能，应避免使用字符串类型作为标识符的数据类型，因为它们很消耗空间，而且通常比整数类型慢。

对于完全“随机”的字符串要非常小心，如MD5()、SHA1()或UUID()生成的字符串。这些函数生成的新值会任意分布在很大的空间内，这会减慢INSERT和某些类型的SELECT查询的速度：

因为插入的值会写到索引的随机位置，所以会使得INSERT查询变慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。
SELECT查询也会变慢，因为逻辑上相邻的行会广泛分布在磁盘和内存中。
对于所有类型的查询，随机值都会导致缓存的性能低下，因为它们会破坏引用的局部性，而这正是缓存的工作原理。如果整个数据集都是“热的”，那么将任何特定部分的数据缓存到内存中都没有任何好处，而且如果工作集比内存大，缓存就会出现大量刷新和不命中。

如果存储通用唯一标识符（UUID）值，则应该删除破折号，或者更好的做法是，使用UNHEX()函数将UUID值转换为16字节的数字，并将其存储在一个BINARY（16）列中。可以使用HEX()函数以十六进制格式检索值。

特殊数据类型

IP 地址

IP 地址实际上是32位无符号整数，应该使用 UNSIGNED INT。MySQL提供了INET_ATON()和INET_NTOA()函数来在这两种表示形式之间进行转换。

表设计中的陷阱

太多的列

MySQL的存储引擎API通过在服务器和存储引擎之间以行缓冲区格式复制行来工作；然后，服务器将缓冲区解码为列。将行缓冲区转换为具有解码列的行数据结构的操作代价是非常高的。

太多的联接

MySQL限制每个联接有61个表。一个粗略的经验法则是，如果需要以高并发性快速执行查询，那么每个查询最好少于十几个的表。

过度使用 ENUM

sql
CREATE TABLE ... (
 country enum(','0
','1','2',...,'31')

变相的 ENUM

如果这里真和假两种情况不会同时出现，那么毫无疑问应该使用ENUM列而不是SET列。

sql
CREATE TABLE ...(
 is_default set('
Y','N') NOT NULL default 'N'

NULL不是虚拟值

当需要表示未知值时，不要太害怕使用NULL。在某些情况下，使用NULL比使用某个虚拟常数更好。

sql
CREATE TABLE ... (
 dt DATETIME NOT N
ULL DEFAULT '0000-00-00 00:00:00'

还有一个相关的细节，MySQL会对NULL值进行索引，而Oracle则不会。

总结

尽量避免在设计中出现极端情况，例如，强制执行非常复杂的查询或者包含很多列的表设计（很多的意思是介于有点多和非常多之间）。
使用小的、简单的、适当的数据类型，并避免使用NULL，除非确实是对真实数据进行建模的正确方法。
尝试使用相同的数据类型来存储相似或相关的值，尤其是在联接条件中使用这些值时。
注意可变长度字符串，它可能会导致临时表和排序的全长内存分配不乐观。
如果可能的话，尝试使用整数作为标识符。
避免使用一些传统的MySQL技巧，例如，指定浮点数的精度或整数的显示宽度。
小心使用ENUM和SET类型。它们很方便，但也可能被滥用，有时还很棘手。另外最好避免使用BIT类型。

目录

整数

实数