UNSIGNED属性就是将数字类型无符号化, 与C、C++这些程序语言中的unsigned含义相同。例如, INT的类型范围是-2147483648 ~ 2147483647, INT UNSIGNED 的范围类型就是0 ~ 4294967295。
看起来这是一个不错的属性选项,特别是对于主键是自增长的类型,因为一般来说,用户都希望主键是非负数。然而在实际使用中, UNSIGNED可能会带来一些负面的影响, 示例如下:
故障信息:
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`db`.`t1`.`a` - `db`.`t1`.`b`)'
主要是增加了 NO_UNSIGNED_SUBTRACTION 严格模式
mysql> create table t1(a int unsigned,b int unsigned);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 select 1,2;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.00 sec)
mysql> select a-b from t1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`db`.`t1`.`a` - `db`.`t1`.`b`)'
# 主要是增加了 NO_UNSIGNED_SUBTRACTION 严格模式
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
mysql> select a-b from t1;
+------+
| a-b |
+------+
| -1 |
+------+
1 row in set (0.00 sec)