11.1.7 超出范围和溢出处理

当 MySQL 在数值列中存储的值超出列数据类型的允许范围时,结果取决于当时有效的 SQL 模式:

  • 如果启用了严格的 SQL 模式,则按照 SQL 标准,MySQL 会拒绝超出范围的值并产生错误,并且插入将失败。

  • 如果未启用任何限制模式,MySQL 会将值裁剪到列数据类型范围的适当端点,并存储结果值。

当将超出范围的值分配给整数列时,MySQL 将存储代 table 该列数据类型范围的相应端点的值。

当给浮点或定点列分配的值超过了指定(或默认)精度和小数位数所隐含的范围时,MySQL 将存储代 table 该范围的相应端点的值。

假设 tablet1具有以下定义:

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 1
mysql> SELECT * FROM t1;
Empty set (0.00 sec)

如果未启用严格 SQL 模式,则会发生带有警告的剪切:

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 TABLELOAD DATAUPDATE和多行INSERT语句的警告。在严格模式下,这些语句将失败,并且某些或所有值都不会插入或更改,这取决于该 table 是否为事务 table 和其他因素。有关详细信息,请参见第 5.1.10 节“服务器 SQL 模式”

数值 table 达式求值期间的溢出会导致错误。例如,最大的有符号BIGINT值是 9223372036854775807,因此以下 table 达式会产生错误:

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

要在这种情况下使操作成功,请将值转换为无符号;否则,将值转换为无符号。

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

是否发生溢出取决于操作数的范围,因此处理前一个 table 达式的另一种方法是使用精确值算法,因为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)'

如果启用了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 模式,则会发生错误并且该列将保持不变。