MySQL数据类型：ENUM、SET、BOOL/BOOLEAN、TINYINT特性介绍

Amygo

c)修改枚举类型字段定义的默认值，且新默认值不在枚举列表中

1. root@localhost : mysqlops 02:39:15> ALTER TABLE mysqlops_set_enum MODIFY Work_Option enum(‘DBA’,‘SA’,‘C++’,‘JavaScript’,‘NA’,‘QA’,‘Java’,‘other’,’’) NOT NULL DEFAULT ‘iphone’;
   
2. 
   
3. ERROR 1067 (42000): Invalid default value for ‘Work_Option’

复制代码

小结：
若是给枚举类型字段指定的默认值，没有在枚举类型值域列表中出现，则会出现SQL语法错误，导致SQL语句执行失败。

Amygo

d)修改枚举类型字段定义，尾部追加枚举元素

1. root@localhost : mysqlops 02:39:27> ALTER TABLE mysqlops_set_enum MODIFY Work_Option enum(‘DBA’,‘SA’,‘C++’,‘JavaScript’,‘NA’,‘QA’,‘Java’,‘PHP’,‘other’,’’,‘Python’) NOT NULL DEFAULT ‘DBA’;
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. Records: 0 Duplicates: 0 Warnings: 0

复制代码

小结：
为枚举类型字段的值域列表增加枚举元素，若是尾部追加的方式，则不需要表级锁，可以非常快就可以完成DDL变更。

Amygo

e)修改枚举类型字段定义，调整枚举元素的顺序

1. root@localhost : mysqlops 02:38:10> SELECT * FROM mysqlops_set_enum WHERE Work_Option=1 LIMIT 1;
   
2. 
   
3. ±---------±------------±------------------+
   
4. 
   
5. | ID | Work_Option | Work_City |
   
6. 
   
7. ±---------±------------±------------------+
   
8. 
   
9. | 12017252 | DBA | guangzhou,tianjin |
   
10. 
    
11. ±---------±------------±------------------+
    
12. 
    
13. 1 row in set (3.22 sec)
    
14. 
    
15. 
    
16. 
    
17. root@localhost : mysqlops 02:39:39> ALTER TABLE mysqlops_set_enum MODIFY Work_Option enum(‘JavaScript’,‘DBA’,‘SA’,‘C++’,‘NA’,‘QA’,‘Java’,‘PHP’,‘other’,’’,‘Python’) NOT NULL DEFAULT ‘DBA’;
    
18. 
    
19. Query OK, 20017251 rows affected (2 min 10.75 sec)
    
20. 
    
21. Records: 20017251 Duplicates: 0 Warnings: 0
    
22. 
    
23. 
    
24. 
    
25. root@localhost : mysqlops 02:42:01> SELECT * FROM mysqlops_set_enum WHERE Work_Option=1 LIMIT 1;
    
26. 
    
27. ±---------±------------±------------------+
    
28. 
    
29. | ID | Work_Option | Work_City |
    
30. 
    
31. ±---------±------------±------------------+
    
32. 
    
33. | 12017252 | DBA | guangzhou,tianjin |
    
34. 
    
35. ±---------±------------±------------------+
    
36. 
    
37. 1 row in set (3.22 sec)

复制代码

小结：
对枚举类型字段的值域列表元素顺序进行调整，会发现：
i.将需要表级锁和重见数据存储表的方式，完成枚举类型字段表的结构调整；
ii.枚举类型字段值域列表中受影响元素的存储顺序编号发生变化；
iii.数据库表枚举类型字段中存储的数据是枚举类型元素的编号，枚举类型元素顺序编号发生变化时，对应存储的数据序号也需要跟着发生变化；

Amygo

f)修改枚举类型字段定义，删除某个枚举元素

1. root@localhost : mysqlops 02:05:24> SELECT COUNT() FROM mysqlops_set_enum WHERE Work_Option=’’;
   
2. 
   
3. ±---------+
   
4. 
   
5. | COUNT() |
   
6. 
   
7. ±---------+
   
8. 
   
9. | 4017251 |
   
10. 
    
11. ±---------+
    
12. 
    
13. 1 row in set (5.00 sec)
    
14. 
    
15. 
    
16. 
    
