How to Deal with MySQL Numeric Type Overflow

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

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

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 and see

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 am afraid that there will be any problems … haha

In this chapter, we will talk about integer spillover.

MySQL numeric type overflow handling

When MySQL stores a value on a numeric column that exceeds the allowable range of column data types, 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 clips the value to the upper and lower limits of the column data type range and stores the When an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column's data type range When a 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 easy to understand, right?

Let's take an example and assume that the structure of t1 table is as follows

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

If the strict SQL mode is enabled, 1 error will occur if it goes 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, the value can be inserted, but it will be cropped and 1 warning will be 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, a column allocation conversion due to cropping occurs for statements such as ALTER TABLE, LOAD ES50INFILE, UPDATE, and multi-row INSERT and raises a warning.

If Strict Mode is enabled, these statements fail directly and do not insert or change some or all of the values, depending on whether the table is a transactional table and other factors.

An overflow in the evaluation of a numeric expression can cause an error, for example, because the largest signed BIGINT value is 9223372036854775807, the following expression can produce an error

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, the value needs to be converted to unsigned

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

On the other hand, whether overflow occurs depends on the range of operands, so another way to deal with the first 1 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 |
+---------------------------+

Subtractions between integer values, if one of the types is UNSIGNED, produce unsigned results by default. If negative, an error is thrown

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 the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative

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

0

If the result of such an operation is used to update an UNSIGNED integer column, the result is cropped to the maximum value of the column type, or to 0 if NO_UNSIGNED_SUBTRACTION is enabled. However, if the strict SQL mode is enabled, an error occurs and the columns remain unchanged.

Postscript

1 cut are all routines, routines … basically related to SQL mode …

Summarize