MySQL 数值类型溢出处理

推荐阅读：吊打面试官！值类MySQL灵魂100问，型溢你能答出多少？出处

来，考考大家一个问题，值类在 MySQL 中当某一列设置为 int(0) 时会发生什么 ？型溢

为了演示这个问题，我们先要创建一个表

DROP TABLE IF EXISTS `na`;CREATE TABLE `na`( n1 INT(0) NOT NULL DEFAULT 0,出处 n2 INT(11) NOT NULL DEFAULT 0);

然后我们使用下面的语句往 na 表中插入一些数据

mysql> INSERT INTO `na` VALUES(520,520),(5201314,5201314);Query OK,2 rows affected (0.02 sec)Records:2Duplicates:0Warnings:0

最后我们读取出来看看

mysql> SELECT * FROM na;+---------+---------+| n1 | n2 |+---------+---------+|520|520||5201314|5201314|+---------+---------+2 rows inset(0.00 sec)

对的，好像什么都不会发生，值类没什么问题才是型溢对的，我就怕有什么问题…哈哈

我们这一章节来讲讲整型溢出问题。出处

MySQL 数值类型溢出处理

当 MySQL 在某个数值列上存储超出列数据类型允许范围的值类值时，结果取决于当时生效的型溢 SQL 模式

如果启用了严格的 SQL 模式，则 MySQL 会根据 SQL 标准拒绝带有错误的出处超出范围的值，并且插入失败

如果没有启用任何限制模式，值类那么 MySQL 会将值裁剪到列数据类型范围的型溢上下限值并存储

当超出范围的值分配给整数列时，MySQL 会存储表示列数据类型范围的出处相应端点的值

当为浮点或定点列分配的服务器托管值超出指定（或默认）精度和比例所隐含的范围时，MySQL 会存储表示该范围的相应端点的值

这个，应该很好理解吧？

我们举一个例子，假设 t1 表的结构如下

CREATE TABLE t1 ( i1 TINYINT, i2 TINYINT UNSIGNED);

如果启用了严格的 SQL 模式，超出范围会发生一个错误

mysql> SET sql_mode =TRADITIONAL;--首先设置严格模式mysql> INSERT INTO t1 (i1, i2) VALUES(256,256);ERROR 1264(22003):Out of range value for column i1 at row 1mysql> SELECT * FROM t1;Emptyset(0.00 sec)

当严格模式被禁用，值可以插入，但会被裁剪，并且引发一个警告

mysql> SET sql_mode =;--禁用所有模式mysql> INSERT INTO t1 (i1, i2) VALUES(256,256);mysql> SHOW WARNINGS;+---------+------+---------------------------------------------+|Level|Code|Message|+---------+------+---------------------------------------------+|Warning|1264|Out of range value for column i1 at row 1||Warning|1264|Out of range value for column i2 at row 1|+---------+------+---------------------------------------------+mysql> SELECT * FROM t1;+------+------+| i1 | i2 |+------+------+|127|255|+------+------+

如果未启用严格 SQL 模式，对于 ALTER TABLE，LOAD DATA INFILE，UPDATE 和多行 INSERT等语句会由于裁剪而发生的列分配转换并且引发一个警告。

而如果启用了严格模式，这些语句会直接失败，并且未插入或更改部分或全部值，具体取决于表是否为事务表和其他因素。

数值表达式求值过程中的溢出会导致错误，例如，因为最大的有符号 BIGINT 值是站群服务器 9223372036854775807，因此以下表达式会产生错误

mysql> SELECT 9223372036854775807+1;ERROR 1690(22003): BIGINT value isout of range in(9223372036854775807 + 1)

为了在这种情况下使操作成功，需要将值转换为 unsigned

mysql> SELECT CAST(9223372036854775807 AS UNSIGNED)+1;+-------------------------------------------+| CAST(9223372036854775807 AS UNSIGNED)+1|+-------------------------------------------+|9223372036854775808|+-------------------------------------------+

从另一方面说，是否发生溢出取决于操作数的范围，因此处理前一个表达式的另一种方法是使用精确值算术，因为 DECIMAL 值的范围大于整数

mysql> SELECT 9223372036854775807.0+1;+---------------------------+|9223372036854775807.0+1|+---------------------------+|9223372036854775808.0|+---------------------------+

整数数值之间的减去，如果其中一个类型为 UNSIGNED ，默认情况下会生成无符号结果。如果为负，则会引发错误

mysql> SET sql_mode =;Query OK,0 rows affected (0.00 sec)mysql> SELECT CAST(0 AS UNSIGNED)-1;ERROR 1690(22003): BIGINT UNSIGNED value isout of range in(cast(0 as unsigned) - 1)

这种情况下，如果启用了 NO_UNSIGNED_SUBTRACTION SQL 模式，则结果为负

mysql> SET sql_mode =NO_UNSIGNED_SUBTRACTION;mysql> SELECT CAST(0 AS UNSIGNED)-1;+-------------------------+| CAST(0 AS UNSIGNED)-1|+-------------------------+|-1|+-------------------------+

如果此类操作的结果用于更新 UNSIGNED 整数列，则结果将裁剪为列类型的最大值，如果启用了 NO_UNSIGNED_SUBTRACTION 则裁剪为 0。但如果启用了严格的 SQL 模式，则会发生错误并且列保持不变。

后记

一切都是套路，亿华云计算套路….基本都和 SQL 模式有关…