- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
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
- Related Articles
- How can I insert default value in MySQL ENUM data type?
- Insert default into not null column if value is null in MySQL?
- What MySQL returns on passing an invalid string as an argument to STR_TO_DATE() function?
- How can I set 0 if a query returns a null value in MySQL?
- Insert NULL value into INT column in MySQL?
- What MySQL returns on running the INSERT INTO statement without giving the column name and values both?
- What MySQL ASCII() function returns if I will provide NULL to it?
- What MySQL returns if we convert an empty hexadecimal value to a number?
- How MySQL behaves if I use INTERVAL keyword with an invalid date?
- How can I remove a value from an enum in MySQL?
- How do I INSERT INTO from one MySQL table into another table and set the value of one column?
- Java application to insert null value into a MySQL database?
- What MySQL returns if we provide value larger than 255 as argument to MySQL CHAR() function?
- What MySQL returns if I write only one value in the enclosed set of unit values for compound INTERVAL unit?
- What happens if I will assign a value to a MySQL user variable using a statement that returns multiple rows?
