以下是配合 SQL 示例修改后的全文。第 3 节和第 4 节之间的 DDL 执行行为部分已按你的要求做了扩展。

生成列的基础知识

生成列（Generated Column）是 MySQL 5.7 引入的特性：列的值不由 INSERT 或 UPDATE 显式提供，而由列定义中的表达式自动计算。基本语法：

col_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]

GENERATED ALWAYS 可选；VIRTUAL | STORED 不写时默认为 VIRTUAL。表达式只能使用确定性内置函数、字面量和运算符，不允许子查询、用户变量、存储函数或非确定性函数（如 NOW()）。

VIRTUAL / STORED：区别、共同点与索引

核心区别

共同点

两者都由表达式自动维护，不允许在 DML 中直接赋值；都支持 NOT NULL、COMMENT；都可以引用普通列和位于其之前的生成列。

索引上的使用

InnoDB 支持在 VIRTUAL 生成列上创建二级索引，且添加或删除该二级索引是就地操作（in-place），不需要复制表。STORED 列的索引天然可用；对 STORED 列而言，索引变更也是就地完成的，但表达式变更不是就地操作。

ADD COLUMN ... GENERATED ALWAYS AS (...) 的用法

在已有表上添加生成列的标准写法：

ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

但 ADD COLUMN 的执行算法因存储类型而不同，这是实际操作中最需要区分的部分。

STORED 列的 ADD COLUMN：不是就地操作

对 STORED 列执行 ADD COLUMN 不是就地操作，因为服务器必须逐行计算表达式并物化写入磁盘，无法绕过全表数据重写。

-- 示例：在已有大表上添加 STORED 生成列
DROP TABLE IF EXISTS t_add_stored;
CREATE TABLE t_add_stored (
  id INT PRIMARY KEY AUTO_INCREMENT,
  a INT NOT NULL,
  b INT NOT NULL
) ENGINE=InnoDB;

INSERT INTO t_add_stored (a, b)
SELECT n, n * 2 FROM (
  WITH RECURSIVE seq AS (
    SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 10000
  ) SELECT n FROM seq
) t;

-- 添加 STORED 生成列：需要计算并写入每一行，不是 in-place
ALTER TABLE t_add_stored
  ADD COLUMN total INT GENERATED ALWAYS AS (a + b) STORED;

这条 ADD COLUMN 在 MySQL 8.0 中不能使用 ALGORITHM=INPLACE，实际会走 COPY 或重建表的方式。如果在有并发写入的生产表上执行，需要预留足够的 DDL 窗口。

VIRTUAL 列的 ADD COLUMN：非分区表上可以是就地操作

对 VIRTUAL 列，在非分区表上 ADD COLUMN 和 DROP COLUMN 是就地操作，因为 VIRTUAL 列不落盘，只需修改元数据即可完成。

-- 示例：在同样的表上添加 VIRTUAL 生成列
DROP TABLE IF EXISTS t_add_virtual;
CREATE TABLE t_add_virtual (
  id INT PRIMARY KEY AUTO_INCREMENT,
  a INT NOT NULL,
  b INT NOT NULL
) ENGINE=InnoDB;

INSERT INTO t_add_virtual (a, b)
SELECT a, b FROM t_add_stored;

-- 非分区表：VIRTUAL 列的 ADD COLUMN 可以是 in-place
ALTER TABLE t_add_virtual
  ADD COLUMN total INT GENERATED ALWAYS AS (a + b) VIRTUAL;

但有两个重要限制：

（1）不能与其他 ALTER TABLE 操作组合。如果一条 ALTER TABLE 中同时包含添加 VIRTUAL 列和其他操作（比如再加一个普通列或索引），整体就不再是就地操作。

-- 这样写会失去 in-place 特性：
-- 添加 VIRTUAL 列 + 同时添加普通列，组合操作不是 in-place
ALTER TABLE t_add_virtual
  ADD COLUMN total2 INT GENERATED ALWAYS AS (a * b) VIRTUAL,
  ADD COLUMN remark VARCHAR(50) DEFAULT '';

（2）分区表例外。对分区表，VIRTUAL 列的 ADD COLUMN / DROP COLUMN 不是就地操作。

