MySQL enum（枚举类型） set（集合类型）用法

说下写这篇日志的原因：在 MySQL 中存储状态等值时，用的 char、varchar、或 tinyint，很少使用 enum 或 set。

从个人角度感觉，enum（枚举类型）、set（集合类型）更直观（它适用于业务功能已经确定，否则后期要频繁维护表结构。），但，总用 FIND_IN_SET 查询 set 很是麻烦。网上找了好些时间，才找到方法。因此记录下。

enum 可存储 65535 项值。这有点像 HTML 中的单选框。
set 可存储 64 项值。这有点像 HTML 中的复选框。

下面将介绍常规用法：

其中 set 类型查询时，可以通过 FIND_IN_SET、LIKE 以及位运算符。首推位运算。

1、创建 enum_set_test 表用于演示

sex 姓别为枚举类型，因为只有3种情况。
my_fruit 水果喜好，集合类型，已知有很多种水果。

-- 导出  表 enum_set_test 结构
CREATE TABLE IF NOT EXISTS `enum_set_test` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户 ID',
  `real_name` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '姓名',
  `sex` enum('none','male','female') CHARACTER SET utf8 DEFAULT 'none' COMMENT '性别',
  `my_fruit` set('apple','banana','pear','orange','peach','lemon') CHARACTER SET utf8 DEFAULT '' COMMENT '喜欢的水果',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='MYSQL enum set 字段演示';

-- 正在导出表  num_set_test 的数据：14 rows

INSERT INTO `enum_set_test` (`user_id`, `real_name`, `sex`, `my_fruit`) VALUES
	(1, '张三', 'none', ''),
	(2, '李四', 'male', 'apple,banana'),
	(3, '王五', 'male', 'banana,pear,orange'),
	(4, '赵六', 'male', 'orange,peach,lemon'),
	(5, '程七', 'male', 'banana,orange,lemon'),
	(6, '孙九', 'male', 'apple,pear,orange,peach'),
	(7, '陈十', 'none', 'apple,pear,peach'),
	(8, '张三一', 'none', 'banana,orange,lemon'),
	(9, '李四一', 'female', 'apple,banana,pear'),
	(10, '王五一', 'female', 'orange,peach,lemon'),
	(11, '赵六一', 'female', ''),
	(12, '程七一', 'female', 'banana'),
	(13, '孙九一', 'female', 'orange'),
	(14, '陈十一', 'none', 'apple,banana,pear');

2、enum（枚举类型）查询和更新
可以通过序号方式查询，也可以通过值。

# 通过序号查询 姓别
SELECT * from enum_set_test where `sex` = 1; # 对应值 none
SELECT * from enum_set_test where `sex` = 2; # 对应值 male
SELECT * from enum_set_test where `sex` = 3; # 对应值 female

# 通过值查询 姓别
SELECT * from enum_set_test where `sex` = 'none'; # 对应序号 1
SELECT * from enum_set_test where `sex` = 'male'; # 对应序号 2
SELECT * from enum_set_test where `sex` = 'female'; # 对应序号 3

同理，更新、插入时，也可以通过序号和值方式的方式进行。

3、set（集合类型）通过 FIND_IN_SET 查询

# 什么都不喜欢的
SELECT * from enum_set_test where `my_fruit` = '';

# 通过 FIND_IN_SET 查询 喜欢苹果的
SELECT * from enum_set_test where FIND_IN_SET('apple', `my_fruit`);

# 通过 FIND_IN_SET 查询 喜欢苹果和香蕉的
SELECT * from enum_set_test where FIND_IN_SET('apple', `my_fruit`) AND FIND_IN_SET('banana', `my_fruit`);

# 通过 FIND_IN_SET 查询 喜欢苹果或香蕉的
SELECT * from enum_set_test where FIND_IN_SET('apple', `my_fruit`) OR FIND_IN_SET('banana', `my_fruit`);

4、set（集合类型）通过 LIKE 查询

# 通过 LIKE 查询 喜欢苹果的
SELECT * from enum_set_test where my_fruit like '%apple%';

# 通过 LIKE 查询 喜欢苹果和香蕉的
SELECT * from enum_set_test where my_fruit like '%apple%' AND my_fruit like '%banana%';

# 通过 LIKE 查询 喜欢苹果或香蕉的
SELECT * from enum_set_test where my_fruit like '%apple%' OR my_fruit like '%banana%';

5、set（集合类型）通过位运算符查询

在使用位运算符前，你需要了解值是怎么存储的，对应关系是什么。
位运算的值，是按10进制来使用的。

因此你需要算出具体值是多少。见下表：

set 值	序号	2进制=2^(序号-1)	对应10进制
apple	1	1	1
banana	2	10	2
pear	3	100	4
orange	4	1000	8
peach	5	10000	16
lemon	6	100000	32

序号，即我们看到的顺序，1-6。实际上存储的是，2进制。

换算关系是：2进制=2^(序号-1)，而将2进制再换算为10进制，便是我们需要的值。

像下面的，多项查询，如 apple+banana+lemon，则对应的值是，其对应的 10 进制相加，即1+2+32=35。

# set 存储 类同的 Linux 的数字权限
# 每一位对应 2的N-1次方即 2^(N-1)

# 通过 位运算 查询 喜欢苹果的

SELECT * from enum_set_test where my_fruit &1;

# 通过 运算符 查询 喜欢苹果和香蕉的
SELECT * from enum_set_test where my_fruit &1 and my_fruit &2;

# 通过 运算符 查询 喜欢苹果或香蕉的
SELECT * from enum_set_test where my_fruit &3;


# 通过 运算符 查询 喜欢柠檬的
SELECT * from enum_set_test where my_fruit &32;


# 通过 运算符 查询 喜欢香蕉和柠檬的
SELECT * from enum_set_test where my_fruit &2 and my_fruit &32;

# 通过 运算符 查询 喜欢苹果、香蕉或柠檬的
SELECT * from enum_set_test where my_fruit &35;

6、set（集合类型）更新

UPDATE enum_set_test set my_fruit = 'apple,lemon' where user_id=1;

相关文章：