Handling method of MySQL numeric type overflow

Time:2021-1-20

Let’s ask you a question. What happens when a column is set to int (0) in MySQL?

To illustrate this problem, we need to create a table first


DROP TABLE IF EXISTS `na`;
CREATE TABLE `na` (
n1 INT(0) NOT NULL DEFAULT '0',
n2 INT(11) NOT NULL DEFAULT '0'
);

Then we use the following statement to insert some data into the Na table


mysql> INSERT INTO `na` VALUES(520,520),(5201314,5201314);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

Finally, let’s read it out


mysql> SELECT * FROM na;
+---------+---------+
| n1 | n2 |
+---------+---------+
| 520 | 520 |
| 5201314 | 5201314 |
+---------+---------+
2 rows in set (0.00 sec)

Yes, it seems that nothing will happen. No problem is right. I’m afraid there will be any problem ha-ha

Let’s talk about integer overflow in this chapter.

MySQL numeric type overflow processing

When MySQL stores a value in a numerical column that is beyond the allowable range of the data type of the out column, the result depends on the SQL schema in effect at that time

  • If strict SQL mode is enabled, MySQL rejects out of range values with errors according to the SQL standard, and the insert fails
  • If no restriction mode is enabled, MySQL will crop the values to the upper and lower limits of the range of column data types and store them
    • 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
    • When the value assigned to a floating-point or fixed-point column exceeds the range implied by the specified (or default) precision and scale, MySQL stores the value representing the corresponding endpoint of the range

This should be very easy to understand, right?

Let’s take an example. Suppose the structure of T1 table is as follows


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

If strict SQL mode is enabled, an error occurs out of range

mysql> SET sql_ Mode ='traditional '; -- set strict mode first
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)

When strict mode is disabled, values can be inserted, but clipped, and a warning is raised

mysql> SET sql_ Mode = '; -- Disable all modes
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 |
+------+------+

If strict SQL mode is not enabled, for alter table, load data, infile, update and multi row insert statements, column allocation conversion will occur due to clipping and a warning will be raised.

If strict mode is enabled, these statements will fail directly, and some or all values will not be inserted or changed, depending on whether the table is a transaction table and other factors.

Overflow during the evaluation of numeric expressions can cause errors. For example, because the largest signed bigint value is 9223372036854775807, the following expressions can cause errors


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

In order for the operation to succeed in this case, you need to convert the value to unsigned


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

Overflow, on the other hand, depends on the range of operands, so another way to deal with the previous expression is to use exact value arithmetic, because the range of decimal values is larger than integers


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

Subtraction between integer values. If one of the types is unseigned, an unsigned result is generated by default. If negative, an error is raised


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)'

In this case, if no is enabled_ UNSIGNED_ The result is negative if you use the substitution SQL mode


mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| -1 |
+-------------------------+

If the result of such an operation is used to update an unsigned integer column, the result is clipped to the maximum value of the column type, if no is enabled_ UNSIGNED_ Substraction is trimmed to 0. However, if strict SQL mode is enabled, an error occurs and the columns remain unchanged.

Postscript

Everything is routine, routine It is basically related to SQL mode

summary

The above is the whole content of this article, I hope the content of this article has a certain reference learning value for your study or work, thank you for your support to developer.