• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

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

MySQL SEian.G 4个月前 (04-11) 244次浏览 已收录 0个评论
文章目录[隐藏]

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

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 可能返回的结果并不是想要得到的结果,如下是每种整型的取值范围:

类型 占用空间 最小值~最大值【signed】 最小值~最大值【unsigned】
tinyint 1 -128~127 0~255
smallint 2 32768~32767 0~65535
mediumint 3 -8388608~8388607 0~16777215
int 4 -2147483648~2147483647 0~4294967295
bigint 8 9223372036854775808~9223372036854775807 0~18446744073709551615

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

创建测试表,插入一条数据:

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)

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

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


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL报错:ERROR 1690 (22003): BIGINT UNSIGNED value is out of range
喜欢 (0)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址