17. root@localhost : mysqlops 02:05:40> SELECT * FROM mysqlops_set_enum limit 6000000,1;
    
18. 
    
19. ±--------±------------±----------+
    
20. 
    
21. | ID | Work_Option | Work_City |
    
22. 
    
23. ±--------±------------±----------+
    
24. 
    
25. | 6000001 | PHP | dalian |
    
26. 
    
27. ±--------±------------±----------+
    
28. 
    
29. 1 row in set (1.38 sec)
    
30. 
    
31. 
    
32. 
    
33. root@localhost : mysqlops 02:06:55> SELECT COUNT() FROM mysqlops_set_enum WHERE Work_Option=‘PHP’;
    
34. 
    
35. ±---------+
    
36. 
    
37. | COUNT() |
    
38. 
    
39. ±---------+
    
40. 
    
41. | 2000000 |
    
42. 
    
43. ±---------+
    
44. 
    
45. 1 row in set (5.11 sec)
    
46. 
    
47. 
    
48. 
    
49. root@localhost : mysqlops 02:45:40> ALTER TABLE mysqlops_set_enum MODIFY Work_Option enum(‘JavaScript’,‘DBA’,‘SA’,‘C++’,‘NA’,‘QA’,‘Java’,‘other’,’’,‘Python’) NOT NULL DEFAULT ‘DBA’;
    
50. 
    
51. Query OK, 20017251 rows affected, 65535 warnings (2 min 11.71 sec)
    
52. 
    
53. Records: 20017251 Duplicates: 0 Warnings: 2000000
    
54. 
    
55. 
    
56. 
    
57. root@localhost : mysqlops 02:54:01> SHOW WARNINGS;
    
58. 
    
59. ±--------±-----±-------------------------------------------------------+
    
60. 
    
61. | Level | Code | Message |
    
62. 
    
63. ±--------±-----±-------------------------------------------------------+
    
64. 
    
65. | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017252 |
    
66. 
    
67. | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017253 |
    
68. 
    
69. | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017254 |
    
70. 
    
71. | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017255 |
    
72. 
    
73. | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017256 |
    
74. 
    
75. | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017257 |
    
76. 
    
77. | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017258 |
    
78. 
    
79. | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017259 |
    
80. 
    
81. | Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017260 |
    
82. 
    
83. 
    
84. 
    
85. root@localhost : mysqlops 02:56:18> SELECT COUNT() FROM mysqlops_set_enum WHERE Work_Option=’’;
    
86. 
    
87. ±---------+
    
88. 
    
89. | COUNT() |
    
90. 
    
91. ±---------+
    
92. 
    
93. | 6017251 |
    
94. 
    
95. ±---------+
    
96. 
    
97. 1 row in set (4.68 sec)
    
98. 
    
99. 
    
100. 
     
101. root@localhost : mysqlops 02:56:48> SELECT * FROM mysqlops_set_enum WHERE ID=6000001;
     
102. 
     
103. ±--------±------------±----------+
     
104. 
     
105. | ID | Work_Option | Work_City |
     
106. 
     
107. ±--------±------------±----------+
     
108. 
     
109. | 6000001 | | dalian |
     
110. 
     
111. ±--------±------------±----------+
     
112. 
     
113. 1 row in set (0.00 sec)

复制代码

小结：
对于枚举类型字段中已存储某枚举元素的数据，再删除枚举类型ENUM字段值域列表中某个枚举值，则会出现：
I.存在多少条要删除的枚举值记录数，就会产生多少条警告信息（注：警告信息最大值65535条）；
II.被删除枚举值对应的字段的记录值，会发生截断，并且用空字符串值填充；

Amygo

g)上述DDL变更操作之后的表结构

