MySQL不同存储引擎可能会有不同。下面的内容以InnoDB为主。
选择数据类型的步骤
- 确定合适的大类型：数字、字符串、时间、二进制
- 确定具体的类型：有无符号、取值范围、变长定长等。
整数类型
| 类型 | 字节数 | 范围 |
|---|---|---|
| TNIYINT | 1 | -128~127 |
| SMALLINT | 2 | -32767~32768 |
| MEDIUMINT | 3 | -8388608~8388607 |
| INT | 4 | -2147483648~2147483647 |
| BIGINT | 8 | -9223372036854775808~9223372036854775807 |
- 每个整数类型都有对应的无符号（UNSIGNED）类型。
- 建议使用TINYINT代替ENUM。
- 避免使用整数的显示宽度。
实数类型
| 类型 | 字节 | 备注 |
|---|---|---|
| FLOAT | 4 | 单精度浮点数 |
| DOUBLE | 8 | 双精度浮点数 |
| DECIMAL | 可变 | 高精度定点数 |
- DECIMAL只是一种存储格式，MySQL以二进制的合适存储DECIMAL类型的列。在计算中，DECIMAL会转换成DOUBLE。
- 不建议指定浮点数的精度。
- 不建议使用DECIMAL。
- 建议要存储的实数乘以相应的倍数，使用整数类型运算和存储。
字符串类型
VARCHAR vs CHAR
| 类型 | 最大长度 | 备注 |
|---|---|---|
| CHAR(size) | 255字节 | 定长。size指定的是字符数，不是字节数。 |
| VARCHAR(size) | 65532字节 | 变长。size指定的是字符数，不是字节数。 |
-
适用VARCHAR：
- 字符串地最大长度比平均长度大很多；
- 列更新很少，所以碎片不是问题；
- 使用像UTF8这样复杂地字符集，每个字符都可能使用不同的字节数进行存储。
-
适用CHAR：
- 短字符串；
- 所有值都接近一个长度；
- 经常变更的列，这样不易产生碎片；
-
CHAR类型的列，原字符串末尾的空格会被“干掉”，再填充空格。MySQL检索CHAR不会使用末尾的空格。（列是定长的，MySQL没有存储写入的字符串有多长，只好一刀切，末尾的空格都忽略掉。）
-
VARCHAR末尾的空格不会被“干掉”，检索的时候会用到。（MySQL存储了写入的字符串的长度，这样可以知道字符串末尾有多少各空格是你写入的。）
-
VARCHAR(5)和VARCHAR(200)，如果都只存了"abc"，它们有什么不同呢？实际上，MySQL会分配固定大小地内存块来保存内部值，因此VARCHAR(200)的列尽管只存了和VARCHAR(5)一样的字符串，但是分配的内存可能会大得多。
BINARY vs VARBINARY
| 类型 | 最大长度 | 备注 |
|---|---|---|
| BINARY | 255 | 定长 |
| VARBINARY | 65535 | 变长 |
- BINARY和VARBINARY与CHAR和VARCHAR非常类似。
- BINARY和VARBINARY存储的是二进制字符串，与字符集无关。
- BINARY的末尾会被填充\0，并且会加入检索。
BLOB vs TEXT
L表示数据的长度。
L+x表示存储需要的空间。
| 类型 | 存储 |
|---|---|
| TINYBLOB | L+1 bytes, L < 2^8 |
| SMALLBLOB/BLOB | L+2 bytes, L < 2^16 |
| MEDIUMBLOB | L+3 bytes, L < 2^24 |
| LONGBLOB | L+4 bytes, L < 2^32 |
| TINYTEXT | L+1 bytes, L < 2^8 |
| SMALLTEXT/TEXT | L+2 bytes, L < 2^16 |
| MEDIUMTEXT | L+3 bytes, L < 2^24 |
| LONGTEXT | L+4 bytes, L < 2^32 |
- BLOB系列存储二进制字符串，与字符集无关。
- TEXT系列存储非二进制字符串，与字符集相关。
- 一般情况下，你可以认为BLOB是一个更大的VARBINARY；TEXT是一个更大的VARCHAR。
- MySQL只能对BLOB和TEXT的前面max_sort_length各字符进行排序和索引。
- BLOB和TEXT都不能有default value。
- 当BLOB和TEXT的长度太大时，InnoDB会使用专门的“外部”存储区域来进行存储。
日期和时间类型
| 类型 | 大小 |
|---|---|
| TIMESTAMP | 4字节 |
| DATETIME | 8字节 |
- MySQL能存储的最小时间粒度为秒。
- TIMESTAMP是UTC时间戳，与时区相关。
- DATETIME的存储格式是一个YYYYMMDDHHmmSS的整数，与时区无关，你存了什么，读出来就是什么。
- DATETIME的存储范围大于TIMESTAMP。
- TIMESTAMP的列可以自动更新。
- 除非有特殊需求，否则建议使用TIMESTAMP。
ENUM、SET、BIT
- ENUM列允许在列中存储一组定义值中的单个值。
- SET列允许在列中存储一组定义值中的一个或多个值。
- BIT在InnoDB中其实是一个最小的整数类型。而MySQL在检索BIT的时候会将其当做字符串，而不是整数，这可能会导致一些奇怪的行为。
- 不建议使用这三个类型：用整数代替。
一些原则
- 选择最小的满足需求的数据类型。
一般情况下，应该尽量使用可以正确存储数据的最小数据类型。
简单就好。
比如，用MySQL的内建类型date, time, datetime来存储时间，而不是使用字符串；用INT UNSIGNED来存储IPv4地址。
如何存储IPv6的地址？IPv6地址128bit，MySQL最大的整数类型BIGINT只有64bit。可以将其存储成定长（16字节）的二进制字符？
- 尽量避免使用NULL。
网友评论