15. 自增列与生成列

这一节是 DDL 主题域的收尾，讲两个非常实用的列属性：自增列让主键自动编号，生成列让某些字段自动算出来。用好了能省很多应用层代码。

15.1 AUTO_INCREMENT 是什么

AUTO_INCREMENT（自增） 是给整数列加的属性。加了它，每次插入新行时，MySQL 自动给这列生成一个比上一行大 1 的值，你不用手动指定。

最常见的用途是给主键自动编号：

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

INSERT INTO users(username) VALUES('zhangsan'), ('lisi'), ('wangwu');

mysql> SELECT * FROM users;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+

插入时没给 id 值，MySQL 自动填了 1、2、3。

15.2 AUTO_INCREMENT 的规则

自增列有几个必须知道的规则：

必须是键的一部分

自增列必须定义为某个索引的一部分（通常是主键）：

-- 正确：自增列是主键
id INT AUTO_INCREMENT PRIMARY KEY

-- 错误：自增列不是键
id INT AUTO_INCREMENT   -- 报错

每表只能一个

一张表只能有一个 AUTO_INCREMENT 列。

只能是整数类型

自增列必须是整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。浮点数、字符串不行。

插入时的行为

• 不指定或写 NULL：自动生成下一个值；
• 写 0：也触发自动生成（除非开了 NO_AUTO_VALUE_ON_ZERO 模式）；
• 写具体值：用你写的值，并把计数器更新成这个值。

INSERT INTO users(id, username) VALUES(NULL, 'a');   -- 自动生成
INSERT INTO users(id, username) VALUES(0, 'b');      -- 自动生成
INSERT INTO users(id, username) VALUES(100, 'c');    -- 用 100

-- 之后再插，从 101 开始
INSERT INTO users(username) VALUES('d');             -- id = 101

15.3 取最近插入的 ID：LAST_INSERT_ID()

插入一行后，想知道 MySQL 给它生成的自增 ID 是多少，用 LAST_INSERT_ID() 函数：

INSERT INTO users(username) VALUES('zhaoliu');

SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+

几个关键点

1. 连接级：LAST_INSERT_ID() 返回的是当前连接最后插入的 ID，不受别的连接影响，并发安全；
2. 单次插入：它返回的是本次 INSERT 第一行的 ID。如果一次插多行，返回的是第一行的：

INSERT INTO users(username) VALUES('p1'),('p2'),('p3');
SELECT LAST_INSERT_ID();   -- 返回 5（第一行 p1 的 id），不是 7

3. 必须紧跟 INSERT：如果在 INSERT 和 LAST_INSERT_ID() 之间又插了别的，值会变。

15.4 重置和设置自增值

设起始值

建表时或改表时设自增起始值：

-- 建表时从 1000 开始
CREATE TABLE t (
    id INT AUTO_INCREMENT PRIMARY KEY
) AUTO_INCREMENT = 1000;

-- 改表设起始值
ALTER TABLE users AUTO_INCREMENT = 1000;

重置的规则

ALTER TABLE ... AUTO_INCREMENT = N 有个规则：只能设成大于当前最大值的数。设成更小的值不会真的降下来，MySQL 会在下次插入时自动调整为”当前最大值 + 1”。

-- 表里最大 id 是 5
ALTER TABLE users AUTO_INCREMENT = 1;  -- 不会真的变成 1
INSERT INTO users(username) VALUES('x');  -- 实际还是从 6 开始

用 TRUNCATE 重置

TRUNCATE TABLE 会把自增值重置回 1（DELETE FROM 不会）：

TRUNCATE TABLE users;   -- 清空数据 + 自增重置为 1

15.5 AUTO_INCREMENT 的存储

不同存储引擎自增值的存储方式不同：

• InnoDB（8.0+）：自增计数器存在数据字典里（redo log 持久化），重启不丢失；
• InnoDB（8.0 之前）：计数器存在内存里，重启后会重新扫描表算最大值，可能回退；
• MyISAM：存在数据文件里，重启不丢。

15.6 生成列是什么

生成列（Generated Column） 是从 MySQL 5.7 起引入的特性。它的值不是你插入的，而是 MySQL 根据表达式自动算出来的。

比如用户有 first_name 和 last_name，你想有个 full_name 自动拼好，不用每次插入都手动拼，就能用生成列。

15.7 生成列的基本语法

定义生成列的语法：

列名 数据类型 [GENERATED ALWAYS] AS (表达式)
    [VIRTUAL | STORED] [其他约束]

• GENERATED ALWAYS：可选关键字，标明是生成列；
• AS (表达式)：必填，定义怎么算；
• VIRTUAL 或 STORED：二选一，决定怎么存。

一个例子

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    full_name VARCHAR(101)
        GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name))
);

INSERT INTO contacts(first_name, last_name) VALUES('张', '三');

