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

时间：2023-01-12 09:02:38

MySQL数据库提供针对字符串存储的一种特殊数据类型：集合类型SET，这种数据类型可以给予我们更多提高性能、降低存储容量和降低程序代码理解的技巧，前面介绍了首先介绍了四种数据类型的特性总结，其后又分别介绍了布尔类型BOOL或称布尔类型BOOLEAN、枚举类型ENUM，本文我们详细介绍集合类型set测试过程与总结，加深对mysql数据库集合类型set的理解记忆。

n  集合类型 SET

a).数据库表mysqlops_set结构

执行创建数据库表mysqlops_set的语句：

root@localhost : test 05:06:13> CREATE TABLE Mysqlops_SET(ID INT NOT NULL AUTO_INCREMENT,

    ->                            Work_Option  SET('','DBA','SA','Coding Engineer','JavaScript','NA','QA','other') NOT NULL,

    ->                            Work_City    SET('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_set表的结构定义信息：

root@localhost : test 04:33:38> SHOW CREATE TABLE Mysqlops_setG

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

       Table: Mysqlops_set

Create Table: CREATE TABLE `Mysqlops_set` (

  `ID` int(11) NOT NULL AUTO_INCREMENT,

  `Work_Option` set('','DBA','SA','Coding Engineer','JavaScript','NA','QA','other') NOT NULL,

  `Work_City` set('shanghai','beijing','hangzhou','shenzhen','guangzhou','other') NOT NULL DEFAULT 'shanghai',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

小结：

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

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

root@localhost : test 05:06:19> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(1,'QA','shanghai');

Query OK, 1 row affected (0.00 sec)

root@localhost : test 05:06:26> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(2,'NA','');

Query OK, 1 row affected (0.00 sec)

root@localhost : test 05:06:33> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(3,'Other',NULL);

ERROR 1048 (23000): Column 'Work_City' cannot be null

root@localhost : test 05:06:47> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(3,'','hangzhou');

Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:06:55> INSERT INTO Mysqlops_SET(ID,Work_City) VALUES(4,'ningbo');

Query OK, 1 row affected, 2 warnings (0.00 sec)

root@localhost : test 05:07:09> SHOW WARNINGS;

+---------+------+--------------------------------------------------+

| Level   | Code | Message                                          |

+---------+------+--------------------------------------------------+

| Warning | 1364 | Field 'Work_Option' doesn't have a default value |

| Warning | 1265 | Data truncated for column 'Work_City' at row 1   |

+---------+------+--------------------------------------------------+

2 rows in set (0.00 sec)
root@localhost : test 05:07:19> INSERT INTO Mysqlops_SET(ID,Work_Option) VALUES(5,'DBA');

Query OK, 1 row affected (0.00 sec)

root@localhost : test 03:06:01> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(6,'DBA','shanghai');

Query OK, 1 row affected (0.00 sec)

root@localhost : test 03:06:10> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(7,'DBA,SA','shanghai,beijing');

Query OK, 1 row affected (0.00 sec)

root@localhost : test 03:06:18> INSERT INTO Mysqlops_SET(ID,Work_Option,Work_City) VALUES(8,'DBA,SA,NA','shanghai,beijing,hangzhou');

Query OK, 1 row affected (0.00 sec)

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');

Query OK, 1 row affected (0.00 sec)
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');

Query OK, 1 row affected, 1 warning (0.00 sec)

root@localhost : test 03:18:27> show warnings;

+---------+------+------------------------------------------------+

| Level   | Code | Message                                        |

+---------+------+------------------------------------------------+

| Warning | 1265 | Data truncated for column 'Work_City' at row 1 |

+---------+------+------------------------------------------------+

1 row in set (0.00 sec)

小结：

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

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元素的存储顺序梳理：

l  集合类型字段定义的值域列表信息

集合类型值域列表顺序

值域列表顺序对应的元素值

存储序号二进制编码

二进制编码对应的十进制值

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

l  集合类型字段存储的值

自增序列字段的值

集合类型字段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

小结：

  1. 出现字段值截断警告提示信息的记录行，对应的字段值都是用空字符串替换掉，且存储序号为0；
  2. 若是集合类型SET字段列表中，元素列表中显式定义空字符串，其存储序列编号也为0；
  3. 通过根据存储序号查询出来的数据，与集合类型SET元素列表顺序、自增序列值大小对比，我们可以得出如下信息：

l  集合类型SET值域列表中，第一个元素的存储顺序编号为：0000,0001；

l  集合类型SET值域列表中，第一个元素之后的元素存储顺序编号为，前一个元素存储序号十进制值的二倍；

l  集合类型SET字段，若存储的数据是值域列表中的元素组合，则是每个元素存储顺序的值相加，则是对应组合值的存储顺序号；

数据类型系列讲解文章列表：
MySQL数据库数据类型之ENUM、SET、BOOL/BOOLEAN、TINYINT特性介绍

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

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

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

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

MySQL数据库之集合类型SET的DDL变更测试总结

MySQL数据库之布尔类型、枚举类型和集合类型的应用场景详解

相关文章