MySQL进阶 -- 数据类型

前言

在先前的MySQL基础章节 MySQL 小结（一） 中介绍过MySQL表的创建等基础操作，本节归纳阐述MySQL支持的数据类型以及各类型的特征。

在数据库建模时，有几点基础原则：
1，在确保数据的存储范围情况下，选用尽可能小的数据类型进行存储。
2，能够赋予默认值且不影响认知的情况下，尽量避免存储NULL值，不绝对。

整型

整型数据在MySQL中可以存储的数据类型如下：

类型	所占空间（Byte）	无符号范围	有符号范围
TINYINT	1	-2^7 ~ 2^7-1	0 ~ 2^8-1
SMALLINT	2	-2^15 ~ 2^15-1	0 ~ 2^16-1
MEDIUMINT	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

在整型数据使用时，会有指定宽度的习惯，例如 int(11)，本质上该字段还是int类型，占4个字节，但是其在客户端操作时最多只显示11位。

实数

实数就理解为小数，MySQL中支持的型数据如下：

类型	所占空间（Byte)
FLOAT(M, D)	4
DOUBLE(M, D)	8
DECIMAL(M, D)	不固定

尽可能解释清楚这三种类型的精度和大小，不能担保一定正确，在MAC 本机5.7版本的MySQL中一定是通过验证的。
此三种类型一定可以用“()“指明 M，D，其中：
M：浮点数对应的数字总长度，整数位数 + 小数位数
D：小数位数

非精确浮点

关于FLOAT和DOUBLE，MySQL官方给出的说明：https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html
原文解释对于FLOAT和DOUBLE都是非精确浮点数，MySQL用4个字节表示单精度浮点FLOAT和DOUBLE表示双精度浮点（关于单精度，双精度浮点，最后会补充说明）。此两种浮点均是不能精确表示浮点数，并非小数点后位数多不能精确表示，如下图：

7.22在FLOAT中无法精确表示。这是单精度浮点精度特性决定的。

精确浮点

DECIMAL类型表示精确浮点，MySQL官方给出的说明https://dev.mysql.com/doc/refman/5.6/en/precision-math-decimal-characteristics.html。
首先是DECIMAL的表示范围，这里的M就是数据总长度，D就是小数点后的数字长度，均表示十进制的数字长度。

对于DECIMAL(6, 3)能够表示的数据大小是 -999.999 ~ 999.999。演示数据库数据库版本5.7。
关于DECIMAL数据类型，其整数部分和小数部分是分开存储的。
在进行运算时，CPU是支持对FLOAT和DOUBLE计算，但CPU是不支持DECIMAL类型数据进行直接运算的，MySQL所有DECIMAL类型的计算，是MySQL服务自身实现的(5.0版本后，早期版本MySQL是由浮点运算实现DECIMAL运算，进而会有精度丢失)。
在所占空间上DECIMAL也并非是固定长度，而是根据所指定的M，D来决定的。官网给出的数字位数和所占字节关系：

数字位数	所占空间（Byte)
0	0
1-2	1
3-4	2
5-6	3
7-9	4

DECIMAL(18,9)表示整数部分9位，小数部分9位，总计8个字节。DECIMAL(20,6) 14位整数部分，6位小数部分，小数部分3个字节。整数部分 9位占4个字节，还剩5位占3个字节，整数部分总计占7个字节，由此DECIMAL(20,6)占10个字节。

计算精度是一个非常繁琐的问题，处理不好系统业务上经常会出现误差。深切感受，特别是在涉及财务问题上，“差一分钱”的场景频繁出现。
避免此种情况的方法：
1，对于数据精度的管控，可以用大整数替代浮点，例如商品单价精确到分，那么就存整数表示多少分取代两位小数。
2，避免除法运算，一定要避免。如果非要进行某些联动性的除法计算，务必在数据入库之前将数据计算清楚。将误差信息反馈给用户，之后入库的是用户认可的和实际业务略有误差的数据。

