颠覆认知 - MySQL数字类型如何选择？

曾经终败给现在 2023-01-20 11:55 20阅读 0赞

点击上方蓝色“java大数据修炼之道”, 选择“设为星标”

每晚九点: 技术干货 ???? 必定奉上
    
    哈喽，各位新来的小伙伴们，大家好！由于公众号做了改版，为了保证公众号的资源能准时推送到你手里，大家记得将咱们的公众号 加星标置顶 ，在此真诚的表示感谢

在进行表结构设计时，数字类型是最为常见的类型之一，但要用好数字类型并不如想象得那么简单，比如：

*  怎么设计一个互联网海量并发业务的自增主键？用 INT 就够了？
 *  怎么设计账户的余额？用 DECIMAL 类型就万无一失了吗？

**以上全错！**

数字类型看似简单，但在表结构架构设计中很容易出现上述“设计上思考不全面”的问题（特别是在海量并发的互联网场景下）。所以我将从业务架构设计的角度带你深入了解数字类型的使用，期待你学完后，能真正用好 MySQL 的数字类型（整型类型、浮点类型和高精度型）。

## 数字类型 ##

### 整型类型 ###

MySQL 数据库支持 SQL 标准支持的整型类型：INT、SMALLINT。此外，MySQL 数据库也支持诸如 TINYINT、MEDIUMINT 和 BIGINT 整型类型（表 1 显示了各种整型所占用的存储空间及取值范围）：

![dc3bb3cbf284b81237b4ee0fdf9aac0c.png][]

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

来看一个“销售表 sale”的例子，其表结构和数据如下。这里要特别注意，列 sale\_count 用到的是 unsigned 属性（即设计时希望列存储的数值大于等于 0）：

其中，sale\_date 表示销售的日期，sale\_count 表示每月的销售数量。现在有一个需求，老板想要统计每个月销售数量的变化，以此做商业决策。这条 SQL 语句需要应用到非等值连接，但也并不是太难写：

mysql> SHOW CREATE TABLE sale\G
    *************************** 1. row ***************************
           Table: sale
    Create Table: 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)

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

然而，在执行的过程中，由于列 sale\_count 用到了 unsigned 属性，会抛出这样的结果：

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`s2`.`sale_count` - `test`.`s1`.`sale_count`)'

可以看到，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)
    SELECT
    
        s1.sale_date,
        IFNULL(s2.sale_count - s1.sale_count,'') AS diff
    FROM
        sale s1
        LEFT JOIN sale s2 
        ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date
    ORDER BY sale_date;
    
    +------------+-------+
    | sale_date  | diff  |
    +------------+-------+
    | 2020-01-01 |       |
    | 2020-02-01 | 2000  |
    | 2020-03-01 | -4000 |
    | 2020-04-01 | 3000  |
    | 2020-05-01 | -1000 |
    | 2020-06-01 | -8000 |
    +------------+-------+
    6 rows in set (0.00 sec)

### 浮点类型和高精度型 ###

除了整型类型，数字类型常用的还有浮点和高精度类型。

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

而数字类型中的高精度 DECIMAL 类型可以使用，当声明该类型列时，可以（并且通常必须要）指定精度和标度，例如：

salary DECIMAL(8,2)

其中，8 是精度（精度表示保存值的主要位数），2 是标度（标度表示小数点后面保存的位数）。通常在表结构设计中，类型 DECIMAL 可以用来表示用户的工资、账户的余额等精确到小数点后 2 位的业务。

然而，在海量并发的互联网业务中使用，金额字段的设计并不推荐使用 DECIMAL 类型，而更推荐使用 INT 整型类型（下文就会分析原因）。

## 业务表结构设计实战 ##

### 整型类型与自增设计 ###

在真实业务场景中，整型类型最常见的就是在业务中用来表示某件物品的数量。例如上述表的销售数量，或电商中的库存数量、购买次数等。在业务中，整型类型的另一个常见且重要的使用用法是作为表的主键，即用来唯一标识一行数据。

整型结合属性 auto\_increment，可以实现**自增功能**，但在表结构设计时用自增做主键，希望你特别要注意以下两点，若不注意，可能会对业务造成灾难性的打击：

*  用 BIGINT 做主键，而不是 INT；
 *  自增值并不持久化，可能会有回溯现象（MySQL 8.0 版本前）。

从表 1 可以发现，INT 的范围最大在 42 亿的级别，在真实的互联网业务场景的应用中，很容易达到最大值。例如一些流水表、日志表，每天 1000W 数据量，420 天后，INT 类型的上限即可达到。

因此，（敲黑板 1）**用自增整型做主键，一律使用 BIGINT，而不是 INT**。不要为了节省 4 个字节使用 INT，当达到上限时，再进行表结构的变更，将是巨大的负担与痛苦。

那这里又引申出一个有意思的问题：如果达到了 INT 类型的上限，数据库的表现又将如何呢？是会重新变为 1？我们可以通过下面的 SQL 语句验证一下：

mysql> CREATE TABLE t (
        ->     a INT AUTO_INCREMENT PRIMARY KEY
        -> );
    
    mysql> INSERT INTO t VALUES (2147483647);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> INSERT INTO t VALUES (NULL);
    ERROR 1062 (23000): Duplicate entry '2147483647' for key 't.PRIMARY'

可以看到，当达到 INT 上限后，再次进行自增插入时，会报重复错误，MySQL 数据库并不会自动将其重置为 1。

第二个特别要注意的问题是，（敲黑板 2）**MySQL 8.0 版本前，自增不持久化，自增值可能会存在回溯问题**！

