MySQL数据库之枚举数据类型ENUM的DDL变更测试 -- MySQL -- IT技术博客大学习 -- 共学习共进步！

您现在的位置：首页 --> MySQL --> MySQL数据库之枚举数据类型ENUM的DDL变更测试

MySQL数据库之枚举数据类型ENUM的DDL变更测试

浏览:3644次出处信息

【导读】

针对四种数据类型：布尔类型BOOL或称布尔类型BOOLEAN、微整型TINYTINT、枚举类型ENUM、集合类型SET，我们已经分多篇文章篇幅给出详细的介绍与功能测试数据，接下来我们深入介绍枚举类型EUNM和集合类型SET。测试基于InnoDB存储引擎上，对MySQL数据库枚举类型ENUM的字段进行DDL变更操作，是否需要重新创建表呢？对数据库的事务处理有何影响？对数据库的数据服务提供有何性能影响？

(一) 系统环境

硬件：DELL R510 10块盘做的RAID5，上面跑了几十个虚拟机

操作系统：CentOS release 5.5 (Final)

MySQL数据库：5.5.15-log

InnoDB存储引擎：plugin-InnoDB 1.1.8

(二) 测试数据准备

鉴于篇幅的缘由，单独作为一篇文章分享在www.mysqlops.com网站上，请点击MySQL数据库之数据类型集合类型和枚举类型测试环境

(三) 枚举类型ENUM字段DDL操作

a) 增加枚举类型字段定义的默认值属性

root@localhost : mysqlops 02:35:51> ALTER TABLE mysqlops_set_enum MODIFY Work_Option enum(‘DBA’,'SA’,'C++’,'JavaScript’,'NA’,'QA’,'Java’,'PHP’,'other’,”) NOT NULL DEFAULT ‘DBA’;

Query OK, 20017251 rows affected (2 min 7.76 sec)

Records: 20017251 Duplicates: 0 Warnings: 0

小结：

枚举类型字段，由允许NULL值且无默认值的定义属性，变更为不允许存储NULL值和制定枚举类型字段的默认值，这个过程需要表级锁，锁住表堵塞其他事务性操作，与其他数据类型的字段属性变更是一样的。

b) 修改枚举类型字段定义的默认值

root@localhost : mysqlops 02:38:19> ALTER TABLE mysqlops_set_enum MODIFY Work_Option enum(‘DBA’,'SA’,'C++’,'JavaScript’,'NA’,'QA’,'Java’,'PHP’,'other’,”) NOT NULL DEFAULT ”;

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

小结：

枚举类型字段已经存在默认值，只是修改默认值为不同默认值的操作，是不需要重新建表与锁表，也是与其他数据类型字段的字段属性变更一样。

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

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’;

ERROR 1067 (42000): Invalid default value for ‘Work_Option’

小结：

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

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

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’;

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

小结：

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

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

root@localhost : mysqlops 02:38:10> SELECT * FROM mysqlops_set_enum WHERE Work_Option=1 LIMIT 1;

+———-+————-+——————-+

| ID | Work_Option | Work_City |

+———-+————-+——————-+

| 12017252 | DBA | guangzhou,tianjin |

+———-+————-+——————-+

1 row in set (3.22 sec)

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’;

Query OK, 20017251 rows affected (2 min 10.75 sec)

Records: 20017251 Duplicates: 0 Warnings: 0

root@localhost : mysqlops 02:42:01> SELECT * FROM mysqlops_set_enum WHERE Work_Option=1 LIMIT 1;

+———-+————-+——————-+

| ID | Work_Option | Work_City |

+———-+————-+——————-+

| 12017252 | JavaScript | guangzhou,tianjin |

+———-+————-+——————-+

1 row in set (3.22 sec)

小结：

对枚举类型字段的值域列表元素顺序进行调整，会发现：

将需要表级锁和重见数据存储表的方式，完成枚举类型字段表的结构调整；

枚举类型字段值域列表中受影响元素的存储顺序编号发生变化；

数据库表枚举类型字段存储的数据是枚举类型元素的编号，为此导致最终返回给我们查询操作的数据，也发生了变化；

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

root@localhost : mysqlops 02:05:24> SELECT COUNT(*) FROM mysqlops_set_enum WHERE Work_Option=”;

+———-+

| COUNT(*) |

+———-+

| 4017251 |

+———-+

1 row in set (5.00 sec)

root@localhost : mysqlops 02:05:40> SELECT * FROM mysqlops_set_enum limit 6000000,1;

+———+————-+———-+

| ID | Work_Option | Work_City |

+———+————-+———-+

| 6000001 | PHP | dalian |

+———+————-+———-+

1 row in set (1.38 sec)

root@localhost : mysqlops 02:06:55> SELECT COUNT(*) FROM mysqlops_set_enum WHERE Work_Option=’PHP’;

+———-+

| COUNT(*) |

+———-+

| 2000000 |

+———-+

1 row in set (5.11 sec)

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’;