VIRTUAL 列上加二级索引：索引操作本身是就地完成的

在已有 VIRTUAL 生成列上添加二级索引，索引操作本身是就地完成的，不需要重建表。这与“添加 VIRTUAL 列”是两个独立的 DDL 动作，可以分开执行以获得各自的最优算法：

-- 先加 VIRTUAL 列（in-place）
ALTER TABLE t_add_virtual
  ADD COLUMN total INT GENERATED ALWAYS AS (a + b) VIRTUAL;

-- 再单独加索引（索引操作本身 in-place）
ALTER TABLE t_add_virtual
  ADD INDEX idx_total (total);

反过来，如果希望一步完成“加 VIRTUAL 列 + 加索引”，组合操作会失去 VIRTUAL 列 ADD COLUMN 的 in-place 特性，但索引部分仍然可以就地完成。

WITHOUT VALIDATION / WITH VALIDATION：VIRTUAL 列的取值范围校验

默认行为：不校验

当添加或修改 VIRTUAL 生成列时，MySQL 默认不校验表达式计算结果是否超出列数据类型范围。这意味着，如果表达式的计算结果与声明的类型不匹配，DDL 本身可能成功，但后续读取时才报告警告或错误。

-- 示例：a + b 可能超过 TINYINT 的范围（TINYINT 上限 127）
DROP TABLE IF EXISTS t_validation;
CREATE TABLE t_validation (
  id INT PRIMARY KEY AUTO_INCREMENT,
  a INT NOT NULL,
  b INT NOT NULL
) ENGINE=InnoDB;

INSERT INTO t_validation (a, b) VALUES (100, 100), (200, 50);

-- 默认 WITHOUT VALIDATION：加 VIRTUAL 列，类型声明为 TINYINT
-- 100 + 100 = 200 > 127，但 DDL 不一定立即报错
ALTER TABLE t_validation
  ADD COLUMN total TINYINT GENERATED ALWAYS AS (a + b) VIRTUAL;

在这种情况下，ADD COLUMN 可能成功，但之后查询 total 列时，超出范围的行会返回警告或触发意外错误。

WITH VALIDATION：复制表并严格校验

如果显式加上 WITH VALIDATION，ALTER TABLE 会复制表并逐行校验生成列表达式的计算结果；一旦出现超出范围或任何其他错误，语句直接失败。

-- 使用 WITH VALIDATION：会复制表，严格校验
ALTER TABLE t_validation
  ADD COLUMN total2 TINYINT GENERATED ALWAYS AS (a + b) VIRTUAL,
  WITH VALIDATION;

由于 a + b 对部分行超过 TINYINT 上限，这条语句会直接失败，而不是留下“脏数据”。代价是表复制带来的耗时和额外磁盘空间。

操作选择速查

WITHOUT VALIDATION 和 WITH VALIDATION 仅在 ADD COLUMN、CHANGE COLUMN、MODIFY COLUMN 中允许使用。

表达式、数据类型、列名与存储类型的调整

ALTER TABLE 对生成列允许的操作是 ADD、MODIFY、CHANGE。

表达式和数据类型可以同时修改：

ALTER TABLE t1
  MODIFY COLUMN c2 TINYINT GENERATED ALWAYS AS (c1 + 5) STORED;

这条语句把数据类型从 INT 改为 TINYINT，表达式从 c1 + 1 改为 c1 + 5。

列名调整：如果生成列没有被其他列引用，可以使用 CHANGE 重命名，但必须完整重新指定列定义。

STORED 与 VIRTUAL 不能直接互转：MODIFY / CHANGE 无法把 VIRTUAL 改成 STORED，反之亦然；必须 DROP 后重新 ADD。

普通列与生成列的互转：普通列可以转为 STORED，但不能转为 VIRTUAL；STORED 生成列可以转为普通列（生成值保留为普通列值），VIRTUAL 生成列不能转为普通列。

可执行脚本示例

仅生成列上创建索引(不使用函数)

三张结构对应的表：t_no_gen（无生成列）、t_virtual（VIRTUAL + 索引）、t_stored（STORED + 索引）。

