超出范围和溢出处理
当MySQL在数字列中存储的值超出列数据类型的允许范围时,结果取决于当时有效的SQL模式:
- 如果启用了严格的SQL模式,则根据SQL标准,MySQL会拒绝超出范围的值并产生错误,并且插入将失败。
如果未启用任何限制模式,MySQL会将值裁剪到列数据类型范围的适当端点,并存储结果值。
当将超出范围的值分配给整数列时,MySQL将存储代表该列数据类型范围的相应端点的值。
当给浮点或定点列分配的值超出指定(或默认)精度和小数位数所隐含的范围时,MySQL将存储代表该范围的相应端点的值。
假设一个表t1
具有以下定义:
CREATE TABLE t1 (i1 TINYINT, i2 TINYINTUNSIGNED );
启用严格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
语句。在严格模式下,这些语句将失败,并且某些或所有值都不会插入或更改,这取决于该表是否为事务表和其他因素。有关详细信息,请参见“服务器SQL模式”。
数值表达式求值期间的溢出会导致错误。例如,最大的有符号BIGINT
值是9223372036854775807,因此以下表达式会产生错误:
mysql>SELECT 9223372036854775807 + 1; ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'
要在这种情况下使操作成功,请将值转换为unsigned;
mysql>SELECT CAST(9223372036854775807AS 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(0AS 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(0AS UNSIGNED ) - 1; +------------------------- + | CAST(0 AS UNSIGNED) - 1 | +------------------------- + | -1 | +------------------------- +
如果将此类操作的结果用于更新UNSIGNED
整数列,则将结果裁剪为该列类型的最大值,如果NO_UNSIGNED_SUBTRACTION
启用,则裁剪为0 。如果启用了严格的SQL模式,则会发生错误,并且该列将保持不变。