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

Vrundesha Joshi

Published on 20-Feb-2018 06:34:52

• Related Questions & Answers
• 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 ASCII() function returns if I will provide NULL to it?
• 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 passing an invalid string as an argument to STR_TO_DATE() function?
• How MySQL behaves if I use INTERVAL keyword with an invalid date?
• What happens if MySQL query returns no rows?
• What MySQL returns if we convert an empty hexadecimal value to a number?
• What MySQL returns on running the INSERT INTO statement without giving the column name and values both?
• What MySQL returns if I use enclosed set of unit values with INTERVAL keyword?
• What MySQL returns if we provide value larger than 255 as argument to MySQL CHAR() function?
• How can I remove a value from an enum in MySQL?
• 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?