1. <p>root@localhost : mysqlops 02:57:30> SHOW CREATE TABLE mysqlops_set_enum\G</p><p>*************************** 1. row ***************************</p><p>Table: mysqlops_set_enum</p><p>Create Table: CREATE TABLE mysqlops_set_enum (</p><p>ID int(11) NOT NULL AUTO_INCREMENT,</p><p>Work_Option enum(‘JavaScript’,‘DBA’,‘SA’,‘C++’,‘NA’,‘QA’,‘Java’,‘other’,’’,‘Python’) NOT NULL DEFAULT ‘DBA’,</p><p>Work_City set(‘shanghai’,‘beijing’,‘hangzhou’,‘shenzhen’,‘guangzhou’,‘xiamen’,‘tianjin’,‘qingdao’,‘dalian’,‘xian’,‘other’) NOT NULL DEFAULT ‘shanghai’,</p><p>PRIMARY KEY (ID)</p><p>) ENGINE=InnoDB AUTO_INCREMENT=20017252 DEFAULT CHARSET=utf8</p><p>1 row in set (0.00 sec)</p><p></p>

复制代码

Amygo

(四)枚举类型ENUM字段数据库索引创建与删除

a)枚举类型字段无创建索引条件的SQL语句执行计划
root@localhost : mysqlops 03:40:35> EXPLAIN SELECT * FROM mysqlops_set_enum WHERE Work_Option=4 LIMIT 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mysqlops_set_enum
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 20017710
Extra: Using where
1 row in set (0.00 sec)
小结：
MySQL数据库枚举类型字段没有显式创建索引信息时，即使符合MySQL数据库使用索引条件要求的SQL语句，也无索引信息可用，也即MySQL数据库枚举类型字段值域列表中的存储序列编号，无法做到替代索引的作用，也即依然需要显式创建数据库索引，加速数据查找速度。
---------------------
作者：Amy—go

Amygo

b)为枚举类型字段创建索引
root@localhost : mysqlops 03:40:59> ALTER TABLE mysqlops_set_enum ADD INDEX idx_Work_Option_enum(Work_Option);
Query OK, 0 rows affected (1 min 14.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
小结：
mysql数据库枚举类型字段上创建普通索引，也是需要表级锁、创建临时表等方式实现，并没有什么内部特殊的机制可使用。

Amygo

c)枚举类型字段有索引条件的SQL语句执行计划
root@localhost : mysqlops 03:42:53> EXPLAIN SELECT * FROM mysqlops_set_enum WHERE Work_Option=4 LIMIT 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mysqlops_set_enum
type: ref
possible_keys: idx_Work_Option_enum
key: idx_Work_Option_enum
key_len: 1
ref: const
rows: 3927162
Extra: Using where
1 row in set (0.00 sec)
小结：
mysql数据库枚举类型字段创建索引之后，若是根据枚举类型字段进行数据查找，且WHERE子句符合正确写法和枚举值所占比例符合使用索引的要求，即可根据索引数据完成数据查找。

Amygo

d)删除枚举类型字段上的索引
root@localhost : mysqlops 03:44:22> ALTER TABLE mysqlops_set_enum DROP INDEX idx_Work_Option_enum;
Query OK, 0 rows affected (0.80 sec)
Records: 0 Duplicates: 0 Warnings: 0
小结：
MySQL5.5.X版本数据库对于普通索引的删除操作，还是非常好的支持，并不需要创建临时表等操作，对于枚举类型字段上的索引也是同样适用的，关于这方面的文章可参考MySQL 5.5版本对普通索引增删性能的优化。

Amygo

(五)总结
通过上述对MySQL数据库表枚举类型字段的定义属性和索引方面的DDL变更操作，观察对枚举类型字段存储的数据影响，可以得出下列结论：
a)MySQL数据库枚举类型字段与其他数据类型一样，进行DDL变更操作可能产生的影响；
b)MySQL数据库枚举类型字段的DDL变更操作，属于枚举类型字段特有的内容：
I.枚举类型字段的枚举数据值域列表，以尾部追加枚举元素值的方式，不会出现锁表等；
II.枚举类型字段的枚举数据值域列表中，若是调整枚举类型枚举元素值的顺序，不会导致数据库表存储的数据出现错乱对照关系，数据库表存储数值会发生更新，以及需要锁表等操作；
III.删除枚举类型字段的枚举数据值域列表中，某个枚举元素值，会导致数据库表已存储的数据行出现截断，以及需要锁表等操作；
IV.枚举类型字段内部的枚举数据与存储序号之间的对照关系，不会能起到MySQL数据库表索引的功能；
V.枚举类型字段存储的数据值，则是枚举类型枚举元素的序列编号，而不是真实的字符串数据，而是通过其内部对照表的方式转换而实现的；