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!