How to handle MySQL numeric type overflow

Now, let me ask you a question. What happens when a column is set to int(0) in MySQL?

To demonstrate this problem, we first 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)

Right, it seems like nothing will happen, it’s right if there is no problem, I’m just afraid if there is any problem…haha

In this chapter we will talk about integer overflow problems.

MySQL numeric type overflow processing

When MySQL stores a value in a numeric column that is outside the range allowed by the column data type, the result depends on the SQL mode in effect at the time.

• If strict SQL mode is enabled, MySQL rejects out-of-range values ​​with an error 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 it.
  • When an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the range of the column data type.
  • 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 values ​​representing the corresponding endpoints of the range.

This should be easy to understand, right?

Let's take an example, assuming that the structure of table t1 is as follows

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

If strict SQL mode is enabled, an error will occur if the range is exceeded.

mysql> SET sql_mode = 'TRADITIONAL'; -- First set strict mode 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 they are 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, statements such as ALTER TABLE, LOAD DATA INFILE, UPDATE, and multi-row INSERT will cause column assignment conversions due to pruning and raise a warning.

If strict mode is enabled, these statements simply fail and some or all values ​​are not inserted or changed, depending on whether the table is transactional and other factors.

Overflow during the evaluation of a numeric expression results in an error. For example, because the largest signed BIGINT value is 9223372036854775807, the following expression results in 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 the operands, so another way to process the previous expression is to use exact-value arithmetic, since the range of DECIMAL values ​​is greater than that of integers.

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

Subtraction between integer values, if one of the types is UNSIGNED, produces an unsigned result 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 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 |
+-------------------------+

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 to 0 if NO_UNSIGNED_SUBTRACTION is enabled. However, if strict SQL mode is enabled, an error occurs and the column remains unchanged.

postscript

Everything is a routine, routine....basically related to SQL mode...

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.