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