Query OK, 20017251 rows affected, 65535 warnings (2 min 11.71 sec)

Records: 20017251 Duplicates: 0 Warnings: 2000000

root@localhost : mysqlops 02:54:01> SHOW WARNINGS;

+———+——+——————————————————-+

| Level | Code | Message |

+———+——+——————————————————-+

| Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017252 |

| Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017253 |

| Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017254 |

| Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017255 |

| Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017256 |

| Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017257 |

| Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017258 |

| Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017259 |

| Warning | 1265 | Data truncated for column ‘Work_Option’ at row 4017260 |

root@localhost : mysqlops 02:56:18> SELECT COUNT(*) FROM mysqlops_set_enum WHERE Work_Option=”;

+———-+

| COUNT(*) |

+———-+

| 6017251 |

+———-+

1 row in set (4.68 sec)

root@localhost : mysqlops 02:56:48> SELECT * FROM mysqlops_set_enum WHERE ID=6000001;

+———+————-+———-+

| ID | Work_Option | Work_City |

+———+————-+———-+

| 6000001 | | dalian |

+———+————-+———-+

1 row in set (0.00 sec)

小结：

对于枚举类型字段中已存储某枚举元素的数据，再删除枚举类型ENUM字段值域列表中某个枚举值，则会出现：

存在多少条要删除的枚举值记录数，就会产生多少条警告信息(注：警告信息最大值65535条)；

被删除枚举值对应的字段的记录值，会发生截断，并且用空字符串值填充；

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

root@localhost : mysqlops 02:57:30> SHOW CREATE TABLE mysqlops_set_enum\G

*************************** 1. row ***************************

Table: mysqlops_set_enum

Create Table: CREATE TABLE `mysqlops_set_enum` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Work_Option` enum(‘JavaScript’,'DBA’,'SA’,'C++’,'NA’,'QA’,'Java’,'other’,”,’Python’) NOT NULL DEFAULT ‘DBA’,

`Work_City` set(‘shanghai’,'beijing’,'hangzhou’,'shenzhen’,'guangzhou’,'xiamen’,'tianjin’,'qingdao’,'dalian’,'xian’,'other’) NOT NULL DEFAULT ‘shanghai’,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=20017252 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

(四) 枚举类型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数据库枚举类型字段值域列表中的存储序列编号，无法做到替代索引的作用，也即依然需要显式创建数据库索引，加速数据查找速度。

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数据库枚举类型字段上创建普通索引，也是需要表级锁、创建临时表等方式实现，并没有什么内部特殊的机制可使用。

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子句符合正确写法和枚举值所占比例符合使用索引的要求，即可根据索引数据完成数据查找。

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版本对普通索引增删性能的优化。

(五) 总结

通过上述对MySQL数据库表枚举类型字段的定义属性和索引方面的DDL变更操作，观察对枚举类型字段存储的数据影响，可以得出下列结论：

a) MySQL数据库枚举类型字段与其他数据类型一样，进行DDL变更操作可能产生的影响；

b) MySQL数据库枚举类型字段的DDL变更操作，属于枚举类型字段特有的内容：

枚举类型字段的枚举数据值域列表，以尾部追加枚举元素值的方式，不会出现锁表等；

枚举类型字段的枚举数据值域列表中，若是调整枚举类型枚举元素值的顺序，会导致数据库表存储的数据出现错乱对照关系，以及需要锁表等操作；

删除枚举类型字段的枚举数据值域列表中，某个枚举元素值，会导致数据库表已存储的数据行出现截断，以及需要锁表等操作；

枚举类型字段内部的枚举数据与存储序号之间的对照关系，不会能起到MySQL数据库表索引的功能；

枚举类型字段存储的数据值，则是枚举类型枚举元素的序列编号，而不是真实的字符串数据，而是通过其内部对照表的方式转换而实现的；

数据类型系列讲解文章列表：

MySQL数据库数据类型之ENUM、SET、BOOL/BOOLEAN、TINYINT

MySQL数据库之数据类型BOOL/BOOLEAN与TINYINT测试总结

MySQL数据库数据类型之枚举类型ENUM测试总结

MySQL数据库数据类型之集合类型SET测试总结

MySQL数据库之数据类型集合类型和枚举类型测试环境

备注：MySQL数据库数据类型之集合类型ENUM测试总结、MySQL数据库数据类型之枚举类型、集合类型和布尔类型的应用场景，将在下周一(2012-03-19日)分享到本站点，那么这次四种特殊数据类型系列的介绍技术文章将全部完成，期待大家整体阅读与指出其中可能存在的文字等错误，感谢大家的支持！

建议继续学习：

QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习

<< 前一篇：MySQL数据库InnoDB数据恢复工具使用总结

后一篇：MySQL 单表插入 10w+ TPS达成 >>

文章信息

作者：Eugene 来源： MySQLOPS 数据库与运维自动化技术分享
标签： DDL ENUM 枚举
发布时间：2012-03-18 23:25:06

建议继续学习

近3天十大热文