摘要：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)
通过上面可以看到，插入的值不在集合范围内，会被忽略并且产生警告。
© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。