【導讀】
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_set\\G
*************************** 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 | +---------+------+---------------------
| 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@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:
+----+-------------+-----------+
| 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
小結： 出現字段值截斷警告提示信息的記錄行，對應的字段值都是用空字符串替換掉，且存儲序號為0; 若是集合類型SET字段列表中，元素列表中顯式定義
小結：
出現字段值截斷警告提示信息的記錄行，對應的字段值都是用空字符串替換掉，且存儲序號為0;
若是集合類型SET字段列表中，元素列表中顯式定義空字符串，其存儲序列編號也為0;
通過根據存儲序號查詢出來的數據，與集合類型SET元素列表順序、自增序列值大小對比，我們可以得出如下信息：
l 集合類型SET值域列表中，第一個元素的存儲順序編號為：0000,0001;
l 集合類型SET值域列表中，第一個元素之後的元素存儲順序編號為，前一個元素存儲序號十進制值的二倍;
l 集合類型SET字段，若存儲的數據是值域列表中的元素組合，則是每個元素存儲順序的值相加，則是對應組合值的存儲順序號;