字节2面：MySQL枚举类型该如何定义？ enum？tinyint？char？varchar？

发表于 更新于 分类于 阅读次数： 本文字数： 6.8k 阅读时长 ≈ 6 分钟

你好，我是猿java

最近，看到字节的一道 2面题：MySQL枚举类型该如何定义？enum？tinyint？char？varchar？该如何选择？今天一起来分析它。

enum

enum简介

enum 是一个字符串对象，其值在创建表时显式枚举，enum从 MySQL 3.23.3 版本开始支持，和 Java语言中的 enum语义比较相似，主要是对值做一个限定约束作用。

下面以一个示例来说明 enum类型，有一张衬衣表，size字段定义成 enum类型，枚举值包含 ‘x-small’, ‘small’, ‘medium’, ‘large’, ‘x-large’ 5种，然后插入 3条记录：

1
2
3
4
5
6
7
8
9
10
-- 创建表
CREATE TABLE shirts_enum (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
-- 增加 3条记录
INSERT INTO shirts_enum (name, size) VALUES
('dress shirt','large'),
('t-shirt','medium'),
('polo shirt','small');

接着，我们可以通过 size = ‘medium’ 来查询数据：

1
2
3
4
5
6
7
-- 查询 size='medium' 的记录
SELECT name, size FROM shirts_enum WHERE size = 'medium';
+---------+--------+
| name    | size   |
+---------+--------+
| t-shirt | medium |
+---------+--------+

实际上，MySQL会为 enum类型的每个枚举值创建一个 index索引，并且 index值从 1开始。

是不是很奇怪？按常识，计算机的 index一般都是从 0开始，为什么这里是从 1开始呢？

这是因为 MySQL需要处理两种特殊的情况： NULL 和 ‘’, NULL 对应的 index是 NULL， ‘’ 对应的 index是 0。 因此上述枚举值与 index对应关系如下表：

枚举值 index
NULL NULL
‘’ 0
x-small 1
small 2
medium 3
large 4
x-large 5

所以，查询 size=’medium’ 也等价于 size = 3，如下 SQL：

1
2
3
4
5
6
7
-- 查询 size=3 的记录
SELECT name, size FROM shirts_enum WHERE size = 3;
+---------+--------+
| name    | size   |
+---------+--------+
| t-shirt | medium |
+---------+--------+

需要说明：默认情况下 MySQL开启了严格模式，即如果插入的值不在 enum定义的范围内，则抛错，如果需要插入 ‘’或者 NULL，需要关闭严格模式，错误信息如下：

1
2
3
-- 插入一条枚举中不存在值为 xx-large的记录时，会报错
INSERT INTO shirts_enum (name, size) VALUES ('x-shirt','xx-large');
ERROR 1265 (01000): Data truncated for column 'size' at row 1

enum优缺点

总结下 enum类型的优缺点

优点

  • 可读性强：使用 enum类型可以直观地看出列的取值范围，提高了数据的可读性。
  • 数据约束：enum类型可以限制取值范围，防止非法数据的插入。

缺点

  • 可扩展性差：一旦定义了 enum类型，如果需要修改枚举值，可能需要修改表结构。

enum使用建议

在实际业务中，如果枚举值固定不变，比如，性别（男/女），星期，月份，扑克牌的花色，那么选择 enum是一个不错的选择。

如果枚举值会随着业务的发展发生变化或者不确定未来会不会发生变化，那么，请勿将字段定义成 enum类型，特别是数据量比较大或者有分库分表的场景，因为一旦需要修改表的 schema将会是一个噩梦。

另外，MySQL官方不建议 enum类型的字段值使用数字，因为数据库可能会将枚举的数字值和索引值混淆。详情参考：https://dev.mysql.com/doc/refman/8.0/en/enum.html

tinyint

tinyint简介

在 MySQL中，tinyint 是一种小范围的整数值，占用一个字节（8-bit），最大值是 2^8=256，有符号 tinyint范围是 -128～127，无符号 tinyint范围是 0～255。

tinyint(M)中 M是什么？

在创建表时，我们常常会纠结 tinyint(M)中的 M到底该设置多大？

其实，tinyint(M)中的 M和存储的值没有任何关系，只是在 zerofill模式下和显示的宽度有关系，为了更好的说明这点，这里以 2个示例来说明：

示例一：tinyint字段不设置 Zerofill

首先，定义一个 tinyint_test表，每个tinyint_ 开头的字段都不设置为 Zerofill，表结构如下图：

img.png

然后，给同一条记录中的每个字段添加相同的值，查询后发现不管 M设置多少，查出来的值都相同，如下图：

img.png

示例二：tinyint字段设置 Zerofill

首先，定义一个 tinyint_test_2表，每个tinyint_ 开头的字段都设置为 Zerofill，表结构如下图：

img.png

然后，给同一条记录中的每个字段添加相同的值，这个时候差异就体现出来了，当数字的宽度不足 M时，会在数字的左边填充0，如下图：

img.png

通过上述示例可以看出：tinyint(M)中的 M其实并不会影响最终存储的值，影响的是在 Zerofill模式下的数据展示的宽度。

有个特殊的情况需要说明：在 java中，数据库的 tinyint(1)会自动转换为 java中的 boolean类型，tinyint(4)会正常转换为 int类型，所以实际开发中一般建议设置成 tinyint(4)。

了解了 tinyint以及 tinyint(M)中 M的具体含义，我们依然使用上面的例子，把 size定义成 tinyint(4)类型，然后插入 3条数据，如下 SQL语句：

1
2
3
4
5
6
7
8
9
10
-- 创建表
CREATE TABLE shirts_tinyint (
    name VARCHAR(40),
    size tinyint(4) -- 1:'x-small', 2:'small', 3:'medium', 4:'large', 5:'x-large'
);
-- 增加 3条记录
INSERT INTO shirts_tinyint (name, size) VALUES
('dress shirt',4),
('t-shirt',3),
('polo shirt',2);

接着，我们可以通过 size = 3 来查询数据：

1
2
3
4
5
6
7
-- 查询 size= 3，即 'medium' 的记录
SELECT name, size FROM shirts_tinyint WHERE size = 3;
+---------+--------+
| name    | size   |
+---------+--------+
| t-shirt |  3     |
+---------+--------+

tinyint 其实是对业务枚举值的一种抽象，tinyint字段的数字可以赋予任何业务上的枚举值，比如

1
2
-- 衬衫表的 size字段： 1:'x-small', 2:'small', 3:'medium', 4:'large', 5:'x-large'
-- 订单表的 status字段 0:NEW, 1:WAIT_PAY, 2:PAID ...

tinyint 优缺点

总结下 tinyint类型的优缺点

优点

  • 扩展性强：使用 tinyint类型可以在不修改表 schema的情况下，灵活的增加新的枚举值
  • 性能强：tinyint是数字类型，在 MySQL中性能较高

缺点

  • 可读性差：tinyint是数字，所以很多时候对于它表示的真实语义无法一目了然，特别是在业务的迭代过程中新增了枚举值，但是没有更新表的字段备注，
    而这种情况往往是实际开发中经常遇到的，这是因为增加枚举值只需要在业务代码里操作，而修改字段备注需要修改表的 schema。

tinyint使用建议

对于 tinyint表示枚举值时，一般不建议使用连续的数字，而是会错开，主要是方便在两个枚举值中间添加新值，
比如订单状态，0:NEW，10:WAIT_PAY，20:PAID，如果需要在 10 和 20中间添加一个 Paying 状态，可以直接将 Paying 定义成 14，这样整体看起来比较和谐。

char

在 MySQL中，char是一种固定长度的类型，长度可以是 0～255之间的任意值，char 类型的声明长度表示可以存储的最大字符数。例如，char(10)代表最多可以容纳 10个字符。

如果字符实际长度小于申明长度（char(M)中的 M），MySQL 会使用空格右填充剩余空间。比如，char(4)，当插入’abc’时，还有 1个字节空间会被填充成空格。

我们还是使用上面的例子，用 char类型来定义 size，因为 char是固定长度，所以要保证枚举中最长的字符串可以插入，这里 size枚举值中的最大长度是 7，因此 char的长度可以设置为 7，如下 SQL语句：

1
2
3
4
5
-- 创建表
CREATE TABLE shirts (
   name VARCHAR(40),
   size CHAR(7) -- size对应的枚举值：'x-small', 'small', 'medium', 'large', 'x-large'
);

使用 char会存在 2个问题：

  • 对于插入的字符长度小于 char(M)中的 M值，多余的空间会被右填充成空格，浪费了空间
  • 如果后期需要增加一个长度大于 M的枚举值，就必须修改表的 schema，否则会报错，如下：
    1
    2
    3
    --这里只是为了更好的展示超出char 范围会报错，xxx不代表实际意义
    MySQL> INSERT INTO shirts (name, size) VALUES ('xxx shirt','xxx-small');
    ERROR 1406 (22001): Data too long for column 'size' at row 1

因此，char(M)类型适合枚举中所有值的长度都小于等于 M的场景，比如性别（F/M），成绩等级（A/B/C/D/E/F），
如果每个枚举值的长度不一样，将会有部分的空间浪费。

varchar

在 MySQL中，varchar是一种存储可变长度字符的类型，长度可以是 0～65535 中任意一个值。

这里的可变长度怎么理解？我们还是使用上面的例子，用 varchar类型来定义 size，并且 varchar的长度设置成 5，如下 sql语句：

1
2
3
4
5
-- 创建表
CREATE TABLE shirts_varchar (
   name VARCHAR(40),
   size VARCHAR(5) -- size对应的枚举值：'x-small', 'small', 'medium', 'large', 'x-large'
);

当我们插入 size=’x-small’时，会报“数据过长”的错误，如下：

1
2
MySQL> INSERT INTO shirts_varchar (name, size) VALUES ('x shirt','x-small');
ERROR 1406 (22001): Data too long for column 'size' at row 1

这里的可变长度对于实际长度超过 varchar(M)中 M的值时不生效？那么可变长到底该如何理解？

在非严格模式下，官方对 char和 varchar进行的对比图：

img.png

对于 char(M) 和 varchar(N)，如果实际插入值的长度 x < M，char会对插入值进行右填空格，而 x < N时，varchar是直接使用实际值长度，
对于 x > M 或者 x > N，char 和 varchar都是采用截断的方式，但是对于严格模式，将抛出异常。

需要说明：varchar会增加 1-2个字节的前缀。

通过上面的对比可以发现，varchar似乎比 char更节省空间，所以，对于枚举中各个值的长度不相同的情况，使用 varchar比 char更节省空间。

varchar 优缺点

总结下 varchar类型的优缺点

优点

  • 扩展性强：使用 varchar(M)类型可以设置一个合理的且比较大的 M，varchar最终使用实际长度空间

缺点

  • varchar会增加 1-2个字节的前缀，一定意义上浪费了空间

关于 tinyint和 varchar性能争议

网上很多帖子都强调 tinyint比 varchar的性能高，如果抛开业务，单纯从性能来说，tinyint确实会比 varchar高效一些，但是，抛开业务仅仅谈性能是毫无意义的。

这里以电商订单状态为例：

一般查看订单的场景有：

  • 用户查看订单，如下 SQL语句：
1
select * from order where user_id = ? and status = ?

用户只能查看自己的订单，因此，需要先根据 user_id进行订单筛选，然后对结果通过 status=？条件二次筛选订单，所以，status定义成 tinyint 和 varchar，性能其实没有多大的差异。

  • 商家查看订单，如下 SQL语句：
1
select * from order where tenant_id = ? and status = ?

商家只能查看下给自己的订单，因此，需要先根据 tenant_id进行订单筛选，然后对结果通过 status=？条件二次筛选订单，所以，status定义成 tinyint 和 varchar，性能也不会有很大的差异。

  • 客服查看订单，如下 SQL语句：

    1
    select * from order where order_id = ?

    客服在处理问题时，一般需要用户提供订单号，所以客服查询订单都是按照 order_id进行精确查询，几乎不涉及到 status的筛选。

  • 平台查看订单，比如查看当前所有未支付的订单，如下 SQL语句：

    1
    select * from order where status = ?

对于这种统计查询，如果是单表，更多建议在离线表中操作，这样不会影响主业务；对于分库分表的场景，一般会把所有表的数据同步到 hbase这样的NoSQL数据库中（数仓），然后进行各种查询操作。

所以，结合实际业务可以发现，一般查询操作都是通过某种角色出发，比如 user_id，tenant_id，这样，首先会通过 xxx_id过滤数据，然后在结果里面再根据 status筛选，所以，status枚举定义成 tinyint还是 varchar类型，在一定意义上相差不大。

总结

MySQL枚举类型该如何定义？enum，tinyint，char，varchar 4种类型，到底该怎么选？

一条原则：选择一定要结合实际业务

  1. enum类型，适合枚举值个数确定，以后不会添加新值的场景，比如：性别，星期，月份，扑克牌的花色，如果后期要修改枚举值就得更改表结构。
  2. tinyint类型，没有使用限制，性能较高但牺牲了一定的可读性，因此，如果使用 tinyint定义枚举类型，要特别注意字段的备注以及沟通带来的成本。
  3. char类型，适合枚举值长度确定的场景，因为 char(M)的 M一旦确认，如果后期要修改 M就得更改表结构。
  4. varchar类型，没有使用限制，保留了枚举值的可读性，减少沟通成本，相对 tinyint类型会有部分的性能损耗。
  5. 如果实在不知道如何选择，那就在 tinyint 和 varchar中选一个。

对于，tinyint 和 varchar，在经历的业务里，不管是单表还是分库分表的场景都有使用，对于它们的性能差异和可读性带来的沟通成本，
很多时候都是一个权衡的结果，或者部门内部商量的结果，并没有绝对的好，也没有绝对的差。

交流学习

如果文章存在缺点和错误，欢迎批评指正。更多干货和面试经，关注公众号：猿java。

drawing