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)

 

     酱紫。