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

发布时间:2020-03-19编辑:脚本学堂
本文详细介绍了mysql中的集合类型set的用法,学习下mysql数据库中集合类型的相关知识,有兴趣的朋友参考研究下。

说明:
mysql/ target=_blank class=infotextkey>mysql数据库提供针对字符串存储的一种特殊数据类型:集合类型SET,这种数据类型可以给予我们更多提高性能、降低存储容量和降低程序代码理解的技巧,前面介绍了首先介绍了四种数据类型的特性总结,其后又分别介绍了布尔类型BOOL或称布尔类型BOOLEAN、枚举类型ENUM。

本文详细介绍集合类型set测试过程与总结,加深对mysql数据库集合类型set的理解记忆。
n  集合类型 SET
2_1.gif
a).数据库表mysqlops_set结构
2_2.gif
执行创建数据库表mysqlops_set的语句:
 

复制代码 代码示例:
[root@jbxue] : 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@jbxue] : 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@jbxue] : 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@jbxue] : 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@jbxue] : 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@jbxue] : 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@jbxue] : 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@jbxue] : 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@jbxue] : test 05:07:19> INSERT INTO Mysqlops_SET(ID,Work_Option) VALUES(5,'DBA'); 
Query OK, 1 row affected (0.00 sec)
 
[root@jbxue] : 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@jbxue] : 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@jbxue] : 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@jbxue] : 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@jbxue] : 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@jbxue] : 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)
 

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

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

复制代码 代码示例:

[root@jbxue] : 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@jbxue] : 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@jbxue] : test 03:07:42> SELECT * FROM mysqlops_set WHERE Work_Option=1;
 
Empty set (0.00 sec)
 
[root@jbxue] : 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@jbxue] : 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@jbxue] : 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@jbxue] : 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@jbxue] : test 03:10:20> SELECT * FROM mysqlops_set WHERE Work_City=2;
 
Empty set (0.00 sec)
 
[root@jbxue] : 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@jbxue] : 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@jbxue] : 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@jbxue] : 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
 

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