字符串

类型	n 范围	说明
CHAR(n)	0～2^8-1	固定长度字符串
VARCHAR(n)	0～2^16-1	可变长度字符串

MySQL官方给出的说明：https://dev.mysql.com/doc/refman/5.7/en/char.html
CHAR类型存储固定长度的字符串，在简表指定字段信息时可以指定该字段所要存储的字符长度，VARCHAR亦是如此，不同之处在于：
1：CHAR类型能够指定的n最长位255，而VARCHAR是65535。
2：CHAR是固定长度，所有列所占的存储空间固定，VARCHAR会根据实际字符长度分配
3：CHAR会自动去除字符串末尾空格，VARCHAR则不会
4：VARCHAR类型当 n <= 255时需要一个字节存储n，当 n > 255时需要 2个字节存储n，CHAR不存需要

CHAR和VARCHAR存储 n 长度字符串所占空间大小是多少呢？
这个完全取决于所建表用的字符集！
例如使用 CHARSET=’utf8’，我们知道utf8是以3个字节存储一个字符，则CHAR所占空间为 n*3 个字节。

对于上图展示的表 c_table 其中 v_column 字段能给的长度n最长多少？是65535么？

与CHAR和VARCHAR对应有 BINARY和VARBINARY，存储二进制字符串，不常用不做纠结。

文本

类型	容纳字符最大长度
TINYBLOB	0～2^8-1
TYNYTEXT	0～2^8-1
BLOB	0～2^16-1
TEXT	0～2^16-1
MEDIUMBLOB	0～2^24-1
MEDIUMTEXT	0～2^24-1
LONGBLOB	0～2^32-1
LONTEXT	0～2^32-1

对于文本型的数据，首先需要知道其占用空间很大。TEXT类型的仅用于存储文本，BLOB是二进制数据，除文本外，还可保存图片，视频等信息。
在数据库中进行排序操作时，MySQL只会对其中 max_sort_length配置的长度字段进行排序，默认max_sort_length = 1024。

注意：存储引擎 Memory 不支持TEXT和BLOB。

日期和时间

MySQL官方给出说明：https://dev.mysql.com/doc/refman/5.7/en/datetime.html。MySQL支持DATE, DATETIME 和 TIMESTAMP等类型的时间数据(TIME，YEAR类型请自行查阅官网介绍)。

类型	所占空间（Byte)	格式	范围
DATE	3	‘YYYY-MM-DD’	‘1000-01-01’ ～ ‘9999-12-31’
DATETIME	8	‘YYYY-MM-DD HH:MM:SS’	‘1000-01-01 00:00:00’ ～ ‘9999-12-31 23:59:59’
TIMESTAMP	4	‘YYYY-MM-DD HH:MM:SS’	‘1970-01-01 00:00:01’ ～ ‘2038-01-19 03:14:07’ (UTC)

注意：
1，MySQL在 5.6.4及以更高版本中支持了微秒。
DATETIME 支持 ‘1000-01-01 00:00:00.000000’ ~ ‘9999-12-31 23:59:59.999999’
TIMESTAMP ‘1970-01-01 00:00:01.000000’ ~ ‘2038-01-19 03:14:07.999999’
2，MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) 官网原话。意味TIMESTAMP存储的时间都是utc时间，只是数据库会对时间自动转化，DATETIME类型不会。即为当链接MySQL的机器时区与MySQL数据库设置的时区不一致时，TIMESTAMP在读取时会自动转换(条件有限，未能验证)。

其他类型

ENUM

枚举类型，官方介绍：https://dev.mysql.com/doc/refman/5.7/en/enum.html
枚举类型的核心是用 数字代替字符串存储。

注意：
1，枚举使用避免用 ‘1’, ‘2’…的字符数字，会引起混淆
2，枚举排序时是按照字符对应的数字序，并非字符串本身的字母序。

SET

集合类型，可以有0个或多个值，某一字段定义为：

