How does MySQL handle out-of-range values?

The processing of MySQL values ​​that exceed the allowed range of the column data type depends on the SQL mode, as follows-

(A) Enable SQL strict mode - When strict SQL mode is enabled, MySQL returns an error when entering a range value. In this case, the insertion of some or all values ​​fails.

For example, we create a table with two columns whose data types are TINYINT and UNSIGNED TINYINT.

mysql> Create table counting(Range1 Tinyint, Range2 Tinyint Unsigned);
Query OK, 0 rows affected (0.14 sec)

Now with the help of the following command, we have enabled strict SQL mode

mysql> Set SQL_MODE ='traditional';
Query OK, 0 rows affected (0.00 sec)

Now, if we try to insert an out-of-range value into the column, MySQL reflects an error and inserts twice All failed, you can check by the following query-

mysql> Insert into Counting(Range1, Range2) Values(256,256);
ERROR 1264 (22003): Out of range value for column 'Range1' at row 1

mysql> Select * from counting;
Empty set (0.00 sec)

(B) Disable SQL strict mode- When restrictive SQL mode is disabled, MySQL will The values ​​are clipped to the appropriate endpoint of the column's data type, and the resulting values ​​are accumulated. MySQL reflects warnings that are the result of column assignment conversions that occur due to pruning.

For example, if we insert values ​​into a column after disabling SQL strict mode, MySQL will reflect the warning and store them after trimming the values ​​to a suitable endpoint. It can be understood through the following query -

mysql> Set SQL_MODE = '';
Query OK, 0 rows affected (0.00 sec)

mysql> Insert Into Counting(Range1,Range2) values (256,256);
Query OK, 1 row affected, 2 warnings (0.02 sec)

mysql> Show Warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'Range1' at row 1 |
| Warning | 1264 | Out of range value for column 'Range2' at row 1 |
+---------+------+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql> Select * from Counting;
+--------+--------+
| Range1 | Range2 |
+--------+--------+
| 127    | 255    |
+--------+--------+
1 row in set (0.00 sec)

The above is the detailed content of How does MySQL handle out-of-range values?. For more information, please follow other related articles on the PHP Chinese website!