ERROR 1690 (22003): BIGINT UNSIGNED value is out of range问题------用if或者cast处理

     来看表的创建:

mysql> show create table x;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| x     | CREATE TABLE `x` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(10) unsigned NOT NULL DEFAULT '0',
  `b` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

      来看表的数据:

mysql> select * from x;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 1 | 2 |
|  2 | 5 | 3 |
+----+---+---+
2 rows in set (0.00 sec)

      求a-b的值(相减为负数后反转了):

mysql> select *, a - b from x;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`taoge`.`x`.`a` - `taoge`.`x`.`b`)'
mysql> 

      改为:

mysql> select *, if(a>b, a-b, -(b-a)) from x ;
+----+---+---+----------------------+
| id | a | b | if(a>b, a-b, -(b-a)) |
+----+---+---+----------------------+
|  1 | 1 | 2 |                   -1 |
|  2 | 5 | 3 |                    2 |
+----+---+---+----------------------+
2 rows in set (0.00 sec)

       或者:

mysql> select *, cast(a as signed) - cast(b as signed) from x;
+----+---+---+---------------------------------------+
| id | a | b | cast(a as signed) - cast(b as signed) |
+----+---+---+---------------------------------------+
|  1 | 1 | 2 |                                    -1 |
|  2 | 5 | 3 |                                     2 |
+----+---+---+---------------------------------------+
2 rows in set (0.00 sec)

 

     酱紫。

 

 

 

发布了2203 篇原创文章 · 获赞 4516 · 访问量 1960万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 技术工厂 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览