mysql unsigned/int字段越界

Posted on | In | Visitors

线上的MySQL错误日志中遇到了以下问题

1
Data truncation: BIGINT UNSIGNED value is out of range in `(database.tablename.fieldname)`

MySQL版本为:

1
2
3
4
5
6
7
mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.21-log |
+------------+
1 row in set (0.00 sec)

引起这个错误的原因是sql语句中对unsigned字段进行了递减操作的结果为负数导致的,

1
update database.tablename set fieldname=fieldname-num

而MySQL的模式是默认的mode:

1
2
3
4
5
6
7
mysql> show variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)

解决方案有3种:

1.程序中做数据合法性检查，先查询出来，递减之后判断结果，结果正常再去插入，不过这样需要2次sql操作，需要保证操作的原子性，不然容易引起数据一致性问题.

2.在同一个session中临时修改MySQL的sql_mode:

1
2
SET sql_mode='NO_UNSIGNED_SUBTRACTION';
UPDATE database.tablename SET fieldname=fieldname-num

NO_UNSIGNED_SUBTRACTION 模式下，对于unsigned字段，如果插入负值，则会讲该字段的值自动设置为 0 .

3.如果unsigned字段能接受负值,则可以使用CAST函数处理:

1
2
SET sql_mode='NO_UNSIGNED_SUBTRACTION';
UPDATE database.tablename SET fieldname=CAST(fieldname-num AS UNSIGNED);

需要注意的是CAST的表现会因不同的sql_mode而不同，需要合适的sql_mode和CAST配合使用.

参考:

0%