MySQL中-1操作负值超出unsigned阈值

背景：业务上存在逆向操作的场景下，需注意计数值在并发处理下可能的负值情况。

逆向操作：一般是指取消类操作，如点赞对应的取消点赞。应注意取消类操作对计数值准确性的影响。

MySQL本身字段类型支持无符号和有符号，一般情况下，当字段为unsigned int时，是不支持负值的。此时unsigned int字段写入负值时，会产生溢出，报错信息如下：

1
BIGINT UNSIGNED value is out of range in 'xxxxx'

本文从 MySQL 整型数值类型的范围和不同sql_mode模式的影响对上述问题展开讨论。

一、MySQL 中整型范围和+/-算术运算的处理

1.1 整型数值范围

MySQL 支持的整型有TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT，同时不同长度的整型支持无符号unsined和有符号signed两种类型。

MySQL 整型数值范围

  • 不同类型的整型，存储字节长度不一，决定了可存储数值范围不同
  • TINYINT：1字节，有符号型范围[-128, 127]，无符号型范围[0, 255]
  • INT：4字节，有符号型范围[-2147483648, 2147483647]，无符号型范围[0, 4294967295]；这就是常用有符号整型signed int最大值为21亿多，无符号42亿多
  • BIGINT：8字节，有符号[-2^63, 2^63-1]，无符号[0, 2^64-1]

1.2 不同算术运算符处理

MySQL 中算术运算的结果按如下规则处理：

  • 对-, +, *三种运算符，如两个操作数为int整型，则结果按64位的bigint保
    存，防止结果溢出
  • 两个操作数为整型，若其中之一为无符号unsigned int，则结果为无符号；对减法操作而言，当开启NO_UNSIGNED_SUBTRACTION后，尽管任一操作数为无符号，但结果按有符号signed 保存。
    • 重要的，这里强制要求：两个整型操作数，只要其中一个为无符号，则结果一定为无符号
    • 这看起来有诸多不合理，如无符号和有符号运算后结果为有符号，则必然结果溢出！比如2+-3=-1，这种情况下结果-1在保存成无符号时，必然溢出了。为何 MySQL 要使用这样的处理策略呢？
    • 开启NO_UNSIGNED_SUBTRACTION模式，也仅影响减法操作时，结果按有符号处理，来避免结果为负值的情况
  • 如+, -, /, *, %中任一操作数为实数或字符串值，结果精度保持操作数的最大精度
  • 除法操作/中，结果是第一个操作数的小数位数+系统变量div_precision_increment值（缺省为4）。表达式5.05 / 0.014的结果的小数位数为六位（360.714286）

1.3 sql_mode 的作用

不同的sql_mode可以在会话级或全局影响 MySQL 服务器的SQL模式。不同 SQL 模式对应不同方面上 SQL 限制的严格程度不同。常见的几种模式有：

  • ANSI：使用标准 SQL 规范解析 SQL
  • STRICT_TRANS_TABLES： 严格事务表，在插入事务表异常时，回滚整个语句
  • TRADITIONAL：与传统的 SQL 数据库保持一致，在异常时直接出 error，而不是给 warning

本文重点对NO_UNSIGNED_SUBTRACTION这种模式详细描述下。前面章节中算术运算符规则中，我们已经知道了一个重要规则：

  • 两个整型操作数的算术运算时，如果有一个无符号型，则结果为无符号

但在减法操作时，两个无符号想减，结果很有可能出现有符号型。
如无符号型字段值为2，进行-3操作后，2-3=-1，在缺省配置下(结果保存为无符号型)，此时将-1保存为无符号型时会发生溢出。

1
BIGINT UNSIGNED value is out of range in 'xxxxx'

开启NO_UNSIGNED_SUBTRACTION模式的作用：

  • 减法操作中，两个整型中，如果结果为负值，则结果按 signed 有符号型保存为无符号型
  • 上述操作时，将负值结果写入 unsigned int 字段时，结果将裁剪为0值，而不会发生溢出错误或警告
  • 特别注意：此模式仅影响「减法」操作，对其他运算无影响

查看和开启不同 sql_mode 的命令：

1
2
3
4
5
6
7
8
9
10
11
12
-- 查看当前 sql_mode（MySQL 5.7缺省配置）
select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- 设置 sql_mode 为NO_UNSIGNED_SUBTRACTION模式。
-- 注意应详细评估此修改，且 sql_mode 可以是多重设置的组合配置
set sql_mode='NO_UNSIGNED_SUBTRACTION';

二、MySQL 中 x+-1 和 x-1 区别

常见的，在进行逆向操作时会对字段值进行减一操作，但存在「加负一」和「减一」两种操作方式。
从结果上看，这两种操作方式多数时相同的。
在存在结果负值的情况时，NO_UNSIGNED_SUBTRACTION模式会影响减法操作，而对加法操作无效。
也就是说，如果开启NO_UNSIGNED_SUBTRACTION模式，「减一」操作在负值写入 unsigned int 字段时会裁剪为0，而「加负一」操作会发生字段溢出错误。

2.1 业务上如何安全的进行-1操作

  • 方式1：加写锁select for update，字段值>0时，更新字段值-1
  • 方式2：update xx set x=x-1 where xxx and x>0，通过x>0条件将更新效果仅作用在正值字段上。这也是利用 MySQL 本身的数据一致性特型来保证-1操作时不会出现负值
  • 方式3：update xx set x=if(changeValue>=0 or x>(-changeValue), x+changeValue, 0，通过if判断条件保证结果不出现负值，成本是降低了 SQL 可读性。

从业务上，入口处应做好业务校验，避免逆向操作对数据准确性的影响。

三、总结

  • 算术运算符时，两个整型操作数，如其中有 unsigned 无符号 整型，则结果为 unsigned int
  • 算术运算符的结果超出字段类型阈值时，则保存为对应类型的最大值
    • 通过show warnings;可查看发生的溢出警告信息Warning | 1264 | Out of range value for column 'xxx' at row
  • MySQL的NO_UNSIGNED_SUBTRACTION模式，在「减法」操作时，如负值结果保存至 unsigned字段时将裁剪为0保存，最终「减法操作结果不是无符号型」
  • 注意NO_UNSIGNED_SUBTRACTION模式仅影响减法操作，对x=x+-1这类加法操作(加负一)不生效

四、参考