背景:业务上存在逆向操作的场景下,需注意计数值在并发处理下可能的负值情况。
逆向操作:一般是指取消类操作,如点赞对应的取消点赞。应注意取消类操作对计数值准确性的影响。
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
两种类型。
- 不同类型的整型,存储字节长度不一,决定了可存储数值范围不同
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 规范解析 SQLSTRICT_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 | -- 查看当前 sql_mode(MySQL 5.7缺省配置) |
二、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
这类加法操作(加负一)不生效