MySQL 5.7-11.1.7 Out-of-Range and Overflow Handling(超出范围和溢出处理)

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:

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

• If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard.

• 如果启用了strict SQL模式，MySQL会根据SQL标准拒绝超出范围的值并返回错误，并且插入失败。

• If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the column data type range and stores the resulting value instead.

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

When an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column data type range.

当一个范围外的值被分配给一个整数列时，MySQL存储表示列数据类型范围的相应端点的值。

When a floating-point or fixed-point column is assigned a value that exceeds the range implied by the specified (or default) precision and scale, MySQL stores the value representing the corresponding endpoint of that range.

当浮点或定点列被赋值超过指定的(或默认的)精度和比例范围时，MySQL存储表示该范围的相应端点的值。

 

With strict SQL mode enabled, an out of range error occurs:

启用严格SQL模式时，会出现超出范围的错误:

With strict SQL mode not enabled, clipping with warnings occurs:

如果没有启用严格的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 5.1.10, “Server SQL Modes”.

当未启用严格SQL模式时，由于剪切而发生的列赋值转换将作为对ALTER TABLE、LOAD DATA、UPDATE和多行INSERT语句的警告报告。在严格模式下，这些语句会失败，部分或所有值不会插入或更改，这取决于表是否是事务表和其他因素。

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:

数值表达式计算期间的溢出会导致错误。例如，最大的带符号BIGINT值是9223372036854775807，因此下面的表达式将产生错误:

To enable the operation to succeed in this case, convert the value to unsigned; 

 在这种情况下，要使操作成功，请将值转换为unsigned;

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: 

是否发生溢出取决于操作数的范围，因此处理前面表达式的另一种方法是使用精确值算术，因为DECIMAL值的范围比整数大:

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: 

整数值之间的减法，其中一个是UNSIGNED类型，默认情况下会产生一个UNSIGNED结果。如果结果是负数，则会出现一个错误:

If the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative: 

如果启用NO_UNSIGNED_SUBTRACTION 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. 

 如果这样一个操作的结果被用于更新一个UNSIGNED整数列，结果将被裁剪为该列类型的最大值，如果启用no_unsigned_subtract，结果将被裁剪为0。如果启用了strict SQL模式，则会发生错误，列保持不变。