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

Amygo · 发表于 2019-6-24 10:20

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)

复制代码

枚举类型数据小结：
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。
---------------------
作者：Amy—go

Amygo · 发表于 2019-6-28 16:04

MySQL数据库提供针对字符串存储的一种特殊数据类型：集合类型SET，这种数据类型可以给予我们更多提高性能、降低存储容量和降低程序代码理解的技巧。

集合类型 SET

a).数据库表mysqlops_set结构
执行创建数据库表mysqlops_set的语句：

<div>root@localhost : test 05:06:13> CREATE TABLE Mysqlops_SET(ID INT NOT NULL AUTO_INCREMENT,</div><div>-> Work_Option SET(’’,‘DBA’,‘SA’,‘Coding Engineer’,‘JavaScript’,‘NA’,‘QA’,‘other’) NOT NULL,</div><div>-> Work_City SET(‘shanghai’,‘beijing’,‘hangzhou’,‘shenzhen’,‘guangzhou’,‘other’) NOT NULL DEFAULT ‘shanghai’,</div><div>-> PRIMARY KEY(ID)</div><div>-> )ENGINE=InnoDB CHARACTER SET ‘utf8’ COLLATE ‘utf8_general_ci’;</div><div>Query OK, 0 rows affected (0.00 sec)</div>

复制代码

查阅数据库中创建的mysqlops_set表的结构定义信息：

<div>root@localhost : test 04:33:38> SHOW CREATE TABLE Mysqlops_set\G</div><div>*************************** 1. row ***************************</div><div>Table: Mysqlops_set</div><div>Create Table: CREATE TABLE Mysqlops_set (</div><div>ID int(11) NOT NULL AUTO_INCREMENT,</div><div>Work_Option set(’’,‘DBA’,‘SA’,‘Coding Engineer’,‘JavaScript’,‘NA’,‘QA’,‘other’) NOT NULL,</div><div>Work_City set(‘shanghai’,‘beijing’,‘hangzhou’,‘shenzhen’,‘guangzhou’,‘other’) NOT NULL DEFAULT ‘shanghai’,</div><div>PRIMARY KEY (ID)</div><div>) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8</div><div>1 row in set (0.00 sec)</div>

复制代码

小结：
对于集合类型字段定义，MySQL没有做任何强制性转换或修改。

b). 写入不同类型的测试数据

<div>root@localhost : test 05:06:19> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(1,‘QA’,‘shanghai’);</div><div>Query OK, 1 row affected (0.00 sec)</div><div>
</div><div>root@localhost : test 05:06:26> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(2,‘NA’,’’);</div><div>Query OK, 1 row affected (0.00 sec)</div><div>
</div><div>root@localhost : test 05:06:33> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(3,‘Other’,NULL);</div><div>ERROR 1048 (23000): Column ‘Work_City’ cannot be null</div><div>
</div><div>root@localhost : test 05:06:47> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(3,’’,‘hangzhou’);</div><div>Query OK, 1 row affected (0.00 sec)</div><div>
</div><div>root@localhost : test 05:06:55> INSERT INTO Mysqlops_SET(ID,Work_City) VALUES(4,‘ningbo’);</div><div>Query OK, 1 row affected, 2 warnings (0.00 sec)</div><div>
</div><div>root@localhost : test 05:07:09> SHOW WARNINGS;</div><div>±--------±-----±-------------------------------------------------+</div><div>| Level | Code | Message |</div><div>±--------±-----±-------------------------------------------------+</div><div>| Warning | 1364 | Field ‘Work_Option’ doesn’t have a default value |</div><div>| Warning | 1265 | Data truncated for column ‘Work_City’ at row 1 |</div><div>±--------±-----±-------------------------------------------------+</div><div>2 rows in set (0.00 sec)</div><div>
</div><div>root@localhost : test 05:07:19> INSERT INTO Mysqlops_SET(ID,Work_Option) VALUES(5,‘DBA’);</div><div>Query OK, 1 row affected (0.00 sec)</div><div>
</div><div>root@localhost : test 03:06:01> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(6,‘DBA’,‘shanghai’);</div><div>Query OK, 1 row affected (0.00 sec)</div><div>
</div><div>root@localhost : test 03:06:10> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(7,‘DBA,SA’,‘shanghai,beijing’);</div><div>Query OK, 1 row affected (0.00 sec)</div><div>
</div><div>root@localhost : test 03:06:18> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(8,‘DBA,SA,NA’,‘shanghai,beijing,hangzhou’);</div><div>Query OK, 1 row affected (0.00 sec)</div><div>
</div><div>root@localhost : test 03:06:12> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(9,‘DBA,SA,NA’,‘shanghai,beijing,hangzhou,shenzhen,guangzhou,other’);</div><div>Query OK, 1 row affected (0.00 sec)</div><div>
</div><div>root@localhost : test 03:18:25> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(20,‘DBA,SA,NA’,‘shanghai,beijing,hangzhou!shenzhen!guangzhou!other’);</div><div>Query OK, 1 row affected, 1 warning (0.00 sec)</div><div>
</div><div>root@localhost : test 03:18:27> show warnings;</div><div>±--------±-----±-----------------------------------------------+</div><div>| Level | Code | Message |</div><div>±--------±-----±-----------------------------------------------+</div><div>| Warning | 1265 | Data truncated for column ‘Work_City’ at row 1 |</div><div>±--------±-----±-----------------------------------------------+</div><div>1 row in set (0.00 sec)</div>

