【MySQL数据类型测试】布尔类型、枚举类型和集合类型的应用场景详解（第十节）

MySQL数据库四种数据类型：布尔类型、微整型、枚举类型和集合类型，都逐一分析这四种数据类型的特性，以及针对每种数据类型做相应的深入分析和案例测试，挖掘出MySQL手册没有详细写清楚的部分，相关技术文章可以从数据类型系列第一篇文章MySQL数据库数据类型之ENUM、SET、BOOL/BOOLEAN、TINYINT特性介绍开始阅读。
本文内容属于基于在此之前分享的9篇关于四种数据类型的文章之上，我们结合实际的业务场景和生产环境维护成本等多个角度进行分析，阐述什么样的业务场景，适合使用布尔类型、枚举类型和集合类型？使用这三种数据类型之后，又回给我们带来哪些麻烦？如何规避这三种数据类型带来的弊端等问题，将会逐一解答。

(1).布尔类型
MySQL数据库之数据类型BOOL/BOOLEAN与TINYINT测试总结一文的测试过程和结论，非常清晰地告诉我们：MySQL数据库的布尔类型BOOL或称布尔类型BOOLEAN，等同于微整型TINYINT(1)。MySQL数据库数据类型分类中确实存在布尔类型，但是MySQL数据库并没有真正实现布尔类型，而是借助微整型的方式实现，并且创建数据库表结构的时候，即使字段定义属性设置为布尔类型BOOL或布尔类型BOOLEAN，都会被默认改写成TINYINT(1)。

建议：
MySQL数据库产品没有真正实现对布尔类型的支持，建议大家不要使用MySQL布尔类型BOOL或布尔类型BOOLEAN，而是使用数据库类型微整型TINYINT替代。

(2).枚举类型ENUM
对枚举类型字段存储数据的数据测试案例分享和总结文章为MySQL数据库数据类型之枚举类型ENUM数据测试总结，对枚举类型字段进行DDL变更操作支持的案例分享和总结文章为MySQL数据库之枚举数据类型ENUM的DDL变更测试，通过详尽的测试对比过程，对MySQL枚举类型的特点非常清晰，我们再简要综合地回顾枚举类型的优缺点：

优点
1)MySQL枚举类型的枚举元素允许最大65535个，基本够绝大多数业务场景使用；
2)引入枚举类型数据存储，有利于缩减数据库存储数据的容量，尤其能达到减少数据库瓶颈最大的物理IO，逻辑IO也能减小，提高主机的处理能力；
3)引入枚举类型数据存储，有利于简化工程师的代码复杂度、工作量，增加代码的可读性和可维护性；
4)可以通过枚举类型元素值访问数据，也可以根据枚举类型元素编号进行访问数据；

缺点
1)MySQL数据库枚举类型的引入，可能给软件程序的版本发布，存在遗忘数据库结构变更的隐患；
2)MySQL数据库枚举类型字段的元素增加，必须以尾部追加的方式，否则影响数据库提供数据服务；
3)枚举类型字段不再需要的元素，也不能进行删除，否则影响数据库提供数据服务；
4)MySQL数据库枚举类型的字段定义属性元素值，不能随意调整其顺序，否则影响数据库提供数据服务；

建议：
MySQL数据库枚举类型是一种有应用场景广泛的数据类型，若是抛开网站程序或软件版本发布，可能会导致开发工程师与数据库维护人员之间没有配合好的问题，非常推荐大家把枚举类型引入到生产环境的数据库应用中，对企业而言也可以起到节省人力、物理等成本。建议大家使用枚举类型的时候，尽量把可能需要用到的枚举元素，都写到MySQL数据库表字段的定义属性中，减少出现漏做DDL变更的故障。

(3).集合类型SET
MySQL数据库数据类型之集合类型SET数据测试总结和MySQL数据库之集合类型SET的DDL变更测试文章，有完整的测试过程，充分总结MySQL数据库集合类型的优缺点，我们再简要地回顾集合类型的优缺点。
优点
1)数据库的数据存储容量相应缩小，利于减少数据操纵的逻辑IO和物理IO；
2)集合类型的数据读取方便，可根据字符串值，也可以根据字符串集合的顺序编号；
3)集合类型字段的定义属性维护与其他数据类型类似，并不特殊化；
4)开发工程师，不需要借助额外的集合元素编码表或程序中使用编号替代集合的字符串元素，达到减少开发成本、提高代码的可读性和可维护性；