mysql> SELECT * FROM t;
    +---+
    | a |
    +---+
    | 1 |
    | 2 |
    | 3 |
    +---+
    3 rows in set (0.01 sec)
    
    mysql> DELETE FROM t WHERE a = 3;
    Query OK, 1 row affected (0.02 sec)
    
    mysql> SHOW CREATE TABLE t\G
    *************************** 1. row ***************************
           Table: t
    Create Table: CREATE TABLE `t` (
      `a` int NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`a`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
    1 row in set (0.00 sec

可以看到，在删除自增为 3 的这条记录后，下一个自增值依然为 4（AUTO\_INCREMENT=4），这里并没有错误，自增并不会进行回溯。但若这时数据库发生重启，那数据库启动后，表 t 的自增起始值将再次变为 3，即自增值发生回溯。具体如下所示：

mysql> SHOW CREATE TABLE t\G
    *************************** 1. row ***************************
           Table: t
    Create Table: CREATE TABLE `t` (
      `a` int NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`a`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
    1 row in set (0.00 s

若要彻底解决这个问题，有以下 2 种方法：

*  升级 MySQL 版本到 8.0 版本，每张表的自增值会持久化；
 *  若无法升级数据库版本，则强烈不推荐在核心业务表中使用自增数据类型做主键。

其实，在海量互联网架构设计过程中，为了之后更好的分布式架构扩展性，不建议使用整型类型做主键，更为推荐的是字符串类型。

### 资金字段设计 ###

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

CREATE TABLE User (
      userId BIGINT AUTO_INCREMENT,
      money DECIMAL(8,2) NOT NULL,
      ......
    )

（敲黑板3）**在海量互联网业务的设计标准中，并不推荐用 DECIMAL 类型，而是更推荐将 DECIMAL 转化为 整型类型。** 也就是说，资金类型更推荐使用用分单位存储，而不是用元单位存储。如1元在数据库中用整型类型 100 存储。

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

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

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

字段存储时采用分存储，即便这样 BIG INT 也能存储千兆级别的金额。这里，1兆 = 1万亿。

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

注意，**在数据库设计中，我们非常强调定长存储，因为定长存储的性能更好。**

我们来看在数据库中记录的存储方式，大致如下：

![fbbf7635880bc1d33745d6f75bf8da03.png][]

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

![44b8a9f53958c4cbe2b12e6f9165e984.png][]

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

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

## 总结 ##

今天，我带你深入了解了 MySQL 数字类型在表结构设计中的实战。我总结一下今天的重点：

*  不推荐使用整型类型的属性 Unsigned，若非要使用，参数 sql\_mode 务必额外添加上选项 NO\_UNSIGNED\_SUBTRACTION；
 *  自增整型类型做主键，务必使用类型 BIGINT，而非 INT，后期表结构调整代价巨大；
 *  MySQL 8.0 版本前，自增整型会有回溯问题，做业务开发的你一定要了解这个问题；
 *  当达到自增整型类型的上限值时，再次自增插入，MySQL 数据库会报重复错误；
 *  不要再使用浮点类型 Float、Double，MySQL 后续版本将不再支持上述两种类型；
 *  账户余额字段，设计是用整型类型，而不是 DECIMAL 类型，这样性能更好，存储更紧凑。

文章来源：http://dwz.date/eZzA

在公众号后台对话框回复关键字：数据库获取MySQL和Oracle两大主流开发数据库实战教学视频，回复关键字：数据库 即可免费领取

End

干货分享

这里为大家准备了一份小小的礼物，关注公众号，输入如下代码，即可获得百度网盘地址，无套路领取！  
  
大数据就业班：大数据就业班

springboot 入门到精通视频资源全套; 在公众号后台回复关键字: springboot

毕向东java视频在公众号回复关键字:  毕向东

学习资源，包括Java,大数据，前端，Pyhon   公众号后台回复关键字：资源

大数据就业班　　公众号后台回复关键字：大数据就业班

python全栈　公众号后台回复关键字：python

xxx机构JavaEE云计算大数据全套视频  公众号后台回复关键字：javaee

Dubbo 入门到精通架构高级课程 公众号后台回复关键字：dubbo

win10激活工具在公众号后台回复关键字: win10激活工具

开发软件、工具在公众号后台回复关键字: 软件

eclipse、myeclipse视频教程 在公众号后台回复关键字: eclipse教程

Java高并发实战教程 在公众号后台回复关键字: Java高并发教程

java多线程教程 在公众号后台回复关键字: java多线程教程

高并发强化教程 在公众号后台回复关键字 :高并发

史上最易懂的设计模式视频 -- 马士兵系列  在公众号后台回复关键字 :设计模式

**mysql****、****oracle**入门到精通全套视频教程  在公众号后台回复关键字 : 数据库资源

加群： 微信交流群

▼点击下方↓↓↓公众号名片识别关注

####  ####

关注 java大数据修炼之道

每天学习java技术你想学的Java知识这里都有**![dc1d32ef5d338a1ddb9c5de3b81a75b8.png][]**

[dc3bb3cbf284b81237b4ee0fdf9aac0c.png]: /images/20221021/90d3681e269548d2baa5981792016e18.png
[fbbf7635880bc1d33745d6f75bf8da03.png]: /images/20221021/f01bd2bde4f141b597d620cc4a12ba00.png
[44b8a9f53958c4cbe2b12e6f9165e984.png]: /images/20221021/ed5720ea38af4079b7e4a81ae25c08bc.png
[dc1d32ef5d338a1ddb9c5de3b81a75b8.png]: /images/20221021/7d9a352aeeee43e696f7419f5d83ba63.png