mysql> create table a1(id int not null auto_increment primary key,name varchar(20), total bigint unsigned);
Query OK, 0 rows affected (0.12 sec)
mysql> insert into a1 values(null,'a',15);
Query OK, 1 row affected (0.06 sec)
mysql> select * from a1;
+----+------+-------+
| id | name | total |
+----+------+-------+
| 1 | a | 15 |
+----+------+-------+
1 row in set (0.00 sec)
mysql> select 14-total from a1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(14 - `hhm`.`a1`.`total`)
[4 Jan 2020 10:17] Roy Lyseng
Posted by developer:
This is not a bug.
The CRC32 function produces an UNSIGNED value, and the UNSIGNED property is propagated to the addition operator.
Workaround is to convert the CRC32 result to a signed integer:
select ((floor(-(401))) * CAST(CRC32(0) AS SIGNED));
MySQL官方文档找到了：
Subtraction between integer values, where one is of type UNSIGNED , produces an unsigned result by default. If the result would otherwise have been negative, an error results:
mysql> alter table a1 add sum bigint;
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from a1;
+----+------+-------+------+
| id | name | total | sum |
+----+------+-------+------+
| 1 | a | 15 | NULL |
+----+------+-------+------+
1 row in set (0.01 sec)
mysql> update a1 set sum=15;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select 14-sum from a1;
+--------+
| 14-sum |
+--------+
| -1 |
+--------+
1 row in set (0.01 sec)