复制代码

小结：
I.集合类型SET字段即使没有定义空格字符串作为元素，也会默认成为其中一个组成元素；
II.集合类型SET字段不允许为NULL时，向其写入NULL值会报错，导致SQL执行失败；
III.集合类型SET字段不允许为NULL且无显式申明默认值时，未集合类型字段给出值的INSERT操作，会出现警告信息，提示字段值阶段，并且用空字符串值替代，SQL语句执行成功；
IV.集合类型SET字段的值域列表中有空字符串元素时，决断用空字符串值替代的记录，与显式写入空字符串值的序列编号不同，前者序号为0，后者序号为值域列表中真实的顺序；
V.向集合类型SET字段写入一个值域列表中，不存在的值，会发生字段值截断，并且用空格字符串替代，SQL语句执行成功；
VI.集合类型SET字段值域列表中任意元素的组合，只要用逗号分隔，就是合法的值；
VII.集合类型SET字段值域列表中任意元素的组合时，若部分元素的值没有用逗号分隔，或者部分不是值域列表中元素值或组合，则会把非法的部分截断掉，并且给出警告信息，SQL语句执行成功；

Amygo · 发表于 2019-7-1 18:02

c). 查询数据库表mysqlops_SET的数据

root@localhost : test 03:18:31> select * from mysqlops_SET;±—±----------------±---------------------------------------------------+| ID | Work_Option | Work_City |±—±----------------±---------------------------------------------------+| 1 | QA | shanghai || 2 | NA | || 3 | | hangzhou || 4 | | || 5 | DBA | shanghai || 6 | DBA | shanghai || 7 | DBA,SA | shanghai,beijing || 8 | DBA,SA,NA | shanghai,beijing,hangzhou || 9 | DBA,SA,NA | shanghai,beijing,hangzhou,shenzhen,guangzhou,other || 10 | Coding Engineer | || 11 | Coding Engineer | shanghai || 12 | | shanghai || 13 | | hangzhou || 20 | DBA,SA,NA | shanghai,beijing |±—±----------------±---------------------------------------------------+14 rows in set (0.00 sec)
root@localhost : test 03:07:02> SELECT * FROM mysqlops_set WHERE Work_Option=0;±—±------------±----------+| ID | Work_Option | Work_City |±—±------------±----------+| 3 | | hangzhou || 4 | | |±—±------------±----------+2 rows in set (0.00 sec)
root@localhost : test 03:07:42> SELECT * FROM mysqlops_set WHERE Work_Option=1;Empty set (0.00 sec)
root@localhost : test 03:07:44> SELECT * FROM mysqlops_set WHERE Work_Option=2;±—±------------±----------+| ID | Work_Option | Work_City |±—±------------±----------+| 5 | DBA | shanghai || 6 | DBA | shanghai |±—±------------±----------+2 rows in set (0.00 sec)
root@localhost : test 03:08:09> SELECT * FROM mysqlops_set WHERE Work_Option=6;±—±------------±-----------------+| ID | Work_Option | Work_City |±—±------------±-----------------+| 7 | DBA,SA | shanghai,beijing |±—±------------±-----------------+1 row in set (0.00 sec)
root@localhost : test 03:10:04> SELECT * FROM mysqlops_set WHERE Work_City=0;±—±------------±----------+| ID | Work_Option | Work_City |±—±------------±----------+| 2 | NA | || 4 | | |±—±------------±----------+2 rows in set (0.00 sec)
root@localhost : test 03:10:18> SELECT * FROM mysqlops_set WHERE Work_City=1;±—±------------±----------+| ID | Work_Option | Work_City |±—±------------±----------+| 1 | QA | shanghai || 5 | DBA | shanghai || 6 | DBA | shanghai |±—±------------±----------+3 rows in set (0.00 sec)
root@localhost : test 03:10:20> SELECT * FROM mysqlops_set WHERE Work_City=2;Empty set (0.00 sec)
root@localhost : test 03:10:22> SELECT * FROM mysqlops_set WHERE Work_City=3;±—±------------±-----------------+| ID | Work_Option | Work_City |±—±------------±-----------------+| 7 | DBA,SA | shanghai,beijing |±—±------------±-----------------+1 row in set (0.00 sec)
root@localhost : test 03:10:24> SELECT * FROM mysqlops_set WHERE Work_City=4;±—±------------±----------+| ID | Work_Option | Work_City |±—±------------±----------+| 3 | | hangzhou |±—±------------±----------+1 row in set (0.00 sec)
root@localhost : test 03:10:30> SELECT * FROM mysqlops_set WHERE Work_City=7;±—±------------±--------------------------+| ID | Work_Option | Work_City |±—±------------±--------------------------+| 8 | DBA,SA,NA | shanghai,beijing,hangzhou |±—±------------±--------------------------+1 row in set (0.00 sec)
root@localhost : test 03:13:13> SELECT * FROM mysqlops_set WHERE Work_City=63;±—±------------±---------------------------------------------------+| ID | Work_Option | Work_City |±—±------------±---------------------------------------------------+| 9 | DBA,SA,NA | shanghai,beijing,hangzhou,shenzhen,guangzhou,other |±—±------------±---------------------------------------------------+1 row in set (0.00 sec)