缺点
1)集合类型字段的集合元素限制最大为64个；
2)集合类型字段的定义属性的集合元素，删除导致锁表而影响数据服务提供；
3)集合类型字段的定义属性的集合元素增加，只能以尾部追加的方式，若是此特性没有掌握，则会导致数据服务提供受影响；

MySQL数据库支持集合类型，对解决一些特殊的业务场景提供了非常好的解决方案，经典应用场景案例：
人才招聘网站的用户，设置工作意向城市一项，则往往会选择1-3个城市，甚至更多城市，采用集合类型字段作为数据存储结果的话，将可以大量简化程序复杂度，以及大规模降低数据存储的容量，唯一的遗憾则是集合元素限制为64个，会导致无法满足招聘网站后期业务发展需要。

电子商务等行业特点：
1)数据分类的种类较多，往往超过总数64的限制；
2)公司或产品开发维护的技术工程师更迭频繁；
3)电子商务、招聘网站等行业的产品运营周期长；
4)多数公司的软件程序版本发布流程不健全；
5)数据库维护人员和开发工程师的工作配合，容易出现信息同步不到位或不周全的情况；
6)用户的数据安全性和正确性，对企业非常重要，往往都是花费较贵的推广费用吸引而来的；
MySQL数据库中采用集合类型存储数据，生产环境的网站程序或软件版本更新发布时，一旦出现数据库维护人员没有优先更新数据库表字段的定义属性，则会导致重大的数据丢失事故，给企业造成直接的经济损失。

网络游戏行业的特点：
1)有大量业务是分类不多的数据需要存储（注：往往是几个分类，最多不超过30个分类）；
2)网络游戏行业要求开发成本降低、开发速度快等特点；
3)网络游戏公司采用的数据库服务器的硬件配置差，甚至单硬盘的主机支撑多个数据库提供数据服务，主机的存储空间也有限；
4)网络游戏产品的生命周期绝大部分不超过5年，一般的网络游戏产品运营1年以上就很少再更新软件的版本；
网络游戏产品使用MySQL数据库集合类型字段存储业务数据，是非常值得推荐的方式，对开发人员而言，跟使用其他数据类型是一样的，也无额外学习成本，还可以为企业降低开发成本、硬件资源成本。

(4).总结
系统地分析了布尔类型、枚举类型、集合类型，MySQL数据库没有实现布尔类型，只是借助微整型TINYINT(1)间接实现，为此可以理解MySQL数据库没有布尔类型；集合类型与枚举类型，都各自存在一个非常可怕的弊端 — 数据库结构变更没有做，网站程序或软件版本发布已上线，导致用户数据丢失的问题，那么我们不使用枚举类型或集合类型，能否获得这2种数据类型的优点，规避这2种数据类型的缺陷，为此我们各举一个实际应用案例。

取代集合类型应用场景—求职者工作意向城市

1)创建存储城市信息的表City（注：假设只有一个字段：城市名称）

CREATE TABLE city(ID MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
                  City_Name VARCHAR(20) NOT NULL DEFAULT '',
                  PRIMARY KEY(ID),
                  UNIQUE INDEX idx_city_name(City_Name)
                 )ENGINE=InnoDB CHARACTER SET'utf8' COLLATE'utf8_general_ci';

2)创建存储城市编号与求职者ID编号的对照关系表

CREATE TABLE user_work_city(UID INT UNSIGNED NOT NULL AUTO_INCREMENT,
                  CityID MEDIUMINT NOT NULL DEFAULT 0,
                  PRIMARY KEY(UID,CityID)
                 )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';  

3)写入几条测试数据

INSERT INTO city(City_Name) VALUES('上海'),('北京'),('深圳'),('广州'),('杭州'),('武汉');
INSERT INTO user_work_city(UID,CityID) VALUES(8263638,1),(8263638,3),(8263638,5);
root@localhost : mysqlops 04:09:17> SELECT * FROM city;
+----+-----------+
| ID | City_Name |
+----+-----------+
|  1 | 上海      |
|  2 | 北京      |
|  4 | 广州      |
|  5 | 杭州      |
|  6 | 武汉      |
|  3 | 深圳      |
+----+-----------+
6 rows in set (0.00 sec)