`test_colunm` SET ('A', 'B') NOT NULL DEFAULT '';

test_colunm可以为 ”, ‘A’, ‘B’, ‘A, B’这四者任一个，均合法。具体请参见：https://dev.mysql.com/doc/refman/5.7/en/set.html。
其他未尽之处请看管移步官网自行查阅。

MySQL模式

MySQL在运行时，会有全局的运行模式。通过：

-- 查询数据库模式，5.7.16版本
> SELECT @@sql_mode;
-- 个人机器执行结果
*************************** 1. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

查询结果中如果有STRICT_TRANS_TABLES，表示当前MySQL开启了严格模式。常见的严格模式下：

• 对自增长字段不支持 赋予一个空字符串，可以手动指定其他诸如字符型数字 ‘111’
• 对于NOT NULL字段不支持插入NULL值
• 对于TEXT类型不支持有默认值

官网中对模式的各个字段做了详细说明https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html，此处略微解释如下。
ONLY_FULL_GROUP_BY：不支持未检索的列进行排序操作。
STRICT_TRANS_TABLES：对事务型引擎开启严格模式，也可能为非事务型开启，上 面官网地址的“Strict SQL Mode”给出了详细介绍严格模式。
NO_ZERO_IN_DATE：严格模式下限定了在日期年部分为非0时是否允许月，和日为0。
NO_ZERO_DATE：严格模式下全0的日期字段视为非法。
ERROR_FOR_DIVISION_BY_ZERO：严格模式下取模函数MOD(m, 0)，在执行INSERT, UPDATE操作报错。非严格录入数据NULL。
NO_AUTO_CREATE_USER：不允许 GRANT命令自动创建新用户，除非指定了密码。
NO_ENGINE_SUBSTITUTION：在CREATE TABLE or ALTER TABLE 指明一个未启开的存储引擎时，不允许用默认存储引擎替代。

数据存储

此处仅仅介绍InnoDB的存储限定，官网介绍：https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html。
InnoDB在5.6.9及更高版本中单张表最多支持1017字段。

-- 查看innodb_page_size大小，单位byte
> SHOW VARIABLES LIKE 'innodb_page_size%';
-- 结果 16384 byte 为16Kb
innodb_page_size     | 16384

For example, the maximum row length for the default innodb_page_size of 16KB is about 8000 bytes. (官网原例子)
按照上述默认配置，单行数据在InnoDB中最多存储长度 8Kb数据。
此外，尽管 InnoDB支持单行的数据大小超过65535，但是MySQL强制限定一行的总存储大小不得超过65535个byte，且实际存储从第2个byte开始。

解答：
之前字符串类型中留下问题，v_column 最多能有多长？

答案是：21588
utf-8 是3字节编码
id 4 byte，c_column 255*3 byte，超过255存储长度2byte
(65535 - 4 -255*3 - 2 ) / 3 = 21588 byte

附录

单精度浮点

wiki百科上给出了详细介绍：https://zh.wikipedia.org/wiki/%E5%96%AE%E7%B2%BE%E5%BA%A6%E6%B5%AE%E9%BB%9E%E6%95%B8 详细定义及表示方法请参见该说明。
那么对于 11.375这个浮点单精度如何表示？
11对应2进制表示：1011
0.375对应2进制表示：0.011 = 2^(-2) + 2^(-3)
11.375二进制表示为 1011.011 = 1.011011 * 2^3
所以单进度表示为：
0 10000010 0110110….0 共32位。

在FLOAT类型中，0.22在MySQL中是精确表示了，且1.22，2.22，3.22都精确表示了，直至4.22不能精确表示了，因为4.22整数部分占了3位，往后表示进度表示不下了，所以网上更大的 5.22，7.22都表示不下，可以试试。

范式

MySQL在设计时有一些范式的理念避免重复数据，感兴趣的看官可去自行搜索下。核心思想在于平衡重复数据和读取数据效率。