MySQL报错：ERROR 1690 (22003): BIGINT UNSIGNED value is out of range

今天开发人员遇到一个问题，具体的报错信息如下：

mysql>select 
    ->    IF( (a.dateline-b.dateline) > 900, 1, 0) as st
    -> from xxx.xxx1 a left join xxx.xxxx2 b on a.tid=b.tid and b.first=0
    -> where a.fid in(
    ->   173,1,31,246,254,255,264,265,267,269,272,274,277,275,271,273,276
    -> ) and a.authorid in (553931397,300563341,135382780,593155731,124641828,734724948,381613985)
    -> and a.first=0;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`xxx`.`xxx1`.`dateline` - `xxx`.`xxx2`.`dateline`)'

从报错信息看，是由于在计算 (a.dateline-b.dateline) 的结果越界导致的，就出现ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in..的错误，因为结果可能会出现负数，但是出现负数应该是正常的结果，那为什么会出现如上的报错呢？

接下来我们看下具体的表结构：

CREATE TABLE `XXXX1` (
  `pid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  .....
  `subject` varchar(80) NOT NULL DEFAULT '',
  `dateline` int(10) unsigned NOT NULL DEFAULT '0',
  `message` mediumtext NOT NULL,
  .....
  PRIMARY KEY (`pid`),
) ENGINE=InnoDB AUTO_INCREMENT=40430633 DEFAULT CHARSET=UTF8

部分表结构省略，重点关注dateline字段类型，发现dateline设置的是int类型，并且是无符号类型，由于结果可能会出现负数，所以出现文章最开始的报错信息；

MySQL针对整型来说，有 signed 和 unsigned 属性，其表示的是整型的取值范围，默认为 signed。在设计时，个人不建议你刻意去用 unsigned 属性，因为在做一些数据分析时，SQL 可能返回的结果并不是想要得到的结果，如下是每种整型的取值范围：

既然已经初步定位到问题的原因，那么如何解决该问题呢？下面我们来做个测试，并且提供以下几种解决方法：

创建测试表，插入一条数据：

mysql>CREATE TABLE t ( a INT UNSIGNED, b INT UNSIGNED )    ENGINE=INNODB; 
Query OK, 0 rows affected (0.05 sec)

mysql>insert into t values(1,2);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT a-b FROM t; 
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`wjqtest`.`t`.`a` - `wjqtest`.`t`.`b`)'

解决方法一：修改字段类型，从unsigned调整为signed

mysql>alter table t modify `a` int DEFAULT NULL,modify `b` int DEFAULT NULL;
Query OK, 1 row affected (0.12 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>show create table t\G                                                 
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>SELECT a-b FROM t;
+------+
| a-b  |
+------+
|   -1 |
+------+
1 row in set (0.00 sec)

解决方法二：修改SQL语句，使用cast强制转化为signed

mysql>select cast(a as signed)-cast(b as signed) FROM t;
+-------------------------------------+
| cast(a as signed)-cast(b as signed) |
+-------------------------------------+
|                                  -1 |
+-------------------------------------+
1 row in set (0.00 sec)

这种方法不需要修改字段类型就可以解决该问题；

解决方法三：修改sql_mode选项，添加NO_ENGINE_SUBSTITUTION选项（生产环境的不建议这种方案）

如下是官方文档关于NO_ENGINE_SUBSTITUTION选项的解释：

Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default.

默认情况下，整数值之间的减法（其中一个为UNSIGNED类型）会生成无符号结果。如果结果为负值，则会出现文档开始的报错；

mysql>set session sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql>show variables like '%sql_mode%';
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                         |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,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)

mysql> SELECT a-b FROM t;
+------+
| a-b  |
+------+
|   -1 |
+------+
1 row in set (0.00 sec)

好了，今天就到这里，如有问题随时沟通；