MySQL编程进阶之：`ENUM`与`SET`数据类型的优缺点：在性能和可维护性上的权衡。

各位听众，大家好！我是老码农，今天咱们聊聊MySQL里两个有点意思的数据类型：ENUM 和 SET。它们就像数据库里的“特种部队”，用得好了能提升效率，用得不好可能埋雷。咱们一起扒一扒它们的好与坏，看看怎么才能用好这两把“双刃剑”。

开场：来点儿段子热热身

话说，当年老码农刚入行的时候，有个老前辈语重心长地跟我说：“小伙子，ENUM 和 SET 啊，就像恋爱，初见美好，用久了嘛…嘿嘿嘿。” 当时的我还不明白，直到后来踩了坑，才深刻体会到这句话的真谛。

第一部分：ENUM – 单选题的艺术

ENUM，顾名思义，就是枚举类型。你可以把它想象成一个单选题，选项是事先定义好的，字段的值只能从这些选项里挑一个。

1.1 ENUM 的优点：

• 存储空间小： ENUM 在存储时，实际上存储的是选项对应的数字索引，而不是字符串本身。如果你的枚举选项不多，MySQL会用1个字节甚至更小的空间来存储，非常节省空间。
  • 例如，如果你的枚举选项少于256个，MySQL会用1个字节存储；少于65536个，就用2个字节。
• 数据校验： ENUM 强制字段的值必须是预定义的选项之一，可以防止脏数据的产生。
• 可读性好： 虽然存储的是数字索引，但在查询结果中，MySQL会自动把索引转换为字符串显示，方便我们阅读。

1.2 ENUM 的缺点：

• 修改困难： 修改 ENUM 的选项需要修改表结构，而且可能会导致数据问题。如果你的选项数量很多，或者需要频繁修改选项，ENUM 就不是一个好选择。
• 字符串比较： 虽然显示的是字符串，但内部存储的是数字索引，所以进行字符串比较时，MySQL会先将索引转换为字符串，然后再进行比较，效率相对较低。
• 排序问题： ENUM 的排序是按照选项在定义时的顺序进行的，而不是按照字母顺序或者其他规则。这可能会导致一些意想不到的结果。

1.3 ENUM 的使用示例：

假设我们要存储用户的性别，可以选择 ENUM 类型：

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  gender ENUM('male', 'female', 'other') DEFAULT 'other'
);

INSERT INTO users (username, gender) VALUES
('张三', 'male'),
('李四', 'female'),
('王五', 'other');

SELECT * FROM users;

在这个例子中，gender 字段的值只能是 ‘male’、’female’ 或 ‘other’ 中的一个。

1.4 ENUM 的坑：

• 修改 ENUM 选项： 如果我们要增加一个新的性别选项 ‘unknown’，就需要修改表结构：

  ALTER TABLE users MODIFY COLUMN gender ENUM('male', 'female', 'other', 'unknown') DEFAULT 'unknown';

  注意，修改 ENUM 类型可能会导致数据问题。如果已有的数据是按照原来的选项顺序存储的，修改选项后，这些数据的含义可能会发生变化。所以修改前一定要备份数据！

• ENUM 的排序： 如果我们按照 gender 字段排序：

  SELECT * FROM users ORDER BY gender;

  结果会按照 ‘male’、’female’、’other’ 的顺序排序，而不是按照字母顺序。

第二部分：SET – 多选题的诱惑

SET 类型可以看作是一个多选题，字段的值可以是预定义选项中的一个或多个，选项之间用逗号分隔。

2.1 SET 的优点：

• 存储多个值： SET 允许字段存储多个值，这在某些场景下非常有用。例如，我们可以用 SET 类型来存储用户的兴趣爱好。
• 数据校验： SET 强制字段的值必须是预定义的选项之一或多个的组合，可以防止脏数据的产生。

2.2 SET 的缺点：

• 存储空间： 相比ENUM， SET类型会占用更多的存储空间，因为它需要记录每个选项是否被选中。 选项越多，占用的空间越大。
• 模糊查询： SET 类型不太适合模糊查询，因为需要使用 FIND_IN_SET() 函数，效率较低。
• 可读性差： 当 SET 包含多个选项时，查询结果的可读性较差，需要进行额外的处理。

2.3 SET 的使用示例：

假设我们要存储用户的兴趣爱好，可以选择 SET 类型：

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  interests SET('music', 'sports', 'reading', 'movies')
);

