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

Amygo

(一)系统环境
硬件：DELL R510 10块盘做的RAID5，上面跑了几十台虚拟机
操作系统：CentOS release 5.5 (Final)
MySQL数据库：5.5.15-log
InnoDB存储引擎：plugin-InnoDB 1.1.8

Amygo

(二)测试数据准备

A.创建表结构的SQL命令

CREATE DATABASE mysqlops CHARACTER SET ‘utf8’ COLLATE ‘utf8_general_ci’;
USE mysqlops;

CREATE TABLE mysqlops_set_enum_bk (
ID INT NOT NULL AUTO_INCREMENT,
Work_Option set(‘JavaScript’,‘DBA’,‘SA’,‘C++’,‘NA’,‘QA’,‘Java’,‘PHP’,’’,‘Python’),
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=1 DEFAULT CHARSET=utf8;

Amygo

B.用于分隔字符串的函数代码
USE mysqlops;

DELIMITER $$

DROP FUNCTION IF EXISTS fun_split $$
CREATE FUNCTION fun_split(strValue VARCHAR(500),strSplit CHAR(1),iPostion TINYINT)
RETURNS VARCHAR(20) DETERMINISTIC
BEGIN
DECLARE strValue_New VARCHAR(500) DEFAULT ‘’;
DECLARE strValue_Old VARCHAR(500) DEFAULT ‘’;
DECLARE strReturn VARCHAR(20) DEFAULT ‘’;

DECLARE iLength_Old TINYINT DEFAULT 0;
DECLARE iLength_New TINYINT DEFAULT 0;
DECLARE iLength TINYINT DEFAULT 0;

SET strValue_New=SUBSTRING_INDEX(strValue,strSplit,iPostion);
– SELECT SUBSTRING_INDEX(‘DBA,SA,C++,JavaScript,NA,QA,Java,PHP,other,Python’,’,’,10);
– DBA,SA,C++,JavaScript,NA,QA,Java,PHP,other,

IF iPostion-1<>0 THEN
SET strValue_Old=SUBSTRING_INDEX(strValue,strSplit,iPostion-1);
SET iLength_Old=LENGTH(strValue_Old);
ELSE
SET iLength_Old=-1;
END IF;

SET iLength_New=LENGTH(strValue_New);
SET iLength=iLength_New-iLength_Old;

SET strReturn=SUBSTRING(strValue_new,iLength_Old+2,iLength);

RETURN strReturn;
END $$

DELIMITER ;

Amygo

C.用于生成测试数据的存储过程代码

USE mysqlops;

DELIMITER $$

DROP PROCEDURE IF EXISTS usp_mysqlops_set_enum $$

CREATE PROCEDURE usp_mysqlops_set_enum(strenum_job VARCHAR(500),strset_city VARCHAR(500),idata_total INT)
BEGIN
DECLARE isplit_num TINYINT DEFAULT 0;
DECLARE idata_num INT DEFAULT 0;
DECLARE iwhile_num INT DEFAULT 0;
DECLARE icommit SMALLINT DEFAULT 0;

1. DECLARE irnd_f     TINYINT DEFAULT 0;
   
2. DECLARE irnd_s     TINYINT DEFAULT 0;
   
3. 
   
4. DECLARE strenum_job_sub VARCHAR(20) DEFAULT '';
   
5. 
   
6. 
   
7. SET isplit_num=LENGTH(strenum_job)-LENGTH(REPLACE(strenum_job,',',''));
   
8. SET idata_num=FLOOR(idata_total/isplit_num);
   
9. 
   
10. WHILE isplit_num<>0
    
11. DO
    
12.    SET strenum_job_sub=fun_split(strenum_job,',',isplit_num);
    
13.    
    
14.    WHILE iwhile_num<idata_num
    
15.    DO
    
16.      IF icommit=0 THEN
    
17.         START TRANSACTION;
    
18.      END iF;
    
19.      
    
20.      SET irnd_f=SUBSTRING(RAND(),3,1);
    
21.      SET irnd_s=SUBSTRING(RAND(),3,1);
    
22.      
    
23.      INSERT INTO mysqlops_set_enum(Work_Option,Work_City)
    
24.      VALUES(strenum_job_sub,CONCAT(fun_split(strset_city,',',irnd_f),',',fun_split(strset_city,',',irnd_s)));
    
25.      
    
26.      IF icommit=10000 THEN
    
27.          SET icommit=0;
    
28.          COMMIT;
    
29.      END IF;
    
30.      
    
31.      SET icommit=icommit+1;
    
32.      SET iwhile_num=iwhile_num+1;
    
33.    END WHILE;
    
34.    
    
35.    SET iwhile_num=0;
    
36.    SET isplit_num=isplit_num-1;
    

复制代码

END WHILE;

END $$
DELIMITER ;

Amygo

D.生成测试数据
传入三个收入参数：枚举类型ENUM字段的值、集合类型SET字段的值、目标生成测试数据的总条数2000W，命令如下：

USE mysqlops;
CALL usp_mysqlops_set_enum(‘DBA,SA,C++,JavaScript,NA,QA,Java,PHP,other,Python’,‘shanghai,beijing,hangzhou,shenzhen,guangzhou,xiamen,tianjin,qingdao,dalian,xian,other’,20000000);

Amygo

(三)总结
以上几层楼主要是为接下来的集合数据类型SET的DDL变更测试、枚举数据类型ENUM的DDL变更测试而准备测试环境的信息，且这些都是用共同的测试表数据，也为方便大家的理解与后续的阅读，主要内容是MySQL函数与MySQL存储过程，可以借鉴到的信息：
a)提供一个较通用性的字符串分拆函数，也即指定分隔符号，再传入字符串和分拆字符的顺序号，即返回想获得的字符串数据；
b)MySQL函数的正确编写技巧；
c)MySQL存储过程的编写技巧；
d)MySQL提供的语句段控制循环操作的WHILE用法；
e)MySQL函数与MySQL存储过程内，都有各种字符串函数的调用，也请读者学习其用法；
f)MySQL存储过程中采用了多条语句写入，批量提交的事务技巧，以加速InnoDB存储引擎INSERT事务的处理速度。

Amygo

接下来我们深入介绍枚举类型EUNM和集合类型SET。测试基于InnoDB存储引擎上，对MySQL数据库枚举类型ENUM的字段进行DDL变更操作，是否需要重新创建表呢？对数据库的事务处理有何影响？对数据库的数据服务提供有何性能影响？

Amygo

(一)系统环境
硬件：DELL R510 10块盘做的RAID5，上面跑了几十个虚拟机
操作系统：CentOS release 5.5 (Final)
MySQL数据库：5.5.15-log
InnoDB存储引擎：plugin-InnoDB 1.1.8

(二)测试数据准备
详见之前的楼层

Amygo

(三)枚举类型ENUM字段DDL操作
a)增加枚举类型字段定义的默认值属性

1. 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’;
   
2. 
   
3. Query OK, 20017251 rows affected (2 min 7.76 sec)
   
4. 
   
5. Records: 20017251 Duplicates: 0 Warnings: 0

复制代码

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

Amygo

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

1. 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 ‘’;
   
2. 
   
3. Query OK, 0 rows affected (0.00 sec)
   
4. 
   
5. Records: 0 Duplicates: 0 Warnings: 0

复制代码

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