mysql set类型筛选_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)

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