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
What MySQL returns if I insert invalid value into ENUM?
If strict SQL mode is disabled and we insert invalid value (which is not in the list of permitted enumeration values) into ENUM then MySQL will insert an empty string instead of throwing an error. But if strict SQL mode is enabled then MySQL throws an error on inserting invalid value.
Example
After disabling the strict SQL mode, we insert the invalid string into ENUM as follows −
mysql> Insert into result(id, name, grade) values(100, 'Gaurav','abcd'); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> Select * from result; +-----+--------+-------+ | Id | Name | Grade | +-----+--------+-------+ | 100 | Gaurav | | +-----+--------+-------+ 1 row in set (0.00 sec)
From the query above, we can see that MySQL has inserted an empty string at the place of invalid string and no error has been thrown.
Now, with the help of the following query we can get the index number, which is 0 and it confirms that the string inserted by MySQL is an empty string because the index value of empty string is 0 always.
mysql> Select Grade + 0 from result; +-----------+ | Grade + 0 | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec)
But, after enabling the SQL strict mode, as done in query below, MySQL throws an error on inserting the invalid string into ENUM.
mysql> Set SQL_MODE = 'Traditional'; Query OK, 0 rows affected (0.00 sec) mysql> Insert into result(id, name, grade) values(101, 'Saurabh','abcd'); ERROR 1265 (01000): Data truncated for column 'Grade' at row 1
498 Views