mysql> SELECT * FROM contacts;
+----+------------+-----------+-----------+
| id | first_name | last_name | full_name |
+----+------------+-----------+-----------+
|  1 | 张         | 三        | 张 三     |
+----+------------+-----------+-----------+

插入时不用给 full_name，查询时它自动是 CONCAT(first_name, ' ', last_name) 的结果。

15.8 VIRTUAL 和 STORED 的区别

生成列分两种存储方式：

类型	VIRTUAL（虚拟）	STORED（存储）
是否物理存储	不存，查询时现算	存在行里
占用磁盘	不占	占
读取开销	每次读要计算	直接读
写入开销	无额外	插入/更新时要算并写
能否建索引	能（8.0+ 支持虚拟列索引）	能
默认值	是默认	要显式写

-- 虚拟列（默认）
full_name VARCHAR(101)
GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL

-- 存储列
stock_value DECIMAL(15,2)
GENERATED ALWAYS AS (price * quantity) STORED

怎么选

• 计算便宜、读取频繁 -> VIRTUAL：省空间，默认就选它；
• 计算贵、读取频繁 -> STORED：算一次存下来，读得快；
• 要建索引 -> 两者都能建，但 STORED 更直观，VIRTUAL 在 8.0+ 也能建。

15.9 生成列的限制

生成列不是万能的，有几个限制：

1. 不能手动插入或更新：生成列的值由表达式决定，INSERT/UPDATE 时不能给它赋值（除非写 DEFAULT）：

INSERT INTO contacts(first_name, last_name, full_name)
VALUES('李', '四', '自定义');  -- 报错，不能给生成列赋值

2. 表达式必须是确定性的：不能用 NOW()、RAND()、UUID() 这类每次结果不同的函数，也不能用子查询；
3. 只能引用同表的列：不能跨表引用；
4. 可以参与索引：虚拟列和存储列都能建索引，提升查询。

15.10 生成列的典型用途

用途一：派生字段

把常用的计算结果固化为列：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(12,2)
        GENERATED ALWAYS AS (quantity * unit_price) STORED,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

total_amount 自动等于 quantity * unit_price，不用应用层算，也不会算错。

用途二：从 JSON 提取字段建索引

前面 JSON 章提过，JSON 列不能直接建索引。用生成列提取 JSON 字段再建索引：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile JSON,
    city VARCHAR(50)
        GENERATED ALWAYS AS (profile->>'$.city') VIRTUAL,
    INDEX idx_city (city)
);

-- 之后能走索引查询
SELECT * FROM users WHERE city = '北京';

用途三：统一格式

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    name_upper VARCHAR(100)
        GENERATED ALWAYS AS (UPPER(name)) VIRTUAL
);

name_upper 永远是大写，做大小写不敏感的查找很方便。

15.11 自增列和生成列的组合

最后看一个综合示例，把两者都用上：

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT UNSIGNED NOT NULL DEFAULT 0,
    stock_value DECIMAL(15,2)
        GENERATED ALWAYS AS (price * quantity) STORED,
    code VARCHAR(20)
        GENERATED ALWAYS AS (CONCAT('P', LPAD(id, 6, '0'))) VIRTUAL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO products(name, price, quantity)
VALUES('鼠标', 50.00, 100), ('键盘', 200.00, 50);

mysql> SELECT id, name, price, quantity, stock_value, code FROM products;
+----+------+--------+----------+-------------+---------+
| id | name | price  | quantity | stock_value | code    |
+----+------+--------+----------+-------------+---------+
|  1 | 鼠标 |  50.00 |      100 |     5000.00 | P000001 |
|  2 | 键盘 | 200.00 |       50 |    10000.00 | P000002 |
+----+------+--------+----------+-------------+---------+

• id 自增主键；
• stock_value 是存储生成列，等于 price * quantity；
• code 是虚拟生成列，用 id 拼成 P000001 这种编码。

数据库自动维护这些派生值，应用层只管插原始数据。

15.12 小结

这一节是 DDL 主题域的收官：

• AUTO_INCREMENT 让整数列自动编号，必须是键、每表一个、只能整数；
• 插 NULL/0 触发自增，插具体值会重置计数器；
• LAST_INSERT_ID() 取当前连接最近插入的 ID，并发安全；
• ALTER TABLE ... AUTO_INCREMENT = N 只能设大不能设小；
• 删行后自增值不回收，会有空洞，正常现象；
• 8.0+ 自增值持久化，重启不丢；
• 生成列 GENERATED ALWAYS AS (表达式) 自动算值；
• VIRTUAL 不存储（默认），STORED 物理存储；
• 生成列不能手动赋值，表达式必须确定；
• 用途：派生字段、JSON 提取建索引、统一格式。

到这里，入门与 DDL 主题域（第 1~15 章）就全部讲完了。你已经能装 MySQL、连服务器、建库建表、选数据类型、改结构、管理字符集。下一章开始进入 DML 与查询，学习怎么往表里插数据、查数据。