mysql：列类型之enum、set_mysql enum

环境：

• window10
• vs2022
• .net 6
• mysql 8.0.25
• DBeaver

参考：
《mysql：11.3.5 The ENUM Type》

注意；在mysql中定义enum和set应该使用英文字母，本文使用汉字是为了方便阅读。

1. enum类型

mysql支持枚举类型，即：只能从其中选择一个值插入到数据库。
用法如下：

create table test(
	t_enum set('刘备','刘禅','张飞')
)
insert into test(t_enum) values('刘备')
1
2
3
4

mysql中的enum和c#中的枚举很相似，但它也有自己的特点：

• mysql的enum实际存储的是数字，占用1到2个字节，可表示的数字范围是0-65535；
• mysql的enum的有效值是从1开始，比如上面示例插入的’刘备’，相当于插入了1；
• mysql的enum支持字符串和数字操作：
  • 当存储字符串到mysql时，mysql自动将其翻译成数字存储；
  • 当存储数字的时候，mysql则直接插入；
  • 当从mysql读取数据时，mysql自动将其转为字符串输出；
• mysql在非严格模式下可以插入数字0或空字符串''，建议开启mysql的严格模式；
• mysql中以枚举列排序的时候，是以其数字形式排序的；

下面是一个使用示例：

create table test(
	t_enum enum('刘备','刘禅','张飞')
)

select * from test

-- 插入
insert into test(t_enum) values('刘备')
insert into test(t_enum) values('刘禅')
insert into test(t_enum) values('张飞')
insert into test(t_enum) values('关羽') -- 报错: Data truncated for column
insert into test(t_enum) values('') -- 严格模式报错:  Data truncated for column
insert into test(t_enum) values(0) -- 严格模式报错:  Data truncated for column

-- 查询
select * from test where t_enum =1 -- 和 where t_enum ='刘备' 一个意思
select * from test where t_enum ='刘备'
select * from test where t_enum >1 -- 得到刘禅、张飞
select * from test order by t_enum desc -- 按枚举倒序排列 张飞、刘禅、刘备

insert into test(t_enum) values(null) -- 依然可以插入null，因为 t_enum 并没指定 not null 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

那么在c#中应该怎么表示呢？

• 首先，在c#中定义的枚举建议从数字1开始，不要使用默认的0；
• 其次，mysql和c#中都应该使用英文的枚举项，而不是上面示例的中文（比如：上面定义枚举应该是 enum(LiuBei,LiuShan,ZhangFei)）；

看个示例：

create table test(
	t_enum enum('LiuBei','LiuShan','ZhangFei')
)
1
2
3

public enum EnumTest
{
    LiuBei = 1,
    LiuShan = 2,
    ZhangFei = 3
}
1
2
3
4
5
6

2. set类型

set类型类似于c#中的位枚举，即：可以是枚举项的0到多个组合。
mysql实际存储set还是将其存为数字，占用1、2、4、8个字节。
但和enum不同，mysql存储set的规则和bit类型相似，即：用每一个bit位表示一个枚举项的叠加。
以类型t_set set('刘备','刘禅','张飞')为列，mysql存储如下：

因为，mysql规定set类型最多占用8个字节（64个bit位），所以set类型最多表示64个枚举项的叠加状态。

set类型和enum类型有一个易混淆的地方：
严格模式下：enum类型不可插入0，不可插入’‘，但set类型在严格模式下也是可以插入0、插入’'的（可以理解为：set类型本身就是为表示叠加状态的，当它为0的时候表示没有任何枚举项叠加，这本身也是叠加状态的一种）。

下面是使用示例：

create table test(
	t_set set('刘备','刘禅','张飞')
)

select * from test

insert into test(t_set) values
	(0),(1),(2),(3),(4),(5),(6),(7);

insert into test(t_set) values
	(''),('刘备'),('刘禅'),('刘备,刘禅'),('张飞'),('刘备,张飞'),('刘禅,张飞'),('刘备,刘禅,张飞');
-- 注意: insert或更新多项的话，中间不能带空格，比如:'刘备, 张飞'会报错，而'刘备,张飞'则不会。
-- 关于空格，mysql真的是处理的不好，datetime带时区的'2022-03-29 01:00:00 +08:00' 因为带空格也会报错（+08:00前面不能有空格），而'2022-03-29 01:00:00 +08:00'则正常。


-- 精确查询
select * from test where t_set='刘备'
select * from test where t_set='刘备,刘禅'
select * from test where t_set=''

select * from test where t_set = 0
select * from test where t_set = 1
select * from test where t_set = 2
select * from test where t_set = 3

