enum类型 mysql set类型 mysql中enum的用法

转载

编程小天匠 2023-07-11 12:56:52

文章标签 enum类型 mysql set类型数据库 database MySQL mysql 文章分类 MySQL 数据库

`ENUM` 枚举类型

ENUM适合于只能在一组固定值中选一个的场景，比如性别只能为男或者女。
ENUM的优势在于：

只能在固定值中选择，可以在数据库层面限制非法值。
数据的存储用数字来存储，占用空间少。

但是它的使用有很多需要我们注意的地方，一不小心你就会得到错误的结果。

使用`ENUM`枚举类型

mysql> create table test (name varchar(40), sex enum('male', 'female') );
mysql> insert into test (name, sex) values('a', 'male'), ('b', 'female'), ('c', 'male');
mysql> select * from test;
+------+--------+
| name | sex    |
+------+--------+
| a    | male   |
| b    | female |
| c    | male   |
+------+--------+
3 rows in set (0.00 sec)

创建枚举类型时，我们使用关键字enum，同时跟着一组可枚举值列表，这些可枚举值必须使用字符串的格式，否则会报错。如果插入值的大小写不匹配，会自动转换成枚举值。

`ENUM`类型数据存储的实际值是索引值

我们所有枚举值都是按照枚举值列表中的索引值进行存储的，如上面的ENUM('male', 'female')的sex字段所有值为：

字面值	存储值
NULL	NULL
''	0
'male'	1
'female'	2

因此如果有1000条记录都存储为male，我们可能认为数据库存储了4000个字符，其实只存储了1000个1字符。而在查询的时候又会将这个编码过的数字转为实际的值。

我们可以用两个例子测试下：

mysql> select * from test where sex=1;
+------+------+
| name | sex  |
+------+------+
| a    | male |
| c    | male |
+------+------+
2 rows in set (0.00 sec)

mysql> select name, sex+0 from test;
+------+-------+
| name | sex+0 |
+------+-------+
| a    |     1 |
| b    |     2 |
| c    |     1 |
+------+-------+
3 rows in set (0.00 sec)

这种存储和查询的方式会导致一些处理数字的函数，也会使用存储的值来进行计算，如SUM()和AVG()：

mysql> select name, avg(sex) from test;
+------+--------------------+
| name | avg(sex)           |
+------+--------------------+
| a    | 1.3333333333333333 |
+------+--------------------+
1 row in set (0.00 sec)

读写时不要使用数字

由于上面介绍的用索引值存储的特性，我们不要用枚举类型来存储数字格式的列，否则会引起很大的混淆，如：

mysql> create table test2 (numbers enum('0', '1', '2'));
Query OK, 0 rows affected (0.04 sec)

# 此时2被当做索引值，因此是'1'；'2'就是'2'；'3'因为不是合法值，会用索引值尝试，因此是'2'
mysql> insert into test2 (numbers) values (2), ('2'), ('3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test2;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+
3 rows in set (0.00 sec)

枚举类型的默认值

即便一列被设定为枚举类型，但依然有额外两种值为合法值：NULL和''。

当我们插入一个非法值时，在宽松模式下，会插入一个普通的空字符''，其值为0。而在严格模式下会报错。

当该字段设定为允许为空时，NULL字段可以被正常插入。当不允许为空时，如果你不填值，会使用默认值：枚举值的第一个，如上面的male。

除了设置为严格模式，否则没有合适的办法让一列数据必须插入合法枚举值。使用默认值很多情况下不能满足需求。

枚举类型的排序

常规使用order by进行排序时，会按照字母的文本顺序。但枚举类型由于存储为索引值，因此会按照索引值进行排序：NULL < '' = 0 < 1 < 2。

如果希望按照文本类型进行排序，可以使用：

order by cast(col as char)
或者
order by concat(col)

枚举值声明的限制

创建数据类型时，枚举值不允许为表达式，如：

mysql> create table test (name varchar(40), sex enum('male', concat('fem', 'ale') );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'concat('fem', 'ale') )' at line 1