Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How does MySQL handle out of range numeric values?
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)
507 Views
