MySQL数据类型

2021-09-07

原则 1. 更小的通常更好
尽可能使用正确存储数据的最小数据类型，较小的内存、磁盘和CPU缓存，并且CPU时间更少
2. 简单就好
【MySQL数据类型】简单的数据类型占用更少的cpu周期。如：

存储时间尽量用MySQL内置的datetime类型
存储IP可以用整数

3. 尽量避免使用NULL
通常情况下最好指定列为Not NULL，除非真的需要。

查询中包含NULL的列，对MySQL来说，优化更难，因为可为NULL的列使索引、索引统计、值比较都更复杂
如果计划对列加索引，应该避免设计成可为NULL的列
InnoDB存储引擎使用单独的位（bit）存储NULL值，对稀疏数据（很多值为NULL，只有少量非NULL值）具有很好的空间效率

SHOW CREATE TABLE shop_suborder

显示创建shop_suborder的语句，字段类型均为基本类型

整数类型 INT(11) 对大多数应用是无意义的，不会限制值的范围，只是规定了一些交互工具（如客户端）显示字符的个数，即int(11) == int(20)

TINIINT

8 bit ，存储-2^7 ~ 2^7-1 ，即-128~127
加上UNSIGNED 属性 0~255

SMALLINT

16 bit

MEDIUMINT

24bit

INT

32bit 存储 -2147483648 ~ 21 4748 3647

BIGINT

64bit
存储 -9223372036854775808L ~ 922 3372 0368 5477 5807L
实数类型 DECIMAL

尽量只在对小数点进行精确计算时才使用DECIMAL---如财务数据
允许最多存储65个数字
5.0以上MySQL版本DECIMAL 支持精确计算
可指定小数点前后允许的最大位数
数据量较大时，可用BIGINT代替，乘以相应倍数

FLOAT

4个字节存储

DOUBLE

8个字节存储，比DECIMAL更少的存储空间
比FLOAT更高的精度，更大范围
MySQL内部使用DOUBLE作为内部浮点计算的类型

字符串类型

每个字符串列可以定义自己的字符串集合和排序规则（校验规则）
VARCHAR、CHAR存储和具体的存储引擎有关

VARCHAR

存储可变长字符串
仅使用必要的空间，对性能有帮助（ROW_FORMAT=FIXED则每行都定长存储，浪费空间）
在更新时若变长则需要额外工作
需要1或2个额外字节记录字符串的长度，如VARCHAR(10) 需11字节存储空间
以下情节使用VARCHAR合适：
1. 字符串的最长比平均长度大很多
2. 列的更新很少，碎片不是问题
3. 使用了utf-8这样复杂的字符集
5.0以后，存储和检索会保留末尾空格
InnoDB把过长的VARCHAR存储为BLOB
VARCHAR(5) 和 VARCHAR(100)存储‘hello'是一样的，但是更长的列会消耗更多的内存，尤其是实用内存表进行排序或操作时会特别糟糕——只分配需要的空间

CHAR

定长，MySQL分配足够的空间
删除末尾空格
非常适合存储密码的MD5值
对于经常变更的数据，不易产生碎片
如CHAR(1) 占1个字节，VARCHAR(1)占2个字节（记录长度的额外字节）

BINARY和VARBINARY

二进制字符串
存储的是字节码
二进制比字符比较简单很多

BLOB

二进制很大的字符串
MySQL把每个BLOB和TEXT当做一个独立的对象处理
没有排序规则和字符集

TEXT

有排序规则和字符集
尽量避免使用TEXT 和 BLOB

TIPS: EXPLAIN 执行计划的Extra列包含 Using temporary 说明查询使用了隐式临时表
枚举 ENUM

有时可用枚举列代替常用的字符串类型
MySQL存储枚举时非常紧凑，内部将每个值在列表中的位置保存为整数
在.frm 文件中保存‘数字--字符串’的映射关系
排序时是按保存的整数排序，而非字符串
在添加或删除字符串时必须使用ALTER TABLE
VARCHAR/CHAR ---关联--- ENUM 比 VARCHAR/CHAR ---关联-- VARCHAR/CHAR慢
如果不是必须关联，则使用ENUM比较好

日期和时间

Mysql能存储的最小时间粒度--秒
BIGINT可用来存储微秒级别的时间戳，或者DECIMAL的小数部分

DATATIME

1001年--9999年，精确到秒
8个字节
可排序、无歧义
UNIX_TIMESTAMP() 函数将日期转化成时间戳

TIMESTAMP

4字节
表示范围比DATETIME小很多：1970年-2038年
时区相关
FROM_UNIXTIME() 函数将时间戳转化成日期
TIMESTAMP列默认Not NULL，与其他数据类型不同
空间效率更高

位数据类型 BIT

在一列中存储一个或多个true/false值
行为与存储引擎有关
MySQL将BIT当作字符串类型，而非数字类型
谨慎使用BIT类型
存储一个true/false值可创建CHAR(0)列

SET

保存很多true/false值可将这些列合并到一个SET数据类型
打包的位的集合表示，有效利用存储空间
FIND_IN_SET()、FIELD()这样的函数方便在查询中使用
改变列的代价高，需要ALTER TABLE
可以将8个位包装到一个TINIINT中，按位操作来使用----保存权限的访问控制列表

为标识列选择数据类型如关联查询的比较列、外键等

应选择和关联表中对应列一样的数据类型，类型间需要精确匹配
预留未来增长空间的前提下，选择最小的数据类型
整数通常是标识列最好的选择--可用AUTO_INCREAMENT
应避免用字符串类型作标识列，因为很消耗空间，并且比数字类型慢
MD5(), UUID(), SHA1()生成的字符串分布在很大空间，会导致INSERT以及一些SELECT很慢
1. 插入随机写到索引的不同位置，所以INSERT慢，聚簇存储引擎会产生聚簇索引碎片
2. SELECT语句慢，逻辑相邻的行会分布在磁盘和内存的不同地方
3. 随机值导致霍村对所有类型的查询效果都差
若存储UUID值，应移除“-”，用UNHEX()函数转换为16字节，并存储在BINARY(16)列中，检索是可通过HEX()函数格式化为十六进制格式
UUID虽然分布不均匀，但也还是有一定顺序的，但还不如递增的整数好用

特殊数据类型 IP存储

经常使用VARCHAR(15)存储IP，然而：
IPV4实际上是32位无符号整数，不是字符串，应使用无符号整数存储IP
MySQL提供INET_ATON() 和 INET_NTOA()转换IP地址和整数

推荐阅读

上一篇：泰晤士报（巴萨正式报价库鸟，1.42亿英镑）

下一篇：菲律宾主要城市简介以及风土人情