昨天线上出现重大bug, 用户通过修改 接口请求参数 1 改为 ‘000000000000…1’ 的方式 造成我们mysql 数据库字段在 insert 操作 decimal 字段类型溢出,

举例

-- mysql 5.6 版本运行

MySQL [test]> insert into `table_name` (id, num )values(null ,'0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001');

MySQL [test]> select * from table_name
    -> ;
+----+-----------------------+
| id | num                   |
+----+-----------------------+
|  1 | 999999999999.99999999 |
+----+-----------------------+

线上的情况 就如上面的问题一样 外部参数 没有做格式化处理 插入时带有过多的0 decimal 字段类型溢出 生成 999999999999.99999999 …..

本地docker 安装 mysql5.7 复现问题


Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 5724
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [money]> insert into `table_name` (id,num)values(null,'000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001');
ERROR 1366 (HY000): Incorrect decimal value: '00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000' for column 'num' at row 1

直接插入 抛错 ERROR 1366 (HY000): Incorrect decimal value: '0000000000000000000000000000000000000000000000000' for column 'num' at row 1 , decimal值不正确 , 插入失败.

原因在于 mysql5.7 默认的 sql_mode 配置问题:

MySQL [money]> show variables like '%sql_mode%'\G;
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: 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

sql_mode 详情可以看 MySQL的 sql_mode 合理设置

mysql 5.7 默认 sql_mode 配置中 带有 STRICT_TRANS_TABLES:严格模式
在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制(翻译可能有问题 不好理解)

分别设置 sql_mode 为 ‘’ 和 STRICT_TRANS_TABLES, 结果截然不同.
设置为严格模式下 抛错 Incorrect decimal value: ... 设置为空 插入成功.
1通过前面加入大量的0 插入数据库变成了 999999999.9999999.

我又问过DBA 为什么不选择用严格模式 , DBA答案是: 线上代码基本都会失败.
举例 :
程序运算得到一个浮点型字符串 0.123456789, 需要插入数据库 字段 num 中 , num 类型 decimal(20,8) 小数点后精度8位,
而你要插入的是9 位 正常情况入库 结果 0.12345678 仅保留8位, 如果使用严格模式 插入会失败抛错.

补充下: 更新或插入的字段为字符串类型 写入mysql 非严格模式会造成 decimal溢出, 数字类型不会发生溢出问题.

总结 :
外来参数一定要做格式化处理 数字类型也不能放过. 之前仅是防止数字为负数 或者 浮点溢出问题 , 忽略了 decimal的值不正确 导致的溢出问题.mysql 对字符串转换数字类型不是很给力呀。