mysql之ENUM枚举类型

peibaishi

本节主要内容：
MySQL数据类型之枚举类型ENUM
MySQL数据库提供针对字符串存储的一种特殊数据类型：枚举类型ENUM，这种数据类型可以给予我们更多提高性能、降低存储容量和降低程序代码理解的技巧，前面介绍了首先介绍了四种数据类型的特性总结，其后又分别介绍了布尔类型BOOL或称布尔类型BOOLEAN，以及后续会再单独介绍集合类型SET。
本文详细介绍集合类型enum测试过程与总结，加深对mysql数据库集合类型enum的理解记忆。
n  枚举类型ENUM
a).数据库表mysqlops_enum结构
执行数据库表mysqlops_enum创建的SQL语句：
 
复制代码代码示例:root@localhost : test 11:22:29> CREATE TABLE Mysqlops_enum(ID INT NOT NULL AUTO_INCREMENT,
    ->  Job_type    ENUM('DBA','SA','Coding Engineer','JavaScript','NA','QA','','other') NOT NULL,
    ->  Work_City ENUM('shanghai','beijing','hangzhou','shenzhen','guangzhou','other') NOT NULL DEFAULT 'shanghai',
    ->  PRIMARY KEY(ID)
    ->  )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
 
Query OK, 0 rows affected (0.00 sec)
 执行查询数据库表mysqlops_enum结构的SQL语句：
 
复制代码代码示例:root@localhost : test 11:23:31> SHOW CREATE TABLE Mysqlops_enum\G
 
*************************** 1. row ***************************
 
       Table: Mysqlops_enum
 
Create Table: CREATE TABLE `Mysqlops_enum` (
 
  `ID` int(11) NOT NULL AUTO_INCREMENT,
 
  `Job_type` enum('DBA','SA','Coding Engineer','JavaScript','NA','QA','','other') NOT NULL,
 
  `Work_City` enum('shanghai','beijing','hangzhou','shenzhen','guangzhou','other') NOT NULL DEFAULT 'shanghai',
 
  PRIMARY KEY (`ID`)
 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
 
1 row in set (0.00 sec)
 小结：
     为方便测试枚举类型，如何处理字段定义的默认值、是否允许为NULL和空值的情况，我们定义了2个枚举类型的字段名，经过对比创建与查询数据库中表的结构信息，没有发现MySQL数据库默认修改任何信息。
b). 写入不同类型的测试数据
写入一条符合枚举类型定义的记录值：
 
复制代码代码示例:root@localhost : test 11:22:35> INSERT INTO Mysqlops_enum(ID,Job_type,Work_City) VALUES(1,'QA','shanghai'); 
Query OK, 1 row affected (0.00 sec)测试第二个枚举类型字Work_City是否允许为空记录值：
 
复制代码代码示例:root@localhost : test 11:22:42> INSERT INTO Mysqlops_enum(ID,Job_type,Work_City) VALUES(2,'NA',''); 
Query OK, 1 row affected, 1 warning (0.00 sec)
 
root@localhost : test 11:22:48> SHOW WARNINGS;
 
+---------+------+------------------------------------------------+
 
| Level   | Code | Message              |
 
+---------+------+------------------------------------------------+
 
| Warning | 1265 | Data truncated for column 'Work_City' at row 1 |
 
+---------+------+------------------------------------------------+
 
1 row in set (0.00 sec)
 测试第二个枚举类型字段Work_City是否允许存储NULL值：
 
复制代码代码示例:root@localhost : test 11:22:53> INSERT INTO Mysqlops_enum(ID,Job_type,Work_City) VALUES(3,'Other',NULL); 
ERROR 1048 (23000): Column 'Work_City' cannot be null测试第一个枚举类型字段Job_type是否可以存储空白值：
 
复制代码代码示例:root@localhost : test 11:22:59> INSERT INTO Mysqlops_enum(ID,Job_type,Work_City) VALUES(4,'','hangzhou'); 
Query OK, 1 row affected (0.00 sec)测试第二个枚举类型字段Job_City如何处理没有在定义中描述的值域第一个枚举类型字段Work_Type的默认值没指定情况下，会默认填写那个值：
 
复制代码代码示例:root@localhost : test 11:23:06> INSERT INTO Mysqlops_enum(ID,Work_City) VALUES(5,'ningbo'); 
Query OK, 1 row affected, 1 warning (0.00 sec)
 
root@localhost : test 11:23:13> SHOW WARNINGS;
 
+---------+------+------------------------------------------------+
 
| Level   | Code | Message              |
 
+---------+------+------------------------------------------------+
 
| Warning | 1265 | Data truncated for column 'Work_City' at row 1 |
 
+---------+------+------------------------------------------------+
 
1 row in set (0.00 sec)
 测试第二个枚举类型字段未插入数据的情况下，是否能使用上字段定义中指定的默认值：
 
