MySQL 中的 SET 与 ENUM 类型使用详解

MySQL 中的 SET 与 ENUM 类型使用详解

一、SET类型

在创建表时，就指定SET类型的取值范围。

 

属性名 SET('值1','值2','值3'...,'值n')

 

其中，“属性名”参数指字段的名称；“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是有64个元素构成的组合，根据成员的不同，存储上也有所不同：

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

同ENUM类型一样，列表中的每个值都有一个顺序排列的编号。MySQL中存入的是这个编号，而不是列表中的值。

插入记录时，SET字段中的元素顺序无关紧要。存入MySQL数据库后，数据库系统会自动按照定义时的顺序显示。如果插入的成员中有重复，则只存储一次。

 

二、ENUM类型

ENUM类型(枚举类型)，与C#的概念一样，在定义时指定取值范围。

 

ENUM类型的值范围需要在创建表时通过枚举方式显式指定，对1～255个成员的枚举需要1个字节存储；对于256～65535个成员，需要2个字节存储。最多可以有65535个成员，而SET类型最多只能包含64个成员。两者的取值只能在成员列表中选取。ENUM类型只能从成员中选择一个，而SET类型可以选择多个。

 

因此，对于多个值中选取一个的，可以选择ENUM类型。例如，“性别”字段就可以定义成ENUM类型，因为只能在“男”和“女”中选其中一个。对于可以选取多个值的字段，可以选择SET类型。例如，“爱好”字段就可以选择SET类型，因为可能有多种爱好。 

属性名 ENUM('值1','值2','值3'...'值n')

◆其中，属性名参数指字段的名称；“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。ENUM类型的值只能取列表中的一个元素。其取值列表中最多能有65535个值。列表中的每个值都有一个顺序排列的编号，MySQL中存入的是这个编号，而不是列表中的值。

◆ENUM 有 NOT NULL 属性，其默认值为取值列表的第一个元素；
◆ENUM 无 NOT NULL，则ENUM类型将允许插入NULL，并且NULL为默认值；

CREATE TABLE Test4(Sex ENUM('男','女'));
INSERT INTO Test4 VALUES('男');
INSERT INTO Test4 VALUES('爷');    --这行报错
SELECT * FROM Test4;

ENUM 是一个字符串对象，其值通常选自一个允许值列表中，该列表在表创建时的列规格说明中被明确地列举。

 

在下列某些情况下，值也可以是空串('') 或 NULL：

◆如果将一个无效值插入一个 ENUM (即，一个不在允许值列表中的字符串)，空字符串将作为一个特殊的错误值被插入。事实上，这个字符串有别于一个'普通的'空字符串，因为这个字符串有个数字索引值为 0。稍后有更详细描述。
◆如果一个 ENUM 被声明为 NULL，NULL 也是该列的一个合法值，并且该列的缺省值也将为 NULL 。如果一个 ENUM 被声明为 NOT NULL，该列的缺省值将是该列表所允许值的第一个成员。

 

每个枚举值均有一个索引值：

◆在列说明中列表值所允许的成员值被从 1 开始编号。
◆空字符串错误值的索引值为 0。这就意味着，你可以使用下面所示的 SELECT 语句找出被赋于无效 ENUM值的记录行。
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
◆NULL 值的索引值为 NULL。

例如，指定为 ENUM('one', 'two', 'three') 的一个列，可以有下面所显示的任一值。每个值的索引值也如下所示：

 

值	索引值
NULL	NULL
''	0
'one'	1
'two'	2
'three'	3

 

换个枚举最大可以有 65535 个成员值。

从 MySQL 3.23.51 开始，当表被创建时，ENUM 值尾部的空格将会自动删除。

当为一个 ENUM 列赋值时，字母的大小写是无关紧要的。然而，以后从列中检索出来的值的大小写却是匹配于创建表时所指定的允许值。

 

如果在一个数字语境中检索一个ENUM，列值的索引值将被返回。例如，你可以像这样使用数字值检索一个 ENUM 列：
 

mysql> SELECT enum_col+0 FROM tbl_name;

如果将一个数字存储到一个 ENUM 中，数字被当作为一个索引值，并且存储的值是该索引值所对应的枚举成员。(但是，这在 LOAD DATA 将不能工作，因为它视所有的输入均为字符串。) 在一个 ENUM 字符串中存储数字是不明智的，因为它可能会打乱思维。

ENUM 值依照列规格说明中的列表顺序进行排序。(换句话说，ENUM 值依照它们的索引号排序。)举例来说，对于 ENUM('a', 'b') 'a' 排在 'b' 后，但是对于 ENUM('b', 'a') ， 'b' 却排在 'a' 之前。空字符串排在非空字符串前，NULL 值排在其它所有的枚举值前。为了防止意想不到的结果，建议依照字母的顺序定义 ENUM 列表。也可以通过使用 GROUP BY CONCAT(col) 来确定该以字母顺序排序而不是以索引值。

如果希望得到一个 ENUM 列的所有可能值，可以使用：
 

SHOW COLUMNS FROM table_name LIKE enum_colum;

三、SET 与 ENUM 类型的查询修改操作

SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for setenum -- ---------------------------- DROP TABLE IF EXISTS `setenum`; CREATE TABLE `setenum` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `settype` set('we','周','李','孙','钱','赵') DEFAULT NULL,   `enumtype` enum('ZZZ','南海','长江','黄河') DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of setenum -- ---------------------------- INSERT INTO `setenum` VALUES ('1', 'we,周,钱', '南海'); INSERT INTO `setenum` VALUES ('2', '钱,赵', '黄河'); INSERT INTO `setenum` VALUES ('3', 'we,赵', '南海'); INSERT INTO `setenum` VALUES ('4', '李,孙,钱', '长江'); set('we','周','李','孙','钱','赵')=111111=63 enum('ZZZ','南海','长江','黄河')=100=4 如下表所示： SET类型：低位（右） → 高位（左） we 周 李 孙 钱 赵 1 1 1 1 1 1 从右到左排：111111=63             we 周 李 孙 钱 赵 1 1 0 0 1 0 从右到左排：010011=19             we 周 李 孙 钱 赵 0 0 0 0 1 1 从右到左排：110000=48             we 周 李 孙 钱 赵 1 0 0 0 0 1 从右到左排：100001=33             we 周 李 孙 钱 赵 0 0 1 1 1 0 从右到左排：011100=28 ENUM类型 ZZZ 南海 长江 黄河 1 2 3 4         ZZZ 南海 长江 黄河 1 2 3 4 黄河=4=100         ZZZ 南海 长江 黄河 1 2 3 4 长江=3=11         ZZZ 南海 长江 黄河 1 2 3 4 南海=2=10         ZZZ 南海 长江 黄河 1 2 3 4 ZZZ=1=1 mysql> select * from setenum; +----+----------+----------+ | id | settype  | enumtype | +----+----------+----------+ |  1 | we,周,钱 | 南海     | |  2 | 钱,赵    | 黄河     | |  3 | we,赵    | 南海     | |  4 | 李,孙,钱 | 长江     | +----+----------+----------+ 4 rows in set mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum; +----------+-----------+----------------+----------+------------+-----------------+ | settype  | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +----------+-----------+----------------+----------+------------+-----------------+ | we,周,钱 |        19 | 10011          | 南海     |          2 | 10              | | 钱,赵    |        48 | 110000         | 黄河     |          4 | 100             | | we,赵    |        33 | 100001         | 南海     |          2 | 10              | | 李,孙,钱 |        28 | 11100          | 长江     |          3 | 11              | +----------+-----------+----------------+----------+------------+-----------------+ 4 rows in set mysql> select * from setenum where settype=33; +----+---------+----------+ | id | settype | enumtype | +----+---------+----------+ |  3 | we,赵   | 南海     | +----+---------+----------+ 1 row in set mysql> select * from setenum where enumtype=2; +----+----------+----------+ | id | settype  | enumtype | +----+----------+----------+ |  1 | we,周,钱 | 南海     | |  3 | we,赵    | 南海     | +----+----------+----------+ 2 rows in set --不支持二进制查询 mysql> select * from setenum where settype=b'010011'; Empty set mysql> select * from setenum where settype=b'10011'; Empty set mysql> select * from setenum where enumtype=b'100'; Empty set mysql> SELECT * FROM setenum WHERE settype LIKE '%赵%'; +----+---------+----------+ | id | settype | enumtype | +----+---------+----------+ |  2 | 钱,赵   | 黄河     | |  3 | we,赵   | 南海     | +----+---------+----------+ 2 rows in set --与FIND_IN_SET函数同 mysql> SELECT * FROM setenum WHERE FIND_IN_SET('赵',settype)>0; +----+---------+----------+ | id | settype | enumtype | +----+---------+----------+ |  2 | 钱,赵   | 黄河     | |  3 | we,赵   | 南海     | +----+---------+----------+ 2 rows in set --当查询只是集合某个值的一部分时，与FIND_IN_SET函数不同 mysql> SELECT * FROM setenum WHERE FIND_IN_SET('e',settype)>0; Empty set mysql> SELECT * FROM setenum WHERE settype LIKE '%e%'; +----+----------+----------+ | id | settype  | enumtype | +----+----------+----------+ |  1 | we,周,钱 | 南海     | |  3 | we,赵    | 南海     | +----+----------+----------+ 2 rows in set mysql> SELECT * FROM setenum WHERE settype LIKE '赵'; Empty set mysql> SELECT * FROM setenum WHERE settype = '赵'; Empty set mysql> SELECT * FROM setenum WHERE settype LIKE 'we,赵'; +----+---------+----------+ | id | settype | enumtype | +----+---------+----------+ |  3 | we,赵   | 南海     | +----+---------+----------+ 1 row in set mysql> SELECT * FROM setenum WHERE settype = 'we,赵'; +----+---------+----------+ | id | settype | enumtype | +----+---------+----------+ |  3 | we,赵   | 南海     | +----+---------+----------+ 1 row in set --如果把集合的顺序改一下，照样无效 mysql> SELECT * FROM setenum WHERE settype LIKE '赵,we'; Empty set mysql> SELECT * FROM setenum WHERE settype = '赵,we'; Empty set mysql> SELECT * FROM setenum WHERE enumtype LIKE '%海%';   +----+----------+----------+ | id | settype  | enumtype | +----+----------+----------+ |  1 | we,周,钱 | 南海     | |  3 | we,赵    | 南海     | +----+----------+----------+ 2 rows in set mysql> SELECT * FROM setenum WHERE enumtype = '南海'; +----+----------+----------+ | id | settype  | enumtype | +----+----------+----------+ |  1 | we,周,钱 | 南海     | |  3 | we,赵    | 南海     | +----+----------+----------+ 2 rows in set mysql> SELECT * FROM setenum WHERE enumtype = '海'; Empty set --------------------UPDATE 语法-------------------- set('we','周','李','孙','钱','赵')=111111=63 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +----------+-----------+----------------+----------+------------+-----------------+ | settype  | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +----------+-----------+----------------+----------+------------+-----------------+ | we,周,钱 |        19 | 10011          | 南海     |          2 | 10              | +----------+-----------+----------------+----------+------------+-----------------+ 1 row in set mysql> update setenum set settype = 2 where id=1; Query OK, 1 row affected Rows matched: 1  Changed: 1  Warnings: 0 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +---------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +---------+-----------+----------------+----------+------------+-----------------+ | 周      |         2 | 10             | 南海     |          2 | 10              | +---------+-----------+----------------+----------+------------+-----------------+ 1 row in set --修改settype让其'we'、'周'、'李' 成员为真 mysql> update setenum set settype =settype|1|4|6 where id=1; Query OK, 1 row affected Rows matched: 1  Changed: 1  Warnings: 0 --|1|4|6 表示 二进制的OR运算 1 or 100 or 110 = 111 = 7 --实际与 1|6 结果是一样的：1 or 110 = 111 = 7 we 周 李 孙 钱 赵 1 1 1 0 0 0 从右到左排：000111=7 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +----------+-----------+----------------+----------+------------+-----------------+ | settype  | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +----------+-----------+----------------+----------+------------+-----------------+ | we,周,李 |         7 | 111            | 南海     |          2 | 10              | +----------+-----------+----------------+----------+------------+-----------------+ 1 row in set --实际与 1|6 结果是一样的：1 or 110 = 111 = 7 mysql> update setenum set settype =settype|1|6 where id=1; Query OK, 0 rows affected Rows matched: 1  Changed: 0  Warnings: 0 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +----------+-----------+----------------+----------+------------+-----------------+ | settype  | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +----------+-----------+----------------+----------+------------+-----------------+ | we,周,李 |         7 | 111            | 南海     |          2 | 10              | +----------+-----------+----------------+----------+------------+-----------------+ 1 row in set --settype|1|6 的settype 可以省略，结果一样 mysql> update setenum set settype = 1|6 where id=1; Query OK, 1 row affected Rows matched: 1  Changed: 1  Warnings: 0 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +----------+-----------+----------------+----------+------------+-----------------+ | settype  | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +----------+-----------+----------------+----------+------------+-----------------+ | we,周,李 |         7 | 111            | 南海     |          2 | 10              | +----------+-----------+----------------+----------+------------+-----------------+ 1 row in set -- &表示与运算，1 and 110 = 0，注意0与NULL不同 mysql> update setenum set settype = 1 & 6 where id=1; Query OK, 1 row affected Rows matched: 1  Changed: 1  Warnings: 0 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +---------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +---------+-----------+----------------+----------+------------+-----------------+ |         |         0 | 0              | 南海     |          2 | 10              | +---------+-----------+----------------+----------+------------+-----------------+ 1 row in set -- &表示与运算，~表示反运算，6=110，~6=001，1 and 001 = 1 mysql> update setenum set settype = null where id=1; Query OK, 1 row affected Rows matched: 1  Changed: 1  Warnings: 0 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +---------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +---------+-----------+----------------+----------+------------+-----------------+ | NULL    | NULL      | NULL           | 南海     |          2 | 10              | +---------+-----------+----------------+----------+------------+-----------------+ 1 row in set mysql> update setenum set settype = 1 & ~6 where id=1; Query OK, 1 row affected Rows matched: 1  Changed: 1  Warnings: 0 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +---------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +---------+-----------+----------------+----------+------------+-----------------+ | we      |         1 | 1              | 南海     |          2 | 10              | +---------+-----------+----------------+----------+------------+-----------------+ 1 row in set -- 5 and ~1 = 101 and ~1 = 101 and 111110 = 100 = 4 mysql> update setenum set settype = null where id=1; Query OK, 1 row affected Rows matched: 1  Changed: 1  Warnings: 0 mysql> update setenum set settype = 5 & ~1 where id=1; Query OK, 1 row affected Rows matched: 1  Changed: 1  Warnings: 0 we 周 李 孙 钱 赵 0 0 1 0 0 0 从右到左排：000100=4 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +---------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +---------+-----------+----------------+----------+------------+-----------------+ | 李      |         4 | 100            | 南海     |          2 | 10              | +---------+-----------+----------------+----------+------------+-----------------+ 1 row in set