root@localhost : mysqlops 04:09:28> SELECT * FROM user_work_city;
+---------+--------+
| UID     | CityID |
+---------+--------+
| 8263638 |      1 |
| 8263638 |      3 |
| 8263638 |      5 |
+---------+--------+
3 rows in set (0.00 sec)

4)通过SQL语句获得求职者意向城市信息

root@localhost : mysqlops 04:09:35> SELECT UC.UID,GROUP_CONCAT(City_Name) FROM city C ,user_work_city UC
    -> WHERE  UC.UID=8263638 AND C.ID=UC.CityID
    -> GROUP BY UC.UID ORDER BY NULL;
+---------+-------------------------+
| UID     | GROUP_CONCAT(City_Name) |
+---------+-------------------------+
| 8263638 | 上海,深圳,杭州          |
+---------+-------------------------+
1 row in set (0.00 sec)

小结：
求职者可挑选工作意向城市列表，可以通过后台程序进行编辑，并且存储在City表中，可以任意进行添加、修改、删除，都不会造成网站程序版本的更新，也不会影响数据库提供的数据服务，且城市名称等信息的变更，只要修改City表即可。另外，通过简便的内连接SQL查询语句，就可以符合MySQL支持的JOIN连接算法—嵌套循环算法，效率非常高，也不需要程序进行特殊处理，完全可以取代集合类型的作用，唯一遗憾就是需要多写点代码。

电子商务行网站的商品分类目录
1)创建存储商品分类信息表

CREATE TABLE product_class(ID MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
                          Product_Description VARCHAR(40) NOT NULL DEFAULT '',
                          Parent_Flag TINYINT NOT NULL DEFAULT 0,
                          PRIMARY KEY(ID),
                          UNIQUE INDEX idx_Product_Desc(Product_Description)
                        )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE'utf8_general_ci';

2)创建存储商品分类目录之间的上下级关系表

CREATE TABLE pc_parent_child(ID MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
                           Parent_ID MEDIUMINT NOT NULL DEFAULT 0,
                           Child_ID  MEDIUMINT NOT NULL DEFAULT 0,
                           PRIMARY KEY(ID),
                           INDEX idx_pid_cid(Parent_ID,Child_ID)        
                         )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE'utf8_general_ci';

3)写入测试数据

INSERT INTO product_class(Product_Description) 
VALUES('大家电'),('平板电视'),('洗衣机'),('冰箱');
INSERT INTO product_class(Product_Description) 
VALUES('生活电器'),('取暖器'),('加湿器'),('净化器');

INSERT INTO pc_parent_child(Parent_ID,Child_ID)  VALUES(1,2);
INSERT INTO pc_parent_child(Parent_ID,Child_ID)  VALUES(1,3);
INSERT INTO pc_parent_child(Parent_ID,Child_ID)  VALUES(5,8);

4）商品分类数据查询
商品分类目录的数据读取，都是先读取父节点的信息，再根据父节点信息读取其下子节点的数据信息。

root@localhost : mysqlops 05:28:41> SELECT M.Product_Description 
    -> FROM product_class M 
    ->       INNER JOIN pc_parent_child N  ON M.ID=N.Child_ID
    -> WHERE N.Parent_ID=1 ;
+---------------------+
| Product_Description |
+---------------------+
| 平板电视            |
| 洗衣机              |
+---------------------+
2 rows in set (0.00 sec)

小结：
若是采用枚举类型存储商品分类信息，我们则可以简便地创建一张表，2个字段即可实现上述的做法，最大的缺陷是子节点升级为父节点的时候，则需要发布网站程序与做数据库结构变更。修改枚举类型字段的定义属性，必须以尾部追加的方式，才不影响数据库提供的数据服务。使用我们介绍的替代办法，则需要工程师编写更多的代码实现，可以借助图形化管理工具轻松完成，各自都有优缺点。

一句话总结：任何解决方案，必须从技术、开发成本、维护成本、可靠性等多个角度综合论证，寻找最适合团队、业务场景的方案。

《MySQL数据类型测试》一文到现在已经完全结束了，希望这些用心总结的文章（回看点击这里）能够真正的帮到各位同仁们。后面会开始更新新专题，感兴趣的可以先关注我哦~

点击查看往期文章

作者：Amy—go