MySQL数据类型

《高性能MySQL》第4章，读书笔记。

选择数据类型的原则

1. 更小的通常更好

   尽量使用可以正确存储数据的最小的数据类型，因为这样会占用更少的磁盘、内存、CPU。

2. 简单就好

   简单数据类型的操作需要更少的CPU周期。

3. 尽量避免NULL

   当列中包含NULL时，会使得索引、索引统计和值的比较变得复杂。可以添加DEFAULT避免NULL。

整数类型

MySLQ可以为整数类型指定宽度，例如INT(11)，指定宽度对于值的范围没有影响。

INT(N)，N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但这要整型设置了unsigned zerofill才有效。

1
2
3
4
5
6
7
8
9
10

create table int_width_test(
a int(5),
b int(5) UNSIGNED,
c int(5) ZEROFILL,
d int(5) UNSIGNED ZEROFILL,
e int(8) UNSIGNED ZEROFILL
) ENGINE=INNODB charset=utf8;

INSERT INTO int_width_test values(1,1,11111111,11111111,11111111);
INSERT INTO int_width_test values(1,1,1,1,1);

实数类型

对于float、double有一个比较大的问题就是精度丢失。

原因很简单，其实在MySQL中对对于float类型的数据，只分配了32位的存储空间，对于double类型值分配了64位存储空间。当我们的数据转化为二进制时的存储空间大于32位或者64位，MySQL会将我们存储的数据从高位到低位进行截断。也就是说，当我们存储的空间小于32位或者64位时，数据是准确的，但是当我们存储的数据位数较多时，会出现数据不准确的现象。

对于这个问题，我们可以使用decimal的类型进行解决。

其实decimal可以解决精度丢失的原因：

1. 更大的存储空间
2. 通过字符串化或者其他的方式特殊存储起来

想要深入了解这一方面的读者，可以看我的第二篇参考文献。

字符串类型

CHAR和VARCHAR类型

VARCHAR类型存储可变长度字符串。

实现方式：需要额外的1或2个字节记录字符串的长度，如果长度小于或等于255字节，需要1字节记录。反之，需要2字节。

特点：节省空间，对于性能有帮助。但是需要注意的是，当行的长度变长时，需要进行额外的工作。

当行的长度变长时，MyISAM会将行拆成不同的片段存储，InnoDB需要分裂页使行可以放入内存。

适用场景：字符串的最大长度比平均长度大很多；列的更新很少。

CHAR类型存储定长字符串。

实现方式：MySQL会根据定义分配空间，存储的时候会删除字符串的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。

适用场景：很短的字符串，或所有值都接近一个长度。

BLOB和TEXT类型

BLOB和TEXT类型都是为存储很大的数据而设计的字符串数据类型。

BLOB类型存储的是二进制数据，没有排序规则或字符集。

TEXT类型存储的是字符串，有字符集和排序规则。

MySQL把BLOB和TEXT当作独立的对象处理。当BLOB和TEXT值太大时，InnoDB会使用专门的外部排序存储区域来存储，每个值在行内需要1～4个字节存储一个指针，在外部存储区域内存储实际的值。

当使用BLOB和TEXT列，在遇到使用临时表的情况时，无法使用内存临时表，只能在磁盘上创建临时表。这样会造成严重的性能开销，所以，尽量避免使用BLOB和TEXT类型。

使用枚举(ENUM)代替字符串类型

使用 MySQL ENUM作为列的数据类型

好处：

1. 使得数据更紧凑进而节省空间。
2. 更好的可阅读性。

缺点：

1. MySQL内部存储的是整数，如果使用数字作为ENUM枚举常量，容易混乱。
2. 枚举字段按照内部存储的整数而不是定义的字符串进行排序。
3. 枚举字段是固定的，添加或者删除字符串必须使用ALTER TABLE。

日期和时间

通常推荐使用TIMESTAMP，因为它比DATETIME空间效率更高。

参考文献

1. 详解MySQL数据类型
2. MySQL如何选择float, double, decimal
3. 臭名昭著的 MySQL ENUM 类型 （ 下 ）