How to show that each MySQL enumeration has an index value?

Actually, the elements listed in the ENUM column specifications are assigned index numbers which begin with 1. Here the term “index” is indicating towards the position within the list of enumeration values and they are not related to table indexes. With the help of following examples we can show that each MySQL enumeration has an index value −

By inserting index number instead of enumeration value

We can insert the values in ENUM column with the help of their index numbers rather than writing the values. For example, in the table below we have two ENUM values ‘pass’ and ‘fail’. As ‘pass’ is written first in the enumeration list hence got index number ‘1’ and it would be ‘2 for ‘fail’. Now we can also insert the values by inserting the index numbers as follows −

mysql> Insert into marks(id,name,result)values(103,'Daksh','1');
Query OK, 1 row affected (0.06 sec)

mysql> Insert into marks(id,name,result)values(104,'Shayra','2');
Query OK, 1 row affected (0.07 sec)

mysql> Select * from marks;
+-----+---------+--------+
| id  | Name    | Result |
+-----+---------+--------+
| 101 | Aarav   | Pass   |
| 102 | Yashraj | Fail   |
| 103 | Daksh   | Pass   |
| 104 | Shayra  | Fail   |
+-----+---------+--------+
4 rows in set (0.00 sec)

In the queries above, we have used index numbers 1 and 2 for enumeration values pass and fail respectively.

By inserting empty string instead of enumeration value

The index value of the empty string is 0. Before inserting the empty string, the SQL mode must not be TRADITIONAL, STRICT_TRANS_TABLES or STRICT_ALL_TABLES. With the help of the following example we can understand it −

mysql> SET SESSION sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> Insert into marks(id, name, result) values(105,'Yashraj','');
Query OK, 1 row affected, 1 warning (0.06 sec)

The query above will insert the empty string at the place of an enumeration value. MySQL inserts the empty string with the following warning.

mysql> Show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column 'Result' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

Now when we check table, we can see an empty string in ENUM field.

mysql> Select * from marks;
+-----+---------+--------+
| id  | Name    | Result |
+-----+---------+--------+
| 101 | Aarav   | Pass   |
| 102 | Yashraj | Fail   |
| 103 | Daksh   | Pass   |
| 104 | Shayra  | Fail   |
| 105 | Yash    |        |
+-----+---------+--------+
5 rows in set (0.00 sec)

mysql> Select result+0 As result_index from marks;
+--------------+
| result_index |
+--------------+
| 1            |
| 2            |
| 1            |
| 2            |
| 0            |
+--------------+
5 rows in set (0.01 sec)
From the output of above query, it is clear that the index value of the empty string is 0.

By inserting NULL instead of enumeration value

We can insert NULL at the place of enumeration values because we Do not specify NOT NULL with ENUM column. The index value of NULL is NULL. For example, with the help of the following query, we insert NULL in enumeration column of table ‘result’ and can check its index value.

mysql> Create table result(Id INT PRIMARY KEY NOT NULL, Name Varchar(10), GradeENUM('POOR','GOOD'));
Query OK, 0 rows affected (0.25 sec)

mysql> Insert into result(id, name, grade) values(100, 'Rahul', NULL);
Query OK, 1 row affected (0.06 sec)

The query above inserts the value NULL at the place of enumeration value which can be checked with the help query below −

mysql> Select * from result;
+-----+-------+-------+
| Id  | Name  | Grade |
+-----+-------+-------+
| 100 | Rahul | NULL  |
+-----+-------+-------+
1 row in set (0.00 sec)

Now, with the help of next query, we can observe that the index value of NULL is NULL. 

mysql> Select Grade+0 As Grade_index from result;
+-------------+
| Grade_index |
+-------------+
| NULL        |
+-------------+
1 row in set (0.00 sec)