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

Amygo

以上就是今天的内容，感兴趣的读者可以先去复习一下以前的帖子：【分布式事务数据库 —-MySQL 数据库开发规范http://www.itpub.net/thread-2113902-1-1.html】【技术贴——MySQL的左连接（知识点分享）http://www.itpub.net/thread-2116606-1-1.html】
相信一定会有更多的收获哦~

Amygo

我又来啦~今天也是认真为大家分享知识的Amy~觉得这个帖子有帮到你们的话可以互动一下哦~有什么疑问也可以发出来供大家探讨
·

Amygo

今天分享枚举类型ENUM的测试数据

Amygo

(2). 枚举类型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数据库默认修改任何信息。
---------------------
作者：Amy—go

Amygo

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)
---------------------
作者：Amy—go

Amygo

小结：
I.若是枚举类型字段定义为非NULL，默认的SQL_MODE模式下，插入NULL值则会报错，导致SQL语句执行失败；若是没有明确申明非NULL，则允许插入NULL值；
II.若是枚举类型字段的枚举值中，没有指定空格字符值，插入空格字符数据时，会出现数据截断的警告信息，但是SQL语句依然执行成功；
III.若是向枚举类型字段插入未在定义列表中出现的数据，则会出现数据截断的警告信息，但是SQL语句依然执行成功；
IV.若是枚举类型定义为非NULL，向数据库表中插入新数据，但未指定枚举类型字段的值，则使用枚举类型字段定义申明的默认值，若是无显示申明则是采用枚举类型字段的枚举列表中第一个值作为默认值；

Amygo

c). 查询数据库表mysqlops_enum的数据
查询枚举类型数据库表mysqlops_enum所有的数据（注释：两个枚举类型字段都是非NULL，所以截断后的值为空格）：
root@localhost : test 11:23:24> SELECT * FROM mysqlops_enum;
±—±---------±----------+
| ID | Job_type | Work_City |
±—±---------±----------+
| 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;
±—±---------±----------+
| ID | Job_type | Work_City |
±—±---------±----------+
| 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;
±—±---------±----------+
| ID | Job_type | Work_City |
±—±---------±----------+
| 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;
±—±---------±----------+
| ID | Job_type | Work_City |
±—±---------±----------+
| 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;
±—±---------±----------+
| ID | Job_type | Work_City |
±—±---------±----------+
| 4 | | hangzhou |
±—±---------±----------+
1 row in set (0.00 sec)
---------------------
作者：Amy—go

Amygo

枚举类型数据小结：
I.枚举类型字段定义必须为明确的值，不能为变量或函数等；
II.若是向枚举类型字段插入NULL值，且枚举类型的字段定义为非NULL，SQL语句会执行失败；
III.若是向枚举类型字段插入，其枚举列表中不存在的值，则会发生字段值的截断，并且用空格值替代，其存储的序列编号为0；
IV.若是枚举类型字段定义的枚举列表值中存在空格值，当该枚举类型字段发生字段值截断，则是用空格值替代，但是其存储的序列号与枚举列表中存储的序列号不同，也即参考事例所示：
root@localhost : test 04:37:32> SELECT * FROM mysqlops_enum WHERE Job_type=0;
±—±---------±----------+
| ID | Job_type | Work_City |
±—±---------±----------+
| 8 | | hangzhou |
±—±---------±----------+
1 row in set (0.00 sec)

root@localhost : test 04:37:35> SELECT * FROM mysqlops_enum WHERE Job_type=’’;
±—±---------±----------+
| ID | Job_type | Work_City |
±—±---------±----------+
| 4 | | hangzhou |
| 8 | | hangzhou |
±—±---------±----------+
2 rows in set (0.00 sec)
V.若是枚举类型字段定义为非NULL，且没有为该字段指定值的方式插入数据行，则把字段定义显示申明的默认值作为字段默认值，没有显示申明则把枚举列表中第一个值作为默认值；
VI.若是枚举类型字段允许插入NULL值，则NULL值对应存储的序列号为NULL；
VII.枚举类型字段定义值列表，若是以后插入追加的模式，则不需要重建表；
VIII.枚举类型字段定义值列表，若是进行列表值顺序的调整或删除操作，则类似传统的DDL操作需要重建表（待验证）。

Amygo

接下来更新MySQL数据库之数据类型集合类型和枚举类型测试准备信息

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

Amygo

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