MySQL高阶讲座之：`MySQL`的`ENUM`与`INT`：在数据类型设计中的性能与可维护性权衡。

各位观众老爷，大家好！我是今天的主讲人，大家都叫我老码。今天咱们不整那些虚头巴脑的，直接上干货，聊聊MySQL里两个看似简单，实则暗藏玄机的家伙：ENUM和INT。

开场白：都是选项惹的祸

话说，咱们在设计数据库的时候，经常会遇到选择项的问题，比如：

• 用户的性别：男/女/其他
• 订单的状态：待支付/已支付/已发货/已完成/已取消
• 商品的类型：电子产品/服装/食品/家居

这时候，我们该如何选择数据类型来存储这些选项呢？ENUM和INT，就像是两位武林高手，各有千秋，就看你更欣赏哪一种风格了。

第一回合：ENUM——优雅的类型

ENUM，全称是枚举类型，它的特点是：

• 预定义值： 你必须事先定义好所有可能的值，就像给变量贴上标签一样。
• 存储优化： MySQL会用整数来存储ENUM值，但对外表现的仍然是字符串。
• 可读性强： 直接看到的是字符串，更容易理解数据的含义。

语法演示

咱们先来个简单的例子，创建一个表来存储用户的性别：

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  gender ENUM('男', '女', '其他') DEFAULT '其他'
);

INSERT INTO users (username, gender) VALUES
('张三', '男'),
('李四', '女'),
('王五', '其他');

SELECT * FROM users;

运行结果大概是这样：

id	username	gender
1	张三	男
2	李四	女
3	王五	其他

你看，gender字段直接显示的是我们定义的字符串，是不是一目了然？

ENUM的优点

• 数据校验： ENUM类型自带数据校验功能。如果你尝试插入一个不在枚举列表中的值，MySQL会报错。这可以有效防止脏数据进入数据库。

INSERT INTO users (username, gender) VALUES ('赵六', '人妖'); -- 会报错！

• 节省空间： 虽然存储的是字符串，但MySQL实际上用整数来存储ENUM值。如果枚举值很少，可以节省存储空间。例如，只有两个枚举值时，只需要1个字节就能存储。
• 可读性好： 查询结果直接显示的是字符串，方便理解。

ENUM的缺点

• 修改麻烦： 如果你需要修改枚举值，比如增加一个性别选项“保密”，你需要修改表结构。在大数据量的情况下，修改表结构可能会比较耗时。
• 排序问题： ENUM的排序是按照枚举值的定义顺序来的，而不是按照字母顺序。如果你的枚举值不是按照你期望的顺序定义的，可能会导致排序结果不符合预期。
• 字符串比较： 虽然显示的是字符串，但底层是数字。如果要在查询中使用字符串进行比较，需要注意字符集和大小写的问题。

深入理解ENUM的存储

为了搞清楚ENUM的底层存储，咱们可以这样查询：

SELECT gender + 0 FROM users;

运行结果会显示ENUM值对应的整数：

gender + 0
1
2
3

这是因为MySQL会给每个ENUM值分配一个整数，从1开始。'男'对应1，'女'对应2，'其他'对应3。

总结ENUM

ENUM就像一个精致的盒子，可以把你的选项都装进去，并且帮你把关，防止乱七八糟的东西混进来。但是，这个盒子一旦做好了，想改动就比较麻烦。

特性	优点	缺点
数据校验	强制数据符合预定义值，防止脏数据	修改枚举值需要修改表结构，可能影响性能
存储空间	使用整数存储，节省空间	枚举值过多时，空间优势不明显
可读性	直接显示字符串，易于理解	字符串比较需要注意字符集和大小写
排序	按照枚举值的定义顺序排序	如果枚举值顺序不合理，可能导致排序结果不符合预期

第二回合：INT——灵活的数字

INT，也就是整数类型，它的特点是：

• 自由度高： 你可以随意存储数字，不需要事先定义好所有可能的值。
• 修改方便： 增加或删除选项，只需要修改代码或配置，不需要修改表结构。
• 灵活性强： 可以使用位运算等高级技巧，实现更复杂的功能。

语法演示

咱们用INT来存储用户的性别：

CREATE TABLE users2 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  gender INT DEFAULT 0
);

INSERT INTO users2 (username, gender) VALUES
('张三', 1),
('李四', 2),
('王五', 0);

SELECT * FROM users2;

运行结果：

id	username	gender
1	张三	1
2	李四	2
3	王五	0

你看，gender字段存储的是数字，你需要自己维护数字和选项的对应关系。比如，你可以约定：

• 0：其他
• 1：男
• 2：女

INT的优点

