来看表的创建:
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)
酱紫。