Mysql的整形类型 signed 和 unsigned 属性采坑

出自:
<姜承尧的MySQL实战宝典 >,本人在原文又做了一些更改

MySQL 数据库支持 SQL 标准支持的整型类型：INT、SMALLINT。此外，MySQL 数据库也支持诸如 TINYINT、MEDIUMINT 和 BIGINT 整型类型（表 1 显示了各种整型所占用的存储空间及取值范围）：

各 INT 类型的取值范围
在整型类型中，有 signed 和 unsigned 属性，其表示的是整型的取值范围，默认为 signed。

如果不知道signed 和 unsigned是什么意思就看下面这个博客:
https://www.yuque.com/docs/share/4c0e9796-321c-4479-8ee4-e6ca3523fc65?# 《mysql中 signed 与 unsigned 详解》

在设计时，我不建议你刻意去用 unsigned 属性，因为在做一些数据分析时，SQL 可能返回的结果并不是想要得到的结果。
来看一个“销售表 sale”的例子，其表结构和数据如下。这里要特别注意，列 sale_count 用到的是 unsigned 属性（即设计时希望列存储的数值大于等于 0）：

步骤1,建表语句:

CREATE TABLE sale
(
sale_date DATE NOT NULL,
sale_count INT UNSIGNED DEFAULT NULL,
PRIMARY KEY (sale_date)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci;

步骤2.插入数据

INSERT INTO sale (sale_date, sale_count)
VALUES (‘2020-01-01’, ‘10000’),
(‘2020-02-01’, ‘8000’),
(‘2020-03-01’, ‘12000’),
(‘2020-04-01’, ‘9000’),
(‘2020-05-01’, ‘10000’),
(‘2020-06-01’, ‘18000’)
;

步骤3.

其中，sale_date 表示销售的日期，sale_count 表示每月的销售数量。现在有一个需求，老板想要统计每个月销售数量的变化，以此做商业决策。这条 SQL 语句需要应用到非等值连接，但也并不是太难写：

SELECT s1.sale_date,
s2.sale_count - s1.sale_count AS diff
FROM sale s1
LEFT JOIN
sale s2 ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date
ORDER BY sale_date;

然而，在执行的过程中，由于列 sale_count 用到了 unsigned 属性，会抛出这样的结果:
[22001][1690] Data truncation: BIGINT UNSIGNED value is out of range in ‘(test.s2.sale_count - test.s1.sale_count)’
可以看到，MySQL 提示用户计算的结果超出了范围。其实，这里 MySQL 要求 unsigned 数值相减之后依然为 unsigned，否则就会报错。

注意:这里 unsigned 数值张俊杰我本人理解是无符号的数字,也就是正数的,这只是我的个人理解,如果有问题了,请指点出来

步骤4.

为了避免这个错误，需要对数据库参数 sql_mode 设置为 NO_UNSIGNED_SUBTRACTION，允许相减的结果为 signed，这样才能得到最终想要的结果：

SET sql_mode=‘NO_UNSIGNED_SUBTRACTION’;

执行完了上面的设置之后,再次执行 步骤三的SQL:

SELECT s1.sale_date,
s2.sale_count - s1.sale_count AS diff
FROM sale s1
LEFT JOIN
sale s2 ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date
ORDER BY sale_date;

此时结果是:

diff	sale_date
NULL	2020-01-01
2000	2020-02-01
-4000	2020-03-01
3000	2020-04-01
-1000	2020-05-01
-8000	2020-06-01

此时不报错的原因是允许结果是signed数值,也就是有符号的数值,这样就不会报错了.