MySQL 数据类型以及最优选取规则

数据类型

日常开发中，最常使用到的 MySQL 中常用的数据类型有 : 整型, 浮点型, 定点数, 字符型, 时间日期类型. 下面对其分别进行总结。

整型

格式为 : INT[(M)] [UNSIGNED] [ZEROFILL]

• 取值范围：如果是 unsigned 无符号位 , 则最大值翻倍, 如 tinyint unsigned 的取值范围为 (0~256).
• 注意：INT(1) 和 INT(10) 没有区别, 占用的内存都一样, 并不是代表其最大位数，只是加上 (M) 值后, 在 CMD 中显示的结果的宽度不同而已. 只有在加上 zerofill 零填充后 M 才表现出有点点效果, 比如 int(3) zerofill, 插入到数据库里的是 10, 则实际插入为 010, 也就是在前面补充加了一个0.
• Java 中的 Long 类型, 和 MySQL 中的 bigint 相对应.
• Java 中的 Integer 类型, 和 MySQL 中的 int 相对应.
• Java 中的 Boolean 类型, 和 MySQL 中的 tinyint 相对应.  false 代表 0, true 代表 1.

浮点型

• 设一个字段定义为 float(5,3), 5 表示在 CMD 中显示的总位数是 5 位, 3 表示小数位是 3 位.
• 小数位超过指定长度, 对后面的数字按照四舍五入截取. 所以如果插入一个数 123.45678, 实际数据库里存的是123.457, 进行了四舍五入截取.
• 小数位如果都是0, 则不保存小数位, 直接保存整数.

定点数

浮点型在数据库中存放的是近似值, 而定点类型在数据库中存放的是精确值. 所以对于金额等精度要求高的数据, 使用定点数 decimal 来保存.

decimal(m,d) 参数 m 是总个数, d 是小数位.  ( m<65, d<30, d

字符型

• char 类型的字符串检索速度要比 varchar 类型的快. 所以能 char 的优先 选择 char.
• char(n) 若存入字符数小于 n, 则以空格补于其后, 查询之时再将空格去掉. 所以 char 类型存储的字符串末尾不能有空格, varchar 不限于此.
• char(n) 固定长度, char(4) 不管是存入几个字符, 都将占用 4 个字节. varchar 可变长度, 是存入的实际字符数+1个字节（n<=255）或 2 个字节(n>255),  所以 varchar(4), 存入3 个字符将占用 4 个字节.
• varchar(10) 的 10 代表了申请的空间长度, 即存储的数据的最大长度, 而 int(10) 的 10 只是代表了展示的长度.

日期时间型

• 根据实际需要选择能够满足的最小存储的类型即可. 比如 : DATE 用于表示 年月日, 生日字段使用 DATE 类型即可. 这样不但能节省内存, 还能提高表的操作效率.
• 如果要记录年月日时分秒, 而且记录的年代比较久远, 最好使用 DATETIME. 因为 TIMESTAMP 可以表示的年代范围比 DATETIME 少的多.
• 如果记录的日期需要用到时区, 需要选择 TIMESTAMP.
• 一个表中至多只能有一个字段设置 CURRENT_TIMESTAMP. 并且 CURRENT_TIMESTAMP 只适合 TIMESTAMP 类型的字段.

CREATE TABLE USER(
  ...
  create_time timestamp NULL default CURRENT_TIMESTAMP,    -- 默认为当前时间
  ...
)
复制代码

字段类型的选取原则

• 字段类型优先级 : 整型 > date,time > char,varchar > blob

原因：整型运算快, 且节省空间. char, varchar 要考虑字符集的转换与排序时的校对集, 所以速度慢.

• 优先选择占空间小的类型.

原因 : 小空间的类型占用的磁盘, 内存, CPU 缓存, CPU 处理时间都更少.

• 对于字符型和数字型, 优先选择数字型.

比如 : 应该使用整型来存储 IP 地址, 而不是字符型. 应该使用日期时间类型来存储日期时间, 而不是字符型.

• 对于 char 和 varchar

如果列中的数据长度差不多一致, 比如 : 身份证号, 学号等, 则应该考虑使用 char 类型.

对于那些长度未知的字符串, 使用 varchar.

如果列中的最大数据长度小于 50 Byte (15个字符左右), 则一般考虑使用 char.

• 对于 decimal 和 float:

精确数据只能使用 decimal 类型.

非精确数据优先使用 float 类型, ( float 比 decimal 的存储空间小 )

• 对于那些可能值很有限的列, 使用 tinyint 代替 varchar / int

比如记录移动设备平台, 只有两个值：android, ios, 那么就可以使用 0 表示 android, 1 表示 ios, 并写好注释.

• 不要滥用 bigint, 比如文章表的主键 id 字段, 用 int 就行了, 21 亿篇文章的上限足够使用了.

\

以上原则主要是从以下角度考虑 :

• 查询速度：定长数据比变长数据查询快, 因为定长数据与数据之间的偏移是固定的, 很容易计算下一个数据的偏移.

而变长数据则还需要多一步去查询下一个数据的偏移量. 不过定长数据可能会浪费更多的存储空间.

• 排序速度：在对数据进行比较 ( 查询条件, JOIN 条件以及排序 ) 操作时, 同样的数据, 数字的处理比字符串快.

字符串的比较与当前的排序规则有关, 它需要去查询当前排序规则的字典顺序, 而数字不需要.