• 修改灵活： 增加或删除选项，只需要修改代码或配置，不需要修改表结构。
• 性能更好： 整数比较比字符串比较更快。
• 扩展性强： 可以使用位运算等高级技巧，实现更复杂的功能。

INT的缺点

• 数据校验弱： 你需要自己编写代码来校验数据的有效性。如果没有校验，很容易插入无效数据。
• 可读性差： 直接看到的是数字，不容易理解数据的含义。
• 需要维护映射关系： 你需要维护数字和选项的对应关系，增加了代码的复杂度。

使用INT的正确姿势

虽然INT比较灵活，但也需要一些技巧才能用好它。

• 定义常量： 为了提高代码的可读性，可以使用常量来表示不同的选项。

<?php
const GENDER_OTHER = 0;
const GENDER_MALE = 1;
const GENDER_FEMALE = 2;

// 插入数据
$gender = GENDER_MALE;
$sql = "INSERT INTO users2 (username, gender) VALUES ('张三', $gender)";

// 查询数据
$sql = "SELECT * FROM users2 WHERE gender = " . GENDER_FEMALE;
?>

• 使用CHECK约束： 虽然INT没有像ENUM那样自带数据校验功能，但你可以使用CHECK约束来限制数据的范围。

ALTER TABLE users2 ADD CONSTRAINT chk_gender CHECK (gender IN (0, 1, 2));

INSERT INTO users2 (username, gender) VALUES ('赵六', 3); -- 会报错！

• 使用ENUM进行显示： 你可以在查询的时候，使用CASE语句或者ENUM类型来将整数转换为字符串，提高可读性。

SELECT
  username,
  CASE gender
    WHEN 0 THEN '其他'
    WHEN 1 THEN '男'
    WHEN 2 THEN '女'
    ELSE '未知'
  END AS gender
FROM users2;

-- 或者创建一个临时的ENUM类型
ALTER TABLE users2 MODIFY COLUMN gender ENUM('其他', '男', '女');

SELECT username, gender FROM users2;

-- 之后再改回INT类型，如果需要的话
ALTER TABLE users2 MODIFY COLUMN gender INT;

总结INT

INT就像一块橡皮泥，你可以随意捏成各种形状。但是，你也需要自己负责捏出你想要的形状，并且保证捏出来的东西是正确的。

特性	优点	缺点
数据校验	需要手动校验，灵活	需要编写额外的代码进行校验，容易出错
存储空间	根据INT类型的大小而定	可能比ENUM占用更多空间（当ENUM选项较少时）
可读性	需要手动维护映射关系，可读性较差	需要额外的代码来提高可读性
扩展性	灵活，易于扩展，可以使用位运算等高级技巧	需要更多的设计和编码工作

第三回合：性能大比拼

说完了优缺点，咱们再来聊聊性能。很多人认为ENUM比INT更快，因为ENUM底层是用整数存储的。但实际上，这并不一定。

• 存储空间： 当枚举值很少的时候，ENUM可以节省存储空间。但当枚举值很多的时候，INT可能更省空间，因为ENUM需要额外的元数据来存储枚举值的定义。
• 查询速度： 整数比较比字符串比较更快。所以，如果你的查询条件是整数，INT可能会更快。但如果你的查询条件是字符串，ENUM需要先将字符串转换为整数，可能会慢一些。
• 索引： ENUM和INT都可以使用索引来提高查询速度。但是，如果你的查询条件是字符串，ENUM的索引可能效率更高，因为它可以直接使用字符串索引。

总的来说，ENUM和INT的性能差距并不大。在大多数情况下，性能不是选择数据类型的主要因素。更重要的是可维护性和灵活性。

第四回合：实战案例分析

咱们来看几个实战案例，分析一下在不同的场景下，应该如何选择ENUM和INT。

• 用户的性别： 如果性别选项很少，而且不太可能发生变化，可以选择ENUM。
• 订单的状态： 如果订单状态比较多，而且可能会增加新的状态，可以选择INT。
• 商品的类型： 如果商品类型比较固定，可以选择ENUM。但如果商品类型非常多，而且经常会增加新的类型，可以选择INT，并且使用分类表来管理商品类型。

总结陈词：选择适合自己的才是最好的

ENUM和INT，就像是两种不同的工具，各有各的用途。没有绝对的好坏，只有适合不适合。

• 如果你追求数据的完整性和可读性，而且选项比较固定，可以选择ENUM。
• 如果你追求灵活性和扩展性，而且选项可能会发生变化，可以选择INT。

最重要的是，你要根据自己的实际情况，权衡利弊，选择最适合自己的数据类型。不要盲目追求性能，也不要过度设计。记住，简单才是王道！

好了，今天的讲座就到这里。希望大家有所收获！如果有什么问题，欢迎提问。谢谢大家！