• 首页
  • css3教程
  • html5教程
  • jQuery手册
  • vue手册
  • php手册
  • MySQL手册
  • apache手册
  • redis手册
  • 超出范围和溢出处理

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

    • 如果启用了严格的SQL模式,则根据SQL标准,MySQL会拒绝超出范围的值并产生错误,并且插入将失败。
    • 如果未启用任何限制模式,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 TABLELOAD DATAUPDATE,和多行INSERT语句。在严格模式下,这些语句将失败,并且某些或所有值都不会插入或更改,这取决于该表是否为事务表和其他因素。有关详细信息,请参见“服务器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模式,则会发生错误,并且该列将保持不变。