MySQL实战宝典 表结构设计篇 01 数字类型：避免自增踩坑

数字类型看似简单，但是表结构架构设计中很容易出现设计上思考不全面的问题，尤其是在海量并发的互联网场景下。接下来将从业务架构设计的角度深入了解数字类型的使用，真正用好MySQL的数字类型（整型类型，浮点类型和高精度类型）。

数字类型

整型类型

MySQL数据库支持SQL标准支持的整型类型INT、SMALLINT。此外MySQL数据库还支持诸如TINYINT、MEDIUMINT和BIGINT整型类型。下表表示各种整型类型所占存储空间及取值范围。

类型	占用空间	最小值~最大值（signed）	最小值~最大值（unsigned）
TINYINT	1	-128~127	0~255
SMAILLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT	4	-2147483648~2147483647	0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

• signed和unsigned属性（有符号和无符号）

在整型类型中，由signed和unsigned属性，其表示的是整型的取值范围，默认为signed。在设计时，不建议刻意去使用unsigned属性，因为在一些数据分析时，SQL可能返回的结果并不是想要得到的结果。

来看一个“销售表sale”的例子，其表结构和数据如下。这里需要特别注意，列sale_count用到的是unsigned属性。

mysql> SHOW CREATE TABLE sale;
+-------+--------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------+
| sale  | CREATE TABLE `sale` (
  `sale_date` date NOT NULL,
  `sale_count` int unsigned DEFAULT NULL,
  PRIMARY KEY (`sale_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci                     |
+-------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM sale;
+------------+------------+
| sale_date  | sale_count |
+------------+------------+
| 2020-01-01 |      10000 |
| 2020-02-01 |       8000 |
| 2020-03-01 |      12000 |
| 2020-04-01 |       9000 |
| 2020-05-01 |      10000 |
| 2020-06-01 |      18000 |
+------------+------------+
6 rows in set (0.00 sec)

现在有一个需求，需要统计每个月的销售数量变化，以此做商业决策。所以由如下SQL语句：

SELECT
    s2.sale_date,
    s2.sale_count - s1.sale_count AS diff 
FROM
    sale s1
    LEFT JOIN sale s2 ON DATE_ADD( s1.sale_date, INTERVAL 1 MONTH ) = s2.sale_date 
ORDER BY
    s1.sale_date;

然而，执行过程中，由于sale_count列使用到了unsigned属性，会抛出如下结果：

BIGINT UNSIGNED value is out of range in '(`lg_MySQL`.`s2`.`sale_count` - `lg_MySQL`.`s1`.`sale_date`)'

MySQL提示用户计算的结果超出了范围，其实，这里MySQL要求unsigned数值相减之后依然为unsigned，否则就会抛出如上错误。

为了避免这个错误，需要对数据库参数SQL_MODE设置为NO_UNSIGNED_SUBTRACTION，允许相减的结果为signed，这样才能得到最终想要的结果。

mysql> SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    s2.sale_date,
    s2.sale_count - s1.sale_count AS diff 
FROM
    sale s1
    LEFT JOIN sale s2 ON DATE_ADD( s1.sale_date, INTERVAL 1 MONTH ) = s2.sale_date 
ORDER BY
    s1.sale_date;
+---------------+-------+
| sale_date      |  diff    |
+---------------+-------+
| 2020-02-01 | -2000 |
| 2020-03-01 |  4000 |
| 2020-04-01 | -3000 |
| 2020-05-01 |  1000 |
| 2020-06-01 |  8000 |
| NULL       |  NULL |
+------------+-------+
6 rows in set (0.00 sec)

/***************************************************
DATE_ADD() 函数向日期添加指定的时间间隔。
DATE_SUB() 函数向日期减去指定的时间间隔。
语法格式：
DATE_ADD(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit)
 - date 参数是合法的日期表达式
 - expr 参数是希望添加的时间间隔
 - unit 参数是时间间隔的单位
Type 值
MICROSECOND 毫秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 周
MONTH 月
QUARTER 季度
YEAR 年
SECOND_MICROSECOND 秒和毫秒
MINUTE_MICROSECOND 分钟和毫秒
MINUTE_SECOND 分钟和秒
HOUR_MICROSECOND 小时和毫秒
HOUR_SECOND 小时和秒
HOUR_MINUTE 小时和分钟
DAY_MICROSECOND 天和毫秒
DAY_SECOND 天和秒
DAY_MINUTE 天和分钟
DAY_HOUR 天和小时
YEAR_MONTH 年和月
***************************************************/

浮点类型

MySQL之前的版本中存在浮点类型Float和Double，但这些类型因为不是高精度，也不是SQL标准的类型，所以在真实的生产环境中不推荐使用，否则在计算时，由于精度类型问题，会导致最终的计算结果出错。

MySQL 8.0.17版本开始，当创建表用到类型Float或Double时，会抛出如下的警告：MySQL提醒用户不该用上述浮点类型，甚至提醒将在之后版本中废弃浮点类型。

Specifying number of digits for floating point data types is deprecated and will be removed in a future release

高精度类型

MySQL中的高精度DECIMAL类型可以使用，当声明该类型列时，必须要指定精度和标度,如下：

salary DECIMAL(8,2) -- 其中8是精度，表示保存值的主要位数，2是标度，表示小数点后保存的位数

通常在表结构设计中，类型DECIMAL可以用来表示用户的工资、账户的余额等精确到小数点后2位的业务。

但是，在海量并发的互联网业务中使用，金额字段的设计并不推荐使用DECIMAL类型，而更推荐使用整型类型以分为单位进行存储。

业务表结构设计实战

整型类型与自增设计

整型类型在真实业务中的使用场景

• 在业务中表示某件物品的数量，如电商中的库存数量，购买次数，销售数量

• 作为表的主键，用来唯一标识一行数据，结合AUTO_INCREMENT属性，实现自增功能

但在表结构设计时用自增做主键，需要特别注意以下两点，否则会对业务造成灾难性的打击

• 用BIGINT做主键，而不是INT （不要为了节省 4 个字节使用 INT，INT类型上限很容易达到）

• 自增值并不做持久化，可能会有回溯现象（MySQL 8.0 版本前，当删除自增为3的记录，下一个自增值为4，但数据库发生重启后，表的自增起始值会变成3，即自增值发生回溯）

  • 升级MySQL版本到8.0版本，每张表的自增值会持久化；

  • 若无法升级数据库版本，则强烈不推荐在核心业务表中使用自增数据类型做主键

  • 其实在海量互联网结构设计中，为了之后更好的分布式结构扩展性，更为推荐使用字符串类型作为主键

资金字段设计

在用户余额、基金账户余额、数字钱包、零钱等业务设计中，由于字段都是资金字段，通常程序员习惯使用DECIMAL类型作为字段的选型，因为这样可以精确到分，如DECIMAL(8,2)。

在海量互联网业务的设计中，并不推荐使用DECIMAL类型，而是更推荐将DECIMAL转化为整型类型，资金类型更推荐使用以分为单位存储，而不是以元为单位存储，如1元在数据库中用整型类型100存储。

金额字段的取值范围如果用DECIMAL表示的，如何定义长度呢？因为类型DECIMAL是个变长字段，若要定义金额字段，则定义为DECIMAL(8,2)是远远不够的，这样只能存储最大值为999999.99，百万级的资金存储。

用户的金额至少要存储百亿的字段，而统计局的GDP金额字段则可能达到数十万亿级别。用类型DECIMAL定义，不好统一。

另外，重要的是类型DECIMAL是通过二进制实现的一种编码方式，计算效率远不如整型来的高效。因此推荐使用BIGINT来存储金额相关的字段。

字段存储是采用分存储，即便这样BIGINT也能存储千兆几倍的金额。1兆=1万亿。

这样的好处是，所有的金额相关字段都是定长字段，占用8个字节，存储高效。另一点，直接通过整型计算，效率更高。

在数据库设计中，非常强调定长存储，因为定长存储的性能更好

数据库记录的存储方式大致如下：

修改前

若发生更新，如果记录1原先的空间无法容纳更新后记录1的存储空间，这时数据库会将记录1标记为删除，寻找新的空间给记录1使用，如：

修改后

上图中，*记录1表示的就是原先记录1占用的空间，而这个空间后续将变成碎片空间，无法继续使用，除非人为地进行表空间地碎片整理。

那么，当使用BIGINT存储金额字段的时候，如何表示小数点中的数据呢？其实，这部分完全可以交由前端进行处理并展示，作为数据库本身，只要按分进行存储即可。

总结

• 不推荐使用整型类型的属性UNSIGNED，如非要使用，参数SQL_MODE务必额外添加上选项NO_UNSIGNED_SUBTRACTION;

• 自增整型类型做主键，务必使用BIGINT类型，而非INT,否则后期表结构调整代价巨大；

• MySQL版本前，自增整型会有回溯问题，做业务开发的你一定要了解这个问题；

• 当达到自增整型类型的上限值时，再次自增插入，MySQL数据库会报重复错误；

• 不要再使用浮点类型Float、Double,MySQL后续版本将不再支持上述两种类型；

• 账户余额字段，设计使用整型类型，而不是DECIMAL类型，这样性能更好，存储更紧凑。

• IP可以使用整型存储，IP本身是个变长字段，但是通过INT存储，固定占用4个字节。提升了存储效率和性能

  • 但是在使用INT存储IP字段的时候，切记要使用UNSIGNED，官方文档中有提示：To Store values generated by INET_ATON(), use an INT UNSIGNED column rather than INT, which is signed.

• 日期类型不建议用整型存储，因为效率不高，性能也不好，也不能存储毫秒值