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

In fact, elements listed in the ENUM column specification are assigned index numbers starting from 1. The term "index" here refers to the position in the list of enumeration values, they have nothing to do with the table index. With the following example, we can show that each MySQL enumeration has an index value -

By inserting the index number instead of the enumeration value

We can show that by inserting their index number instead of writing Enter a value to insert a value in the ENUM column. For example, in the table below, we have two ENUM values ​​'pass' and 'fail'. Since 'pass' is written first in the enumeration list, it gets the index number '1', and the index number of 'fail' is '2'. Now, we can also insert the value by inserting the index number as shown below -

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 above query, we have used the index number 1 and 2 to represent the enumeration values ​​pass and fail respectively.

By inserting an empty string instead of an enumeration value

The index value of an empty string is 0. The SQL mode cannot be TRADITIONAL, STRICT_TRANS_TABLES or STRICT_ALL_TABLES before inserting the empty string. We can understand it with the following example −

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 above query will insert an empty string at the position of the enum value. MySQL inserts an 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 inspect the table, we can see that there is an empty string in the 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 the enumeration value

We can insert NULL in the place of the enumeration value because we do not specify NOT NULL for the ENUM column. The index value of NULL is NULL. For example, with the help of the following query, we insert NULL in the enum 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 above query inserted NULL value at the position of the enumeration value, you can check it using the following help query -

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

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

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

The above is the detailed content of How to show that each MySQL enumeration has an index value?. For more information, please follow other related articles on the PHP Chinese website!