SET SESSION cte_max_recursion_depth = 10000;

-- 第一个表无生成列
DROP TABLE IF EXISTS t_no_gen;
CREATE TABLE t_no_gen (
  id INT PRIMARY KEY AUTO_INCREMENT,
  a INT NOT NULL,
  b INT NOT NULL
) ENGINE=InnoDB;

-- 第二个表VIRTUAL生成列
DROP TABLE IF EXISTS t_virtual;
CREATE TABLE t_virtual (
  id INT PRIMARY KEY AUTO_INCREMENT,
  a INT NOT NULL,
  b INT NOT NULL,
  total INT GENERATED ALWAYS AS (a + b) VIRTUAL,
  INDEX idx_total (total) -- total为索引
) ENGINE=InnoDB;

-- 第三个表 STORED 生成列
DROP TABLE IF EXISTS t_stored;
CREATE TABLE t_stored (
  id INT PRIMARY KEY AUTO_INCREMENT,
  a INT NOT NULL,
  b INT NOT NULL,
  total INT GENERATED ALWAYS AS (a + b) STORED,
  INDEX idx_total (total) -- total为索引
) ENGINE=InnoDB;

INSERT INTO t_no_gen (a, b)
WITH RECURSIVE seq AS (
  SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 5000
)
SELECT n, n * 2 FROM seq;

INSERT INTO t_virtual (a, b) SELECT a, b FROM t_no_gen;
INSERT INTO t_stored  (a, b) SELECT a, b FROM t_no_gen;

对比查询：

EXPLAIN SELECT * FROM t_no_gen  WHERE a + b = 3000;  -- type=ALL, key=NULL
EXPLAIN SELECT * FROM t_virtual WHERE total = 3000;   -- type=ref, key=idx_total
EXPLAIN SELECT * FROM t_stored  WHERE total = 3000;   -- type=ref, key=idx_total

解读：t_no_gen 在 a + b 上没有索引，只能全表扫描；t_virtual 和 t_stored 在 InnoDB 下都能通过生成列上的二级索引把访问类型从 ALL 降为 ref。在查询加速上两者效果相同，差异主要在写入成本、磁盘占用和 DDL 算法（见第 3 节）。

查询普通表(不使用函数)

EXPLAIN SELECT * FROM t_no_gen  WHERE a + b = 3000;

VIRTUAL生成列

STORED 生成列

