当 MySQL 在数值列中存储一个超出该列数据类型允许范围的值时，结果取决于当时生效的 SQL 模式：When MySQL stores a value in a numeric column that is outside the permissible range of the column data type, the result depends on the SQL mode in effect at the time:

假设有一个表 t1 定义如下：Suppose that a table t1 has this definition:

CREATE TABLE t1 (i1 TINYINT, i2 TINYINT UNSIGNED);

启用严格 SQL 模式时，会发生超出范围的错误：With strict SQL mode enabled, an out of range error occurs:

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 模式时，会发生带警告的截断：With strict SQL mode not enabled, clipping with warnings occurs:

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 语句。在严格模式下，这些语句会失败，并且根据表是否为事务表及其他因素，部分或所有值不会被插入或更改。详情请参阅 第 7.1.11 节，“服务器 SQL 模式”。When strict SQL mode is not enabled, column-assignment conversions that occur due to clipping are reported as warnings for ALTER TABLE, LOAD DATA, UPDATE, and multiple-row INSERT statements. In strict mode, these statements fail, and some or all the values are not inserted or changed, depending on whether the table is a transactional table and other factors. For details, see Section 7.1.11, “Server SQL Modes”.

数值表达式评估时溢出会导致错误。例如，最大符号 BIGINT 值为 9223372036854775807，因此以下表达式会产生错误：Overflow during numeric expression evaluation results in an error. For example, the largest signed BIGINT value is 9223372036854775807, so the following expression produces an error:

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

为了使操作在此情况下成功，请将该值转换为无符号；To enable the operation to succeed in this case, convert the value to unsigned;

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

是否发生溢出取决于操作数的范围，因此处理前面的表达式的另一种方法是使用精确值算术，因为DECIMAL值的范围比整数更大：Whether overflow occurs depends on the range of the operands, so another way to handle the preceding expression is to use exact-value arithmetic because DECIMAL values have a larger range than integers:

mysql> SELECT 9223372036854775807.0 + 1;
+---------------------------+
| 9223372036854775807.0 + 1 |
+---------------------------+
|     9223372036854775808.0 |
+---------------------------+

整数值之间的减法，如果其中一个是 UNSIGNED 类型，默认会产生无符号结果。如果结果本来会是负数，则会产生错误：Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, an error results:

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 模式，结果将为负数：If the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative:

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 模式，则会发生错误，并且列保持不变。If the result of such an operation is used to update an UNSIGNED integer column, the result is clipped to the maximum value for the column type, or clipped to 0 if NO_UNSIGNED_SUBTRACTION is enabled. If strict SQL mode is enabled, an error occurs and the column remains unchanged.