官方文档地址: 11.1.7 Out-of-Range and Overflow Handling
超出范围
当 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 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 TABLE
,LOAD DATA
,UPDATE
和多行INSERT
语句的警告报告。在严格模式下,这些语句会失败,一些或所有的值都不会被插入或更改,这取决于该表是否是事务性表以及其他因素。详细信息请参见 5.1.11 服务器 SQL 模式。
溢出处理
数值表达式求值期间溢出将导致错误。例如,最大的带符号的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)'
如果启用了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 模式,则会出现错误,列保持不变。