INSERT INTO users (username, interests) VALUES
('张三', 'music,sports'),
('李四', 'reading,movies'),
('王五', 'sports');

SELECT * FROM users;

在这个例子中，interests 字段的值可以是 ‘music’、’sports’、’reading’、’movies’ 中的一个或多个的组合，用逗号分隔。

2.4 SET 的坑：

• 模糊查询： 如果我们想查询所有喜欢 sports 的用户，不能直接使用 LIKE：

  -- 错误的做法
  SELECT * FROM users WHERE interests LIKE '%sports%';

  正确的做法是使用 FIND_IN_SET() 函数：

  SELECT * FROM users WHERE FIND_IN_SET('sports', interests);

  但是，FIND_IN_SET() 函数的效率较低，特别是当数据量很大时。

• SET 的长度限制： SET 类型的长度限制是 64 个选项。如果你的选项数量超过 64 个，就不能使用 SET 类型了。

第三部分：ENUM vs SET – 选哪个？

既然 ENUM 和 SET 各有优缺点，那我们该如何选择呢？

咱们用一个表格来总结一下：

特性	ENUM	SET
选项数量	单选	多选
存储空间	小	较大
修改难度	难	难
查询效率	字符串比较效率较低	模糊查询效率较低
使用场景	性别、状态等选项固定的场景	兴趣爱好、权限等需要选择多个选项的场景
可读性	好	较差，需要额外处理
排序	按照定义顺序排序	无法直接排序
长度限制	无限制（实际上有限制，但一般够用）	64 个选项

选择建议：

• 如果你的字段只需要一个固定的值，而且选项不会经常变化，那么 ENUM 是一个不错的选择。 它可以节省存储空间，并且提供数据校验。
• 如果你的字段需要存储多个值，而且选项可能会变化，那么 SET 可以考虑。 但要注意 SET 的存储空间和查询效率问题。
• 如果你的选项数量很多，或者需要频繁修改选项，那么 ENUM 和 SET 都不是一个好选择。 可以考虑使用关联表或者其他方式来存储。

第四部分：更优雅的替代方案

ENUM 和 SET 虽然在某些场景下很有用，但它们的缺点也比较明显。有没有更优雅的替代方案呢？

4.1 关联表：

使用关联表可以将选项和数据分离，更加灵活。例如，我们可以创建一个 interests 表来存储用户的兴趣爱好：

CREATE TABLE interests (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE user_interests (
  user_id INT NOT NULL,
  interest_id INT NOT NULL,
  PRIMARY KEY (user_id, interest_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (interest_id) REFERENCES interests(id)
);

这种方式的优点是：

• 灵活： 可以随时增加或删除选项，不需要修改表结构。
• 可扩展： 可以为选项添加更多的属性，例如描述、图片等。
• 查询效率高： 可以使用索引来提高查询效率。

缺点是：

• 需要创建多个表： 增加了数据库的复杂性。
• 查询需要使用 JOIN： 增加了查询的复杂度。

4.2 JSON 类型：

MySQL 5.7 之后引入了 JSON 类型，可以用来存储任意结构化的数据。我们可以使用 JSON 类型来存储用户的兴趣爱好：

ALTER TABLE users ADD COLUMN interests JSON;

UPDATE users SET interests = JSON_ARRAY('music', 'sports');

SELECT * FROM users WHERE JSON_CONTAINS(interests, JSON_OBJECT('$','sports'));

这种方式的优点是：

• 灵活： 可以存储任意结构化的数据。
• 可扩展： 可以为选项添加更多的属性。

缺点是：

• 存储空间： JSON 类型会占用更多的存储空间。
• 查询效率： JSON 类型的查询效率相对较低。

第五部分：总结

ENUM 和 SET 是 MySQL 中两个比较特殊的数据类型，它们各有优缺点。在选择使用它们时，需要根据具体的场景进行权衡。如果选项数量不多，而且不会经常变化，ENUM 和 SET 可以考虑。如果选项数量很多，或者需要频繁修改选项，那么关联表或者 JSON 类型可能更适合。

记住，没有银弹！选择最适合你的方案才是最重要的。

结尾：老码农的忠告

好了，今天的讲座就到这里。希望大家在实际工作中，能够灵活运用 ENUM 和 SET，避免踩坑。记住老码农的忠告： “数据库设计，三分靠技术，七分靠经验！” 多踩坑，多总结，你也能成为数据库高手！

感谢大家的聆听！