均建有索引(使用函数)

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_no_gen
-- ----------------------------
DROP TABLE IF EXISTS `t_no_gen`;
CREATE TABLE `t_no_gen`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int NOT NULL,
  `b` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE, 
  INDEX `idx_a`(`a`) USING BTREE,-- 列a上有索引
  INDEX `idx_b`(`b`) USING BTREE,-- 列b上有索引
  INDEX `idx_a-b`(`a`, `b`) USING BTREE --列a和列b组成联合索引
) ENGINE = InnoDB AUTO_INCREMENT = 8192 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_stored
-- ----------------------------
DROP TABLE IF EXISTS `t_stored`;
CREATE TABLE `t_stored`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int NOT NULL,
  `b` int NOT NULL,
  `total` int GENERATED ALWAYS AS ((ifnull(`a`,0) + ifnull(`b`,0))) STORED NULL, -- 生成列索引
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_total`(`total`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8192 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_virtual
-- ----------------------------
DROP TABLE IF EXISTS `t_virtual`;
CREATE TABLE `t_virtual`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int NOT NULL,
  `b` int NOT NULL,
  `total` int GENERATED ALWAYS AS ((ifnull(`a`,0) + ifnull(`b`,0))) VIRTUAL NULL,  -- 生成列索引
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_total`(`total`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8192 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

查询普通表(不使用函数)

EXPLAIN SELECT * FROM t_no_gen  WHERE a + b = 3000;

查询普通表(使用函数)

答疑：为什么两次查询普通表都使用索引

遇到的现象是 MySQL 优化器的一个典型行为：查询条件在索引列上进行了运算（甚至是函数包裹），EXPLAIN 依然显示了索引，但这里的 type 是 index，而不是更高效的 ref 或 range。

解读 EXPLAIN 结果：效率并不高的“使用索引”

两条 SQL 的 EXPLAIN 结果一致：type 为 index，key 为 idx_a-b，Extra 为 Using where。这组信息表明，索引并未用于快速定位数据。

• type: index：这代表全索引扫描 (Full Index Scan)。MySQL 会遍历整个 idx_a-b 索引树来获取数据，而不是像 ref 或 range 那样利用索引进行快速查找。其效率通常比全表扫描 (ALL) 要好一些，但远低于 ref 或 range。
• key: idx_a-b：这表明优化器选择使用 idx_a-b 这个联合索引。
• Extra: Using where：这表示在通过索引获取到数据后，MySQL 还需要在 Server 层进行额外的 WHERE 条件过滤。这意味着索引并没有完成所有的过滤工作。

综合来看，MySQL 在这里只是把 idx_a-b 当作一个更小的、有序的表来扫描，而不是用它来直接定位 a + b = 3000 对应的行。

为什么条件复杂仍“用”了索引？

核心原因在于查询所需的列 a 和 b 全部包含在 idx_a-b 这个联合索引中。

虽然 WHERE 条件 a + b = 3000 和 IFNULL(a,0) + IFNULL(b,0) = 3000 无法直接利用 B+ 树索引进行快速定位，但优化器意识到，与其回表读取整行数据，不如直接扫描这个体积更小、且已经包含了所需全部列 (a 和 b) 的索引树。这种技术被称为索引覆盖 (Covering Index)。扫描索引树所需的数据量通常远小于扫描整个表的数据量，因此优化器认为扫描整个索引的代价低于扫描全表。

关于 IFNULL 的特别说明：
一般情况下，在 WHERE 子句中对索引列使用函数会导致索引失效。但在你的查询中，IFNULL 包裹的是 a 和 b，而这两个列恰好都在联合索引 idx_a-b 中。因此，优化器依然可以采用全索引扫描的方式，先读取所有行的 a 和 b 值，再在 Server 层计算 IFNULL(a,0) + IFNULL(b,0) 并与 3000 进行比较。这并非“高效使用索引”，而是“在无法高效使用索引时，选择了一种代价相对较低的扫描方式”。

如何让索引真正“高效”工作？

要想让索引发挥 ref 或 range 级别的快速查找作用，WHERE 条件中的索引列必须是“裸露”的，即不能包裹任何函数或参与运算。

• 针对 a + b = 3000：如果业务中这类查询很频繁，最佳实践是使用生成列。可以添加一个 STORED 生成列 total AS (a + b)，并直接在 total 上建立索引。这样，查询 WHERE total = 3000 就能精准使用索引了。
• 针对 IFNULL：如果 a 和 b 列可能为 NULL，且你希望将 NULL 视为 0 来处理，那么 IFNULL 是必要的。但在当前场景下，它阻止了索引进行快速定位。如果业务逻辑允许，可以考虑在数据库设计层面就处理好 NULL 值（例如使用 DEFAULT 0），从而避免在查询中使用 IFNULL，让 WHERE a + b = 3000 这样的条件有潜力用到索引（比如生成列索引）。

VIRTUAL生成列

STORED 生成列

直接操作生成列

插入t_stored生成列

执行时报错

INSERT INTO t_stored (id,a,b,total) VALUES(100000,1,2,3)
> 3105 - The value specified for generated column 'total' in table 't_stored' is not allowed.
> 时间: 0.002s

更新t_stored生成列

执行时报错

-- INSERT INTO t_stored (id,a,b,total) VALUES(100000,1,2,5);

UPDATE t_stored set total=3 where id=1
> 3105 - The value specified for generated column 'total' in table 't_stored' is not allowed.
> 时间: 0.002s

插入t_virtual生成列

直接报错

INSERT INTO t_virtual (id,a,b,total) VALUES(100000,1,2,5)
> 3105 - The value specified for generated column 'total' in table 't_virtual' is not allowed.
> 时间: 0s

更新t_virtual生成列

直接报错

UPDATE t_virtual set total=3 where id=1
> 3105 - The value specified for generated column 'total' in table 't_virtual' is not allowed.
> 时间: 0s

• ← Previous Post
• Next Post →