（已更新。。。）
SET：
多选字符串数据类型，适合存储“多个值”。
设定set的时候，同样需要设定“固定的几个值”；存储的时候，可以存储其中的若干个值。
设定set的格式：
字段名称 SET("选项1","选项2",...,'选项n')
同样的，set的每个选项值也对应一个数字，依次是1，2，4，8，16...，最多有64个选项
使用的时候，可以使用set选项的字符串本身（多个选项用逗号分隔），也可以使用多个选项的数字之和（比如：1+2+4=7）
-- 1、创建表
create table set_table(
id int auto_increment primary key,
hobby set('music','movie','swimming')
);
-- 2、插入值
insert into set_table(hobby) values
(1),
(2),
(3),
(4),
(5),
(6),
(7),
('music'),
('movie'),
('music,movie'),
('swimming'),
('music,swimming'),
('movie,swimming'),
('music,movie,swimming');
-- 前7行以选项数字格式插入，后7行以选项字符串值的格式插入
-- 选项数字值分别对应1,2,4,8，最多64个选项
-- 查询结果，前7行和后7行的值是一样的
select * from set_table;
-- 3、查询
-- 找出SET_col包含value set成员的行。
SELECT * FROM set_table WHERE FIND_IN_SET('swimming',hobby)>0;
-- 找出set_col包含value的行，甚至是在另一个SET成员的子字符串中。
SELECT * FROM set_table WHERE hobby LIKE '%mov%';
-- 寻找包含第1个set成员的值。
SELECT * FROM set_table WHERE hobby & 1;
-- 寻找一个确切匹配的值。
SELECT * FROM set_table WHERE hobby = 'music,movie';
-- 注意，用'music,movie'跟'movie,music'查询返回的结果是不同的，（指定值时的顺序应与在列定义中所列的顺序相同。）
11.3.6 The SET Type
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.
For example, a column specified as SET('one', 'two') NOT NULL can have any of these values:
''
'one'
'two'
'one,two'A SET column can have a maximum of 64 distinct members.
Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.
Trailing spaces are automatically deleted from SET member values in the table definition when a table is created.
See String Type Storage Requirements for storage requirements for the SET type.
See Section 11.3.1, “String Data Type Syntax” for SET type syntax and length limits.
When retrieved, values stored in a SET column are displayed using the lettercase that was used in the column definition. Note that SET columns can be assigned a character set and collation. For binary or case-sensitive collations, lettercase is taken into account when assigning values to the column.
MySQL stores SET values numerically, with the low-order bit of the stored value corresponding to the first set member. If you retrieve a SET value in a numeric context, the value retrieved has bits set corresponding to the set members that make up the column value. For example, you can retrieve numeric values from a SET column like this:
mysql> SELECT set_col+0 FROM tbl_name;If a number is stored into a SET column, the bits that are set in the binary representation of the number determine the set members in the column value. For a column specified as SET('a','b','c','d'), the members have the following decimal and binary values.
SET Member | Decimal Value | Binary Value |
|---|---|---|
'a' |
1 |
0001 |
'b' |
2 |
0010 |
'c' |
4 |
0100 |
'd' |
8 |
1000 |
If you assign a value of 9 to this column, that is 1001 in binary, so the first and fourth SET value members 'a' and 'd' are selected and the resulting value is 'a,d'.
For a value containing more than one SET element, it does not matter what order the elements are listed in when you insert the value. It also does not matter how many times a given element is listed in the value. When the value is retrieved later, each element in the value appears once, with elements listed according to the order in which they were specified at table creation time. Suppose that a column is specified as SET('a','b','c','d'):
mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));If you insert the values 'a,d', 'd,a', 'a,d,d', 'a,d,a', and 'd,a,d':
mysql> INSERT INTO myset (col) VALUES
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0Then all these values appear as 'a,d' when retrieved:
mysql> SELECT col FROM myset;
+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+
5 rows in set (0.04 sec)If you set a SET column to an unsupported value, the value is ignored and a warning is issued:
mysql> INSERT INTO myset (col) VALUES ('a,d,d,s');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.04 sec)
mysql> SELECT col FROM myset;
+------+
| col |
+------+
| a,d |
| a,d |