实际上,在ENUM列规范中列出的元素已分配了以1开头的索引号。此处,“索引”一词表示指向枚举值列表中的位置,并且与表索引无关。借助以下示例,我们可以显示每个MySQL枚举都有一个索引值-
通过插入索引号而不是枚举值
我们可以借助索引号将这些值插入ENUM列,而不用编写这些值。例如,在下表中,我们有两个ENUM值“通过”和“失败”。由于在枚举列表中首先写入了“ pass”,因此索引号为“ 1”,“ fail”则为“ 2”。现在我们还可以通过插入索引号来插入值,如下所示:mysql> Insert into marks(id,name,result)values(103,'Daksh','1');
mysql> Insert into marks(id,name,result)values(104,'Shayra','2');
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)
在上面的查询中,我们将索引号1和2分别用于枚举值pass和fail。
通过插入空字符串而不是枚举值
空字符串的索引值为0。在插入空字符串之前,SQL模式不得为TRADITIONAL,STRICT_TRANS_TABLES或STRICT_ALL_TABLES。借助以下示例,我们可以理解它-mysql> SET SESSION sql_mode = '';
mysql> Insert into marks(id, name, result) values(105,'Yashraj','');
上面的查询将在枚举值的位置插入空字符串。MySQL插入带有以下警告的空字符串。mysql> Show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column 'Result' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
现在,当我们检查表时,我们可以在ENUM字段中看到一个空字符串。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.
通过插入NULL而不是枚举值
我们可以在枚举值的位置插入NULL,因为我们不会在ENUM列中指定NOT NULL。NULL的索引值为NULL。例如,借助以下查询,我们在表“结果”的枚举列中插入NULL并可以检查其索引值。mysql> Create table result(Id INT PRIMARY KEY NOT NULL, Name Varchar(10), GradeENUM('POOR','GOOD'));
mysql> Insert into result(id, name, grade) values(100, 'Rahul', NULL);
上面的查询在枚举值的位置插入值NULL,可以通过下面的帮助查询进行检查-mysql> Select * from result;
+-----+-------+-------+
| Id | Name | Grade |
+-----+-------+-------+
| 100 | Rahul | NULL |
+-----+-------+-------+
1 row in set (0.00 sec)
现在,借助下一个查询,我们可以观察到NULL的索引值为NULL。mysql> Select Grade+0 As Grade_index from result;
+-------------+
| Grade_index |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)