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

一、SET类型

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

 

[sql] view plain copy

1. 属性名 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类型，因为可能有多种爱好。 

[sql] view plain copy

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

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

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

[sql] view plain copy

1. CREATE TABLE Test4(Sex ENUM('男','女'));  
2. INSERT INTO Test4 VALUES('男');  
3. INSERT INTO Test4 VALUES('爷');    --这行报错  
4. 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 列：
 

[sql] view plain copy

1. 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 列的所有可能值，可以使用：
 

[sql] view plain copy

1. 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 enum有优点。但个人觉得。。。缺点更多，客观的讲：优点主要是在建数据 库的时候就可以把一些值给规范好。缺点是。。enum不适合PHP。主要是PHP是弱类型，如：你insert into ..... set a= 1，你没法知道你是想 a= '1' 还是 a= 1（a='1'是插入值1，a=1是插入enum的第一个值，尤其php弱类型的，如果int的，很少有人在sql里加双引号。），这是PHP和mysql 在使用enum 最大的问题。所以。。安心点啦。干脆点直接tinyint。 单曲观点：     我觉得没什么优点，对数字型的enum，简直就是梦魇，boolean  tinyint(1) 0,1 status  tinyint(1)  1,2,3,4,5,6..tinyint欢淫你～～。如：audit_result enum(1,2,3)，set audit_result = 1;...容易出现膘哥所说的混淆。 简单观点：    少用，一般都是用tinyint替代。 天枫观点：    我觉得除了状态直观  没什么优点，我一般直接int，tinyint（[1or2or3]） 到底有啥区别？（后面会简单探讨下，这里面的1or2or3区别。） 中庸观点：    a=1是插入enum的第一个值，尤其php弱类型的，如果int的，很少有人在sql里加双引号，基本上是不加引号的。 竖琴螺观点：    六种情况就：tinyint(1)  -1，-2，1,2,3,4 上面各种观点重点集中在PHP这种弱类型语言对引号不重视，程序员不写容易引起插入的语句不是自己想要的结果的问题，容易出现int时没有用引号导致插入了新值而不是定的那个值： 表结构如下：  ? 1 2 3 4 5 CREATE TABLE `enum2tinyint` (   `switchs` enum( 'none' , 'success' , 'fail' , 'delete' , 'skip' , '1' ) DEFAULT NULL ,   `switch` tinyint(1) NOT NULL ,   KEY `switchs` (`switchs`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8   正常带引号插入enum：  ? 1 2 INSERT INTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES ( '1' , '1' ); 1 1 PHP的弱类型问题，特别是对int类型的情况，实践如下：  如果没有带引号插入enum字段后如下（是第一个值none）：  ? 1 2 INSERT INTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES (1, '1' ); none 1 如果没有带引号插入enum字段后如下（是第2个值success）：  ? 1 2 INSERT INTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES (2, '1' ); success 1 结论：要插入enum的值，字段必须加引号，不加引号当然是数字，数字就是key，不是value。 规劝： 1）enum是整型这样的错误很容易发生，尤其是php弱类型的，一般新来一个人，没注意enum类型，就会犯错。 2）数据库说明清楚的话，或者可选择的全是字符串的话，还没什么，但是里面有数字，难免有新手犯错，养成加引号的习惯很重要。 最终结论： 历史原因，要把enum改成tinyint程序改动太大了，用了的没必要改·～，以后新建的时候，尽量使用tinyint就好。 这种字段的重复内容过多的，索引建不建，关系不大，这种在mysql叫索引的势太低，其查询效果不太好：（英文是这么翻译的：https://justwinit.cn/post/1405/）。 ———————————————————————————————————————————————————————————————————— tinyint(1)和tinyint(4)一样的，都是-127到128或者0到256。unsigned属性全是正，和c语言unsigned int有点类似： tinyint(1)   -128 ~ 127 tinyint(1) unsigned  0 ~ 255 为何表示的最大正数比最小负数的绝对值少1呢？ 第一位是符号位，1表示负数 所以负数可以到128,正数只能到127 -128正127 如：0111 1111  正数  ，负数 1111 1111 提问： Mysql里int(1)和int(11)差别很大的，mysql的int,有个属性 ，UNSIGNED ZEROFILL  后面的ZEROFILL，就是有定义的位数不够则用零补齐对齐一下（这儿可能涉及到索引的性能罢）：int,  长度(M)=5, 属性=UNSIGNED ZEROFILL(无符号,用0来填充位数),00001,00002。 但这个tinyint呢，tinyint(1)和tinyint(4)一样的？ 看这文章后：http://www.jb51.net/article/53424.htm 我估计：mysql这块对一个字节不像int这种四个字节要进行对其前部分进行部分字节索引，如果说tinyint(1)和tinyint(4)是一样的表示范围情况下，但mysql对tinyint数字长度位数作了可设置的限定，从理论上说其存在硬盘里的字节是不一样才是合乎逻辑的，这块估计还是为了对字节进行对齐方便索引等，以提升效率为主罢，l如：ike keyword%    索引有效，如果统一对齐，索引效果会不会更好呢？我也只是猜测。 ———————————————————————————————————————————————————————————————————— 在网上找了下这个enum的mysql的大体实现如下： 一）enum的存储原理我仔细查看了下手册。是这样的: 在建立这个字段时，我们会给他规定一个范围比如enum('a','b','c'),这时mysql内部会建立一张hash结构的map表，类似:0000 -> a，0001 -> b，0002 -> c。 当我插入一条数据，此字段的值位a或b或c时，他存储在里面的不是这个字符，而是对应他的索引，也就是那个0000或0001或0002。 同样，enum在mysql手册上的说明: ENUM('value1','value2',...) 1或2个字节，取决于枚举值的个数(最多65,535个值) 除非enum的个数超过了一定数量，否则他所占的存储空间也总是1字节。 二）tinyint: 类型  字节  最小值  最大值       (带符号的/无符号的)  (带符号的/无符号的) TINYINT  1  -128  127 他的最小存储所占空间也是1字节。 最后，Enum，既然要用它，就不必要使用什么0，1，2来代替实际的字符串了。甚至中文字符串。他并不会对数据库性能进行多余开销。因为对于它来说，你使用'0','1','2'和'张三','李四','王五'数据表所占的存储空间一样。但是考虑到我们实际应用时数据需要从db服务器回传到web app，所以在网络传输时，当然还是尽可能的传输小数据比较好。所以如果很在意这些，还是不用它好了。