复制代码代码示例:root@localhost : test 11:23:17> INSERT INTO Mysqlops_enum(ID,Job_type) VALUES(6,'DBA'); 
Query OK, 1 row affected (0.00 sec)小结：
若是枚举类型字段定义为非NULL，默认的SQL_MODE模式下，插入NULL值则会报错，导致SQL语句执行失败；若是没有明确申明非NULL，则允许插入NULL值；
若是枚举类型字段的枚举值中，没有指定空格字符值，插入空格字符数据时，会出现数据截断的警告信息，但是SQL语句依然执行成功；
若是向枚举类型字段插入未在定义列表中出现的数据，则会出现数据截断的警告信息，但是SQL语句依然执行成功；
若是枚举类型定义为非NULL，向数据库表中插入新数据，但未指定枚举类型字段的值，则使用枚举类型字段定义申明的默认值，若是无显示申明则是采用枚举类型字段的枚举列表中第一个值作为默认值；
c). 查询数据库表mysqlops_enum的数据
查询枚举类型数据库表Mysqlops_enum所有的数据（注释：两个枚举类型字段都是非NULL，所以截断后的值为空格）：
 
复制代码代码示例:root@localhost : test 11:23:24> SELECT * FROM Mysqlops_enum;
 
+----+----------+-----------+
 
|> 
+----+----------+-----------+
 
|  1 | QA       | shanghai  |
 
|  2 | NA       |           |
 
|  4 |          | hangzhou  |
 
|  5 | DBA      |           |
 
|  6 | DBA      | shanghai  |
 
+----+----------+-----------+
 
5 rows in set (0.00 sec)
 验证枚举类型字段存储的是数据对应的序列编号，而不是真实的字符串值，且序列号是与枚举类型字段值域列表中的顺序有关：
 
复制代码代码示例:root@localhost : test 11:23:57> SELECT * FROM Mysqlops_enum WHERE Work_City=0;
 
+----+----------+-----------+
 
|> 
+----+----------+-----------+
 
|  2 | NA       |           |
 
|  5 | DBA      |           |
 
+----+----------+-----------+
 
2 rows in set (0.00 sec)
 
root@localhost : test 01:22:08> SELECT * FROM Mysqlops_enum WHERE Work_City=1;
 
+----+----------+-----------+
 
|> 
+----+----------+-----------+
 
|  1 | QA       | shanghai  |
 
|  6 | DBA      | shanghai  |
 
+----+----------+-----------+
 
2 rows in set (0.00 sec)
 
root@localhost : test 02:40:31> SELECT * FROM Mysqlops_enum WHERE Work_City=2;
 
Empty set (0.00 sec)
 
root@localhost : test 02:40:33> SELECT * FROM Mysqlops_enum WHERE Work_City=3;
 
+----+----------+-----------+
 
|> 
+----+----------+-----------+
 
|  4 |          | hangzhou  |
 
+----+----------+-----------+
 
1 row in set (0.01 sec)
 
root@localhost : test 02:40:34> SELECT * FROM Mysqlops_enum WHERE Work_City=4;
 
Empty set (0.00 sec)
 
root@localhost : test 02:40:36> SELECT * FROM Mysqlops_enum WHERE Work_City=5;
 
Empty set (0.00 sec)
 
root@localhost : test 02:40:37> SELECT * FROM Mysqlops_enum WHERE Work_City=6;
 
Empty set (0.00 sec)
 
root@localhost : test 04:29:07> SELECT * FROM Mysqlops_enum WHERE Job_type=7;
 
+----+----------+-----------+
 
|> 
+----+----------+-----------+
 
|  4 |          | hangzhou  |
 
+----+----------+-----------+
 
1 row in set (0.00 sec)
 枚举类型数据小结：
枚举类型字段定义必须为确定的值，不能为变量、函数、表达式等；
若是向枚举类型字段插入NULL值，且枚举类型的字段定义为非NULL，SQL语句会执行失败；
若是向枚举类型字段插入，其枚举列表值域中不存在的值，则会发生字段值的截断，并且用空格字符串值替代，其存储的序列编号为0；
若是枚举类型字段定义的枚举列表值域中存在空字符串值，该枚举类型字段发生字段值截断，则是会用空格值替代，但是其存储的序列号与枚举列表中存储的序列号不同，也即参考事例所示：
 
复制代码代码示例:root@localhost : test 04:37:32> SELECT * FROM Mysqlops_enum WHERE Job_type=0;
 
+----+----------+-----------+
 
|> 
+----+----------+-----------+
 
|  8 |          | hangzhou  |
 
+----+----------+-----------+
 
1 row in set (0.00 sec)
 
root@localhost : test 04:37:35> SELECT * FROM Mysqlops_enum WHERE Job_type='';
 
+----+----------+-----------+
 
|> 
+----+----------+-----------+
 
|  4 |          | hangzhou  |
 
|  8 |          | hangzhou  |
 
+----+----------+-----------+
 
2 rows in set (0.00 sec)
 若是枚举类型字段定义为非NULL，且没有为该字段指定值的方式插入数据行，则把字段定义显式申明的默认值作为字段默认值，没有显式申明则把枚举列值域表中第一个值作为默认值；
若是枚举类型字段允许插入NULL值，则NULL值对应存储的序列号为NULL；