MySQL中的SET类型

SET类型和ENUM类型一样，也是字符串类型的一种。

A SET is a string object that can have zero or more values, each of which must be chosen from a list
of permitted values specified when the table is created. SET column values that consist of multiple set
members are specified with members separated by commas (,). A consequence of this is that SET
member values should not themselves contain commas.

SET的值范围也需要在创建表时显示指定。比如，一个列的数据类型指定为SET(‘one’, ‘two’) NOT NULL，它可以具有以下值:

'one'
'two'
'one,two'
复制

SET类型定义的时候可以包含0~64个成员。根据成员的不同，存储上也有所不同。

• 1~8成员的集合，占1个字节。
• 9~16成员的集合，占2个字节。
• 17~24成员的集合，占3个字节。
• 25~32成员的集合，占4个自己。
• 33~64成员的集合，占8个字节。

SET和ENUM最主要的区别在于SET类型一次可以使用多个成员，而ENUM则只能一个。

下面我们就通过例子来测试下。

root@database-one 23:39:  [gftest]> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
Query OK, 0 rows affected (0.03 sec)

root@database-one 23:39:  [gftest]> INSERT INTO myset (col) VALUES('a,d');
Query OK, 1 row affected (0.00 sec)

root@database-one 23:39:  [gftest]> INSERT INTO myset (col) VALUES('d,a');
Query OK, 1 row affected (0.01 sec)

root@database-one 23:40:  [gftest]> INSERT INTO myset (col) VALUES('a,d,a');
Query OK, 1 row affected (0.02 sec)

root@database-one 23:40:  [gftest]> INSERT INTO myset (col) VALUES('a,d,d');
Query OK, 1 row affected (0.00 sec)

root@database-one 23:40:  [gftest]> INSERT INTO myset (col) VALUES('d,a,d');
Query OK, 1 row affected (0.01 sec)

root@database-one 23:41:  [gftest]> INSERT INTO myset (col) VALUES('A');
Query OK, 1 row affected (0.01 sec)

root@database-one 23:41:  [gftest]> INSERT INTO myset (col) VALUES('a,C,b,d');
Query OK, 1 row affected (0.00 sec)

root@database-one 23:41:  [gftest]> select * from myset;
+---------+
| col     |
+---------+
| a,d     |
| a,d     |
| a,d     |
| a,d     |
| a,d     |
| a       |
| a,b,c,d |
+---------+
7 rows in set (0.00 sec)
复制

通过上面可以看到，SET类型的列确实可以接受多个成员，同时：

• 值中的每个元素都只会出现一次
• 插入时元素的顺序无关紧要，会按照在表创建时指定的顺序列出
• 忽略大小写，在存储时将它们都转换成了建表时定义的大/小写

如果插入不在集合范围内的值时，会怎么样呢？我们来试试：

root@database-one 23:52:  [gftest]> INSERT INTO myset (col) VALUES('e');
Query OK, 1 row affected, 1 warning (0.00 sec)

root@database-one 23:54:  [gftest]> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.01 sec)

root@database-one 23:54:  [gftest]> select * from myset;
+---------+
| col     |
+---------+
| a,d     |
| a,d     |
| a,d     |
| a,d     |
| a,d     |
| a       |
| a,b,c,d |
|         |
+---------+
8 rows in set (0.00 sec)

root@database-one 23:55:  [gftest]> INSERT INTO myset (col) VALUES('a,e');
Query OK, 1 row affected, 1 warning (0.00 sec)

root@database-one 23:55:  [gftest]> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

root@database-one 23:55:  [gftest]> select * from myset;
+---------+
| col     |
+---------+
| a,d     |
| a,d     |
| a,d     |
| a,d     |
| a,d     |
| a       |
| a,b,c,d |
|         |
| a       |
+---------+
9 rows in set (0.00 sec)
复制

通过上面可以看到，插入的值不在集合范围内，会被忽略并且产生警告。