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: 2 Duplicates: 0 Warnings: 0最后我们读取出来看看
mysql> SELECT * FROM na;+---------+---------+| n1 | n2 |+---------+---------+| 520 | 520 || 5201314 | 5201314 |+---------+---------+2 rows in set (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;Empty set (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 is out 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 is out of range in '(cast(0 as unsigned) - 1)'