How does MySQL handle out of range numeric values?

MySQLMySQLi Database

Handling of MySQL numeric value that is out of allowed range of column data type depends upon the SQL mode in following ways −

(A) Enabled SQL strict mode - When strict SQL mode is enabled, MySQL returns the error on entering the put-of-range value. In this case, the insertion of some or all the values got failed.

For example, we have created a table with two columns having TINYINT and UNSIGNED TINYINT as their data types on columns.

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 enabled the strict SQL mode

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

Now, if we will try to insert the out-of-range values into the columns, MySQL reflects the error and both the insertions failed which can be checked by the query below −

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) Disabled SQL strict mode -  When the restrictive SQL mode is disabled, the value is clipped by MySQL up to the suitable endpoint of that column data type and accumulate the resulting value. MySQL reflects warnings which are the result of column-assignment conversion that occurs due to clipping.

For example, if we will insert the values in the column after disabling the SQL strict mode, MySQL will reflect the warnings and stores the values after trimming them up to the suitable end-point. It can be understood with the queries below −

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)
raja
Published on 19-Feb-2018 13:39:53
Advertisements