Mysql 数据类型选择

摘自 《高性能 Myql》 第三版

Mysql 支持的数据类型非常多，选择存储那种类型可参考以下原则

• 更小的通常更好，更小的数据类型查找速度快占用空间少
• 简单就好，比如使用 Mysql 自建类型存储时间日期，应该用整形存储 IP 地址而非字符串。
• 尽量避免 NULL，NULL 列占用更多空间且不利于索引优化，除了时间日期这样不适合用 0 或者空字符串代替。

整数类型

列类型 | 存储空间(字节) | 取值范围(SIGN) | 取值范围(UNSIGN)

• | - | - | -
  tinyint | 1 | -128 ~ 127 | 0 ~ 255
  smallint | 2 | -32768 ~ 32767 | 0 ~ 65535
  mediumnint | 3 | -2^23 ~ 2^23-1 | 0 ~ 2^24 -1
  int | 4 | -2^31 ~ 2^31-1 | 0 ~ 2^32-1
  bigint | 8 | -2^63 ~ 2^63-1 | 0 ~ 2^64-1

MySql 虽可指定整数宽度如 INT(10)，但并不会限制值的合法范围。对于存储和计算来说，INT(1) 和 INT(20) 是相同的。

实数类型

列类型	存储空间(字节)	是否精确
FLOAT	4	否
DOUBLE	8	否
DECIMAL	每4字节存9位数字，小数点占一位	是

浮点数在计算机系统中很多情况下都无法精确表示，如 0.1 + 0.2 只能粗略表达为 0.30000000000000004，所以需要精确运算时，首先考虑规避浮点计算，如货币数字存储时可以先乘以相应倍数转化为整数再存入数据库。

若无法避免计算浮点时，欲精确计算可用 DECIMAL ，MySQL 5.0 和更高版本允许 DECIMAL 最多存 65 个数字。DECIMAL(a,b)，其参数含义：a 指定指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38；b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0。

字符串 varchar 和 char

列类型	含义	额外占用空间
char(n)	固定长度，最多255个字符	0
varchar(n) n < 255	可变长度，最多255个字符	1 byte overhead
varchar(n) n >= 255	可变长度，最多65535个字符	2 byte overhead
tinytext	可变长度，最多255个字符	1 byte overhead
text	可变长度，最多65535个字符	2 byte overhead

varchar类型用于存储可变长字符串，它只占用必要的存储空间。

varchar需要使用 1 或 2 个额外字节记录字符串的长度：如果字符串的最大长度小于 255 则只占用 1 个字节用于记录字符串长度，如果字符串的长度大于 255 则要占用 2 个字节用于记录字符串的长度。这就代表varchar的最大长度就是 65535。但是在实际中varchar列不能达到 65535 这么大，因为对 innodb 来说，65535 是一行中所有 varchar 列共享的长度。

下面的这些情况使用 varchar 类型是合适的：

• 字符串的最大长度比平均长度大很多
• 列的更新很少。这是因为行是变长的，在 update 时可能使行变的比原来更长。如果行占用的空间增长，并且在页内没有更多的空间可以存储，就需要存储引擎特别的处理。例如，Innodb 存储引擎需要分裂页使行放入页内。这会导致增加内存碎片，降低数据库性能。
• 使用了像 UTF-8 这样复杂的字符集，因其每个字符使用了不同的字节数存储。

char 类型是定长的。它的最大宽度为 255。定义char(10)，那么不论你存储的数据是否达到了10个字符，都要占去10个字符的空间

varchar 和 char 的宽度都是以字符为单位的。两种类型相比，char 类型会去掉字符串末尾的空格，而 varchar 类型不会。

VARCHAR(5) 和 VARCHAR(20) 存储 hello 的空间开销是一样的，那么使用更短的列有何优势？虽然存储开销相同，但被内存读取时，内存会分配此列设置宽度的空间，若内存开销过大不利于临时表排序，降低查询速度。

长文本 TEXT

存储很大的数据而设计的类型，存储引擎通常会做特殊的处理，当TEXT值特别大时，InnDB会使用专门的外部存储区域，此时每个值在行内需要1～4个字节存储一个指针，然后外部区域存储实际的值。

TEXT只能对此列最前 max_sort_length 字节而非整个字符串做排序，并且 MySQL 不能将 TEXT 全部字符进行索引。故此建立索引必须给出前缀索引长度。

时间日期类型

列类型	存储范围	存储占用空间(字节)	保存形式
DATETIME	1001年到9999年之间	8	YYYYMMDDHHMMSS 格式数字数据，保存值与时区无关
TIMESTAMP	1970～2038	4	即 UNIX 时间戳格式，显示的值与时区有关，慎用

故而时间日期最好选择 DATETIME，并在存入的时候传 UTC 时间戳。

结论

• 时间日期最好选择 DATETIME，并在存入的时候传 UTC 时间戳。
• IP 地址存 UNSIGN INT，使用内置函数 INET_ATON() 和 INET_NTOA() 转化
• MD5 戳适合用 CHAR(32) ，因为其定长，变更时不容易产生碎片。
• 电话号码 使用 UNSIGN INT，因数字类型比对效率比字符串的高。
• 一定是固定长度字符的用 char；变长的字符串使用 varchar。
• 超过 255 字节的考虑用 varchar 或者 text；并尽量用 varchar，检索效率更高。