MySQL SQL报错-BIGINT UNSIGNED value is out of range

前言

1 环境信息

2 错误描述

开发执行查询的时候，

select a.c1,
       a.c2,
       (a.c1-a.c2) as `数量`
from test.t1 a
where a.create_time >= '2022-2-1 00:00:00'
  and a.create_time < '2022-2-15 00:00:00';

报错如下：

org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1690] [22001]: Data truncation: BIGINT UNSIGNED value is out of range in '(`test`.`a`.`c1` - `test`.`a`.`c2`)'

3 排查

初步搜了一下报错，说是unsigned类型字段进行上述运算的时候，如果结果是负数，就会报错。下面验证

 select a.c1,
	a.c2
from test.t1 a
where a.create_time >= '2022-2-1 00:00:00'
  and a.create_time < '2022-2-15 00:00:00'
  and and a.c1 < a.c2;

  c1          |      c2             |
--------------+---------------------+
            -3|                    0|
           -15|                    0|
            -5|                    0|

该段时间内，有3条数据，a.c1-a.c2会出现负数。

而选择别的时间段，a.c1-a.c2不会出现负数的时候，则不会报错。

4 原因

确认是上面原因，数据里面，会有a.c1 小于 a.c2的情况。

5 解决方案

和开发确认，

1、是否是数据异常导致的；如果这几条数据有问题，是否可以修改数据，而不用修改查询语句；

2、如果数据不能修改，只能修改查询语句（但需要业务确认结果准确性）；

经确认，需要改写SQL。推荐SQL改写如下：

select a.c1,
       a.c2,
       (a.c1-a.c2) as `数量`
from test.t1 a
where a.create_time >= '2022-2-1 00:00:00'
  and a.create_time < '2022-2-15 00:00:00'
  and a.c1 > a.c2;

业务反馈没有问题了。

6 后续

经过半年，业务反馈又出现相同报错，提供的查询语句如下：

select a.c1,
       a.c2,
       (a.c1-a.c2) as `数量`
from test.t1 a
where a.create_time >= '2022-2-1 00:00:00'
  and a.create_time < '2022-2-15 00:00:00'
  and (a.c1 - a.c2) > 0;

发现上次改写语句的时候，where条件里面没有写成a.c1 > a.c2，写成了(a.c1 - a.c2) > 0，怀疑是where里的这个原因。把and (a.c1 - a.c2) > 0;注释掉，不会报错，确认是这个原因。

改成a.c1 > a.c2就好了。