-- 模糊查询,相当于是将值转成字符串后进行like
select * from test where t_set like '%刘%'
-- 模糊查询,查询包含枚举项'刘备'的
select * from test where (t_set like '刘备,%' or t_set like '%,刘备,%' or t_set like '%,刘备' or t_set='刘备')
-- 内置函数查询
select * from test where FIND_IN_SET('刘备',t_set)>0 -- 建议写法
-- 使用位运算查询
select * from test where t_set&1 -- 包含刘备的（其中"1"最好不要使用静态数据，应该从枚举项转换过来）
select * from test where t_set&4 and t_set &1 -- 包含刘备和张飞的
select * from test where t_set&5 -- 包含刘备或张飞的（注意和上面的区别）

select * from test order by t_set -- 按照值对应的数字排序
select * from test order by cast(t_set as char) -- 按照值对应的字符串排序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

再看下面简单的写法：

create table test(
    name varchar(50),
	t_set set('海归','博士','教授')
)
insert into test values
	('小明1',1),
	('小明2',2),
	('小明4',4),
	('小明1_2',3),
	('小明1_4',5),
	('小明2_4',6),
	('小明1_2_4',7);
select * from test

select * from test where t_set & 1 = 1 -- 包含 "海归" 的
select * from test where t_set & 5 = 5 -- 同时包含 "海归","教授" 的
select * from test where t_set & 5 > 0 -- 至少包含 "海归" 或 "教授" 之一的

select * from test where t_set = 5 -- 同时具有切仅具有 "海归" 和 "教授" 的
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3. 修改数据库enum和set的定义

理想的情况下，我们在数据库的enum和set一直不变，但现实往往是一直在变。
比如，

• 设计时，set类型：set('刘备','刘禅','张飞')；
• 但新的需求后变成了：set('刘备','刘禅','张飞','诸葛亮')；
• 或者变成了：set('刘备','刘禅','赵云')；
• 甚至是：set('刘备','刘禅')；

现在我们思考：set类型修改后，数据是否还是正确的？

比如，原来是set('刘备','刘禅','张飞')，要修改成set('刘备','刘禅','赵云') ？

• 这个只要我们保证表里没有'张飞'这项数据即可（张飞这项数据即将被删除，表里有的话肯定不行），至于其他的不用管，nysql会自动映射过去。

看示例：

drop table test
create table test(
	t_set set('刘备','刘禅','张飞')
)

insert into test(t_set) values
	('刘备'),('刘禅,刘备'),('张飞');

select * from test
select * from test where t_set=1 -- 根据set定义，此时 '刘备' 对应数字 1

alter table test modify t_set set('刘备','刘禅','赵云') -- 因为表中已有'张飞'数据,报错: Data truncated for column 't_set' at row 3

delete from test where t_set='张飞'
select * from test

alter table test modify t_set set('赵云','刘备','刘禅')

insert into test(t_set) values('刘备,赵云');

select * from test
select * from test where t_set=2 -- 根据set定义，此时 '刘备' 对应数字 2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

注意：当我们修改定义后，数据会自动进行调整，我们不需要关心。
我们需要关心的是，如果我们使用 where t_set&1 -- 查询包含刘备的这种形式查询的话就危险了，因为重新定义后项对应的数字可能会发生更改，所以我们应该使用引用定义的项，而不是数字。
当然，如果我们在程序中动态生成对应数字的话，就不用考虑了。

补充： enum和set的定义修改是一样的，只要我们保证现有的数据中没有要删除的定义就可以放心修改了，实验sql如下：

drop table test
create table test(
	t_enum enum('刘备','刘禅','张飞')
)

-- 插入
insert into test(t_enum) values('刘备'),('刘禅'),('张飞')

select * from test
select * from test where t_enum=1 -- 此时,'刘备'项对应数字 1

alter table test modify t_enum enum('赵云','刘备','刘禅') -- 因为表中已有 '张飞'数据，所以报错：Data truncated for column 't_enum' at row 2
 

delete from test where t_enum='张飞'

select * from test

alter table test modify t_enum enum('赵云','刘备','刘禅')

insert into test(t_enum) values('赵云')

select * from test
select * from test where t_enum =1 -- 此时,'赵云'项对应数字 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

4. c#中应该怎么使用？

那么，在c#中应该怎么表示呢？
直接看实例：

[Flags]
public enum EnumTest
{
    LiuBei = 1,
    LiuShan = 2,
    ZhangFei = 4
}
1
2
3
4
5
6
7