复制代码

集合类型SET元素的存储顺序梳理：
集合类型字段定义的值域列表信息
集合类型值域列表顺序值域列表顺序对应的元素值存储序号二进制编码二进制编码对应的十进制值
1 shanghai 0000,0001 1
2 beijing 0000,0010 2
3 hangzhou 0000,0100 4
4 shenzhen 0000,1000 8
5 guangzhou 0001,0000 16
6 other 0010,0000 32

集合类型字段存储的值
自增序列字段的值集合类型字段Work_City的值二进制编码十进制值
1 shanghai 0000,0001 1
2 空格字符串 0000,0000 0
3 hangzhou 0000,0100 4
4 空格字符串 0000,0000 0
5 shanghai 0000,0001 1
6 shanghai 0000,0001 1
7 Shanghai,beijing 0000,0011 3
8 shanghai,beijing,hangzhou 0000,0111 7
9 shanghai,beijing,hangzhou,shenzhen,guangzhou,other 0011,1111 63
10 空格字符串 0000,0000 0
11 shanghai 0000,0001 1
12 shanghai 0000,0001 1
13 hangzhou 0000,0100 4
20 shanghai,beijing 0000,0011 3

小结：
I.出现字段值截断警告提示信息的记录行，对应的字段值都是用空字符串替换掉，且存储序号为0；
II.若是集合类型SET字段列表中，元素列表中显式定义空字符串，其存储序列编号也为0；
III.通过根据存储序号查询出来的数据，与集合类型SET元素列表顺序、自增序列值大小对比，我们可以得出如下信息：
集合类型SET值域列表中，第一个元素的存储顺序编号为：0000,0001；
集合类型SET值域列表中，第一个元素之后的元素存储顺序编号为，前一个元素存储序号十进制值的二倍；
集合类型SET字段，若存储的数据是值域列表中的元素组合，则是每个元素存储顺序的值相加，则是对应组合值的存储顺序号。

Amygo · 发表于 2019-7-2 18:08

前面几节Amy给大家分享了STE集合类型和枚举类型ENUM的测试数据。之前看到一些平台上很多人咨询mysql是否提供布尔类型的问题。MySQL数据库确实提供布尔类型，此数据类型的关键字可以写成：布尔类型BOOL或布尔类型BOOLEAN，但是MySQL数据库数据类型的布尔类型是否也像其他数据库产品一样呢？接下来Amy就为大家揭开mysql布尔类型的真实面目。

(一)数据类型测试
(1). 布尔类型BOOL/BOOLEAN 与微整型TINYINT

a). 创建测试表结构

root@localhost : test 05:12:49> CREATE TABLE boolean_test(ID INT NOT NULL AUTO_INCREMENT, -> Online_Flag BOOL, -> Lock_Flag BOOLEAN, -> PRIMARY KEY(ID) -> )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';Query OK, 0 rows affected (0.01 sec)

复制代码

我们可以发现对于字段类型写成BOOL或者BOOLEAN，MySQL的SQL语法都是允许通过的，另外我们再通过SHOW命令查阅创建好的表结构：

*************************** 1. row *************************** Table: boolean_testCreate Table: CREATE TABLE `boolean_test` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Online_Flag` tinyint(1) DEFAULT NULL, `Lock_Flag` tinyint(1) DEFAULT NULL, PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf81 row in set (0.00 sec)

复制代码

小结：
我们对比手工输入创建表boolean_test的结构定义与数据库中查阅到表结构定义，可以发现二者的差别：
I.MySQL数据库将字段的数据类型BOOL/BOOLEAN默认地转换成TINYINT(1)；
II.MySQL数据库自动完成的数据类型转换过程，没有给出任何错误或警告信息提示；
b). 测试数据的写入

root@localhost : test 05:12:58> INSERT INTO boolean_test(Online_Flag,Lock_Flag) VALUES(TRUE,FALSE);Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:13:58> INSERT INTO boolean_test(Online_Flag,Lock_Flag) VALUES(1,0);Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:14:04> INSERT INTO boolean_test(Online_Flag,Lock_Flag) VALUES(2,-1);Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:14:11> INSERT INTO boolean_test(Online_Flag,Lock_Flag) VALUES(-128,127);Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:14:18> INSERT INTO boolean_test(Online_Flag,Lock_Flag) VALUES(-256,256);Query OK, 1 row affected, 2 warnings (0.00 sec)
root@localhost : test 05:14:24> SHOW WARNINGS;+---------+------+------------------------------------------------------+| Level | Code | Message |+---------+------+------------------------------------------------------+| Warning | 1264 | Out of range value for column 'Online_Flag' at row 1 || Warning | 1264 | Out of range value for column 'Lock_Flag' at row 1 |+---------+------+------------------------------------------------------+2 rows in set (0.00 sec)

复制代码

小结：
I.测试数据表boolean_test的2个字段布尔类型字段，写入的值超过有符号整型TINYINT数据类型存储范围时，出现了字段值截断的警告信息；
II.向测试数据表boolean_test的字段可以写入表达布尔数值的TRUE 或 FALSE是不会报错，也不需要用单引号或双引号括起来；
III.向测试数据表boolean_test的字段可以写入非表达布尔类型的数值，MySQL数据库不会有任何错误或警告信息提示；

Amygo · 发表于 2019-7-2 18:09

c). 显示写入表boolean_test的数据

root@localhost : test 06:31:33> SELECT * FROM boolean_test;±—±------------±----------+| ID | Online_Flag | Lock_Flag |±—±------------±----------+| 1 | 1 | 0 || 2 | 1 | 0 || 3 | 2 | -1 || 4 | -128 | 127 || 5 | -128 | 127 |±—±------------±----------+5 rows in set (0.00 sec)

复制代码

小结：
通过查阅测试表boolean_test的数据，可以发现MySQL数据库中存储的值与数据写入的INSERT语句还是有一些差别，体现在：
I.写入的布尔类型值TRUE 转换成了 1，FALSE 转换成了 0；
II.超过TINYINT数据类型存储的上下限制的值，被自动截断；
III.布尔类型BOOL/BOOLEAN的功能等同于微整型TINYTINT。

Amygo · 发表于 2019-7-2 18:11

MySQL数据库四种数据类型：布尔类型、微整型、枚举类型和集合类型，都逐一分析这四种数据类型的特性，以及针对每种数据类型做相应的深入分析和案例测试，挖掘出MySQL手册没有详细写清楚的部分。
接下来我们结合实际的业务场景和生产环境维护成本等多个角度进行分析，阐述什么样的业务场景，适合使用布尔类型、枚举类型和集合类型？使用这三种数据类型之后，又回给我们带来哪些麻烦？如何规避这三种数据类型带来的弊端等问题。

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

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

Amygo · 发表于 2019-7-2 18:11

(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变更的故障。

Amygo · 发表于 2019-7-2 18:12

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

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

MySQL数据库支持集合类型，对解决一些特殊的业务场景提供了非常好的解决方案，经典应用场景案例：

人才招聘网站的用户，设置工作意向城市一项，则往往会选择1-3个城市，甚至更多城市，采用集合类型字段作为数据存储结果的话，将可以大量简化程序复杂度，以及大规模降低数据存储的容量，唯一的遗憾则是集合元素限制为64个，会导致无法满足招聘网站后期业务发展需要。

Amygo · 发表于 2019-7-3 16:01

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

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

Amygo · 发表于 2019-7-3 16:03

(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)

复制代码

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