表结构变更（DDL 实战）

6 min中级2026/10/11

单一主题：建表、ALTER TABLE 全家桶与变更工作流、DROP/TRUNCATE/DELETE 三兄弟、表复制与临时表——改动库结构的安全操作手册

知识点地图

知识类别：SQL DDL / 表结构定义与变更（本篇只讲结构层，不讲数据层）。
解决什么问题：业务上线后要加列、改类型、删旧表——这些操作和 INSERT/UPDATE 的风险等级完全不同：很多 DDL 不可回滚。本篇给一套”改动结构不翻车”的操作手册。
什么时候用到：新表设计落地、迭代加字段、数据清理（删表/清空）、备份副本、临时计算表。
边界声明：本篇由旧版”DDL 大全”收窄而来。原篇的数据类型节已归数据类型，六大约束节归约束，视图节归视图与物化视图，索引节归索引，序列与自增见 PG 序列。

心智模型：DDL 与 DML 的风险差异

	DML（INSERT/UPDATE/DELETE）	DDL（CREATE/ALTER/DROP）
可回滚	通常可（在事务里）	多数方言隐式提交，回不了
影响	数据	结构（连带数据，如 DROP COLUMN）
锁	行锁	常是表级锁（大表 ALTER 可能长时间阻塞）
审批	日常	生产变更流程

由此得出本篇的总纪律：生产 DDL 走变更单，先备份，挑低峰，能用”加列”完成的不要”删列重建”。

建表：一个能直接抄的模板

以 vocaloid 曲库的 producer 表为例（完整造数见曲库项目）：

sql

CREATE TABLE IF NOT EXISTS producer (
    producer_id   INT AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(50)  NOT NULL COMMENT 'P 主名',
    circle        VARCHAR(100) DEFAULT NULL COMMENT '所属社团',
    debut_year    SMALLINT     NOT NULL,
    INDEX idx_producer_debut (debut_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='P主（创作者）表';

逐段讲解模板里的每个决策：

IF NOT EXISTS：脚本可重复执行（CI 初始化、重放迁移）不报错；
utf8mb4 显式指定：MySQL 的”utf8”实为 3 字节的 utf8mb3，存不了生僻字与 emoji——建库建表两处都要显式 utf8mb4，靠服务器默认值迟早翻车；
COMMENT：字段语义写在库里，DataGrip/Navicat 悬停可见，比翻文档靠谱；
SMALLINT 存年份而不是 INT：够用类型（见数据类型的选型原则）。

ALTER TABLE 全家桶

十连操作（每个都”改了再改回来”，脚本可复跑——这是写变更脚本的通用手法）：

sql

-- 1. 加列
ALTER TABLE vsinger ADD COLUMN fanclub VARCHAR(50) DEFAULT NULL;
ALTER TABLE vsinger DROP COLUMN fanclub;

-- 2. 改列类型（MySQL 用 MODIFY；PG 用 ALTER COLUMN ... TYPE）
ALTER TABLE vsinger MODIFY COLUMN name VARCHAR(100);
ALTER TABLE vsinger MODIFY COLUMN name VARCHAR(50);

-- 3. 改列名（MySQL 8.0+ 的 RENAME COLUMN；老版本用 CHANGE 带旧类型）
ALTER TABLE vsinger RENAME COLUMN name TO stage_name;
ALTER TABLE vsinger RENAME COLUMN stage_name TO name;

-- 4. 改默认值
ALTER TABLE vsinger ALTER COLUMN nationality SET DEFAULT '中国';
ALTER TABLE vsinger ALTER COLUMN nationality DROP DEFAULT;

-- 5. 表改名
ALTER TABLE vsinger RENAME TO singers;
ALTER TABLE singers RENAME TO vsinger;

-- 6. 加唯一约束
ALTER TABLE producer ADD CONSTRAINT uk_circle UNIQUE (circle);
ALTER TABLE producer DROP INDEX uk_circle;      -- MySQL 删唯一约束用 DROP INDEX

-- 7. 加外键
ALTER TABLE music ADD CONSTRAINT fk_music_producer
  FOREIGN KEY (producer_id) REFERENCES producer(producer_id);
ALTER TABLE music DROP FOREIGN KEY fk_music_producer;

-- 8. 加非空
ALTER TABLE producer MODIFY COLUMN circle VARCHAR(100) NOT NULL;
ALTER TABLE producer MODIFY COLUMN circle VARCHAR(100) DEFAULT NULL;

三个方言与版本坑：

MySQL 没有 DROP COLUMN IF EXISTS——ALTER TABLE t DROP COLUMN IF EXISTS c 直接报 ERROR 1064（语法不认）。安全删列用 INFORMATION_SCHEMA 先判断：

sql

-- 安全删除：列存在才执行（用动态 SQL）
SET @col_exists = (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'vsinger' AND COLUMN_NAME = 'fanclub'
);
SET @ddl = IF(@col_exists > 0,
              'ALTER TABLE vsinger DROP COLUMN fanclub', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

注意与”列不存在”的 ERROR 1091 区分：1064 是语法错（IF EXISTS 不被支持），1091 是对象真不存在； 2. PG 一侧 IF [NOT] EXISTS 全家支持：ADD COLUMN IF NOT EXISTS / DROP COLUMN IF EXISTS / CREATE INDEX IF NOT EXISTS 都是原生语法； 3. CHANGE vs MODIFY vs RENAME：MySQL 老写法 CHANGE COLUMN 旧新类型（必须带完整列定义），8.0 起才有干净的 RENAME COLUMN；PG 的 RENAME COLUMN 一直可用。写跨库迁移脚本时按方言分文件，别混写。

变更工作流：大表改列的完整决策

ALTER TABLE 的真实风险是锁表时长。不同操作的代价分级：

text

秒级（元数据变更）：改列名、加列（多数场景）、加/删默认值
分钟到小时级：     改列类型、加非空、加外键（要全表校验/重建）
MySQL 8.0 的 INSTANT 算法：加列常为秒级（8.0.12+），但改类型仍是重建

生产变更四步：

text

1. 备份：mysqldump 该表（或 CTAS 建副本，见下节）
2. 评估：DESC + 估算行数，判断操作落在哪一档锁级别
3. 低峰执行：大表用 online DDL / pt-online-schema-change（ gh-ost ），别裸 ALTER
4. 验证：DESC 确认结构 + 抽查应用读写正常

换一种写法会发生什么：直接在生产高峰 ALTER TABLE 千万表 MODIFY 列类型——表锁挂起期间所有写入排队，连接池迅速耗尽（连锁反应见 PG 连接池篇的事故复盘，同款机理）。

DROP / TRUNCATE / DELETE：三兄弟对照

	DELETE	TRUNCATE	DROP
删什么	数据（可带 WHERE）	全部数据	整张表（结构+数据）
可回滚	可（事务内）	不可（隐式提交）	不可
自增计数	保留	重置到初值	随表消失
触发器	触发 DELETE 触发器	不触发	无从谈起
速度	逐行，慢	快（按页释放）	快

选择口诀：删部分行用 DELETE；清空整表且确认无回滚需求用 TRUNCATE；表整个不要了用 DROP。

安全删除四步（删任何结构/数据前）：

text

1. 备份：CREATE TABLE t_bak AS SELECT * FROM t;   或 mysqldump
2. 确认影响面：SELECT COUNT(*) 看行数；查外键引用（INFORMATION_SCHEMA.KEY_COLUMN_USAGE）
3. 试删验证：事务里 DELETE ... WHERE ...，SELECT 核对，再 ROLLBACK 或 COMMIT
4. 执行与留痕：低峰执行，变更单记录时间、操作人、备份位置

表复制三式

sql

-- LIKE：结构 + 索引，不带数据（备份表结构首选）
CREATE TABLE vsinger_bak LIKE vsinger;

-- CTAS：结构 + 数据，不带索引/约束/注释（快照数据用）
CREATE TABLE vsinger_snapshot AS SELECT * FROM vsinger;

-- 带 WHERE 的部分复制（归档历史数据）
CREATE TABLE music_archive AS
SELECT * FROM music WHERE release_date < '2020-01-01';

三者差异一句话：LIKE 复制”骨架”，CTAS 复制”内容”——CTAS 的副本没有主键、索引、AUTO_INCREMENT 属性，拿它当正式表用会丢约束。要从副本恢复索引，先 LIKE 建骨架再 INSERT INTO ... SELECT。

临时表

sql

CREATE TEMPORARY TABLE tmp_dept_avg AS
SELECT company, AVG(debut_year) AS avg_year
FROM vsinger GROUP BY company;

SELECT * FROM tmp_dept_avg;   -- 只在当前连接可见
-- 连接断开自动删除；同名普通表会被临时表遮蔽（小心误判）

用途：报表会话里的中间结果落表（比多层子查询可读）。局限：临时表上的索引/统计信息弱、连接池环境下”连接断开”时机不可控——可复用查询优先 CTE（见CTE与 CTE 的”存不存一份”讨论）。

序列

sql

-- PostgreSQL: 创建序列
CREATE SEQUENCE order_seq
  START WITH 1000
  INCREMENT BY 1
  NO MAXVALUE
  NO CYCLE
  CACHE 20;

-- 使用序列
INSERT INTO orders (id, customer_id) VALUES (NEXTVAL('order_seq'), 1);

-- 查看当前值（不消耗）
SELECT CURRVAL('order_seq');

-- 设置序列值
SELECT SETVAL('order_seq', 5000);

-- 将序列关联到列
ALTER TABLE orders ALTER COLUMN id SET DEFAULT NEXTVAL('order_seq');

-- MySQL: 使用 AUTO_INCREMENT（无独立序列对象）
-- Oracle: 使用 SEQUENCE + TRIGGER 实现自增
CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER trg_orders_id
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  :NEW.id := order_seq.NEXTVAL;
END;

动手实践：给曲库做一轮”变更演练”

任务（全部在练习库，每步都改了再改回来）：

给 vsinger 加 heat_score DECIMAL(5,2) DEFAULT 50，验证默认值对新旧行各自的生效时机（旧行会不会被填 50？）；
用 INFORMATION_SCHEMA 动态 SQL 安全删除 heat_score；
用三种方式各建一个 music 副本，DESC 对比约束/索引差异；
演示”事务里放 DDL”的隐式提交：BEGIN 后 UPDATE 一行、再 CREATE TABLE，然后 ROLLBACK——检查 UPDATE 是否被回滚（答案见TCL）；
写一个”重建表换分区键”的完整脚本：LIKE 建新表、INSERT SELECT 搬数据、RENAME 换名、验证行数。

参考实现（先自己写再展开）

sql

-- 1
ALTER TABLE vsinger ADD COLUMN heat_score DECIMAL(5,2) DEFAULT 50;
INSERT INTO vsinger (name, debut_year, company) VALUES ('测试姬', 2026, '测试社');
SELECT name, heat_score FROM vsinger WHERE name = '测试姬';   -- 50（新行取默认）
SELECT name, heat_score FROM vsinger LIMIT 3;                  -- NULL（旧行不受影响）
-- 结论：DEFAULT 只对"之后"的插入生效，存量行需手动 UPDATE 补值

-- 2（正文 INFORMATION_SCHEMA 动态 SQL 原样执行，表名列名对应替换）

-- 3
CREATE TABLE m1 LIKE music;                                     -- 有索引有约束
CREATE TABLE m2 AS SELECT * FROM music;                         -- 无索引无约束
DESC m1; DESC m2;   -- 对比 Key 列
DROP TABLE m1; DROP TABLE m2;

-- 4
START TRANSACTION;
UPDATE vsinger SET heat_score = 99 WHERE name = '测试姬';
CREATE TABLE ddl_probe (id INT);
ROLLBACK;
SELECT heat_score FROM vsinger WHERE name = '测试姬';           -- 仍是 99！UPDATE 被 CREATE TABLE 隐式提交了

-- 5
CREATE TABLE music_v2 LIKE music;
INSERT INTO music_v2 SELECT * FROM music;
SELECT (SELECT COUNT(*) FROM music) AS src, (SELECT COUNT(*) FROM music_v2) AS dst;
RENAME TABLE music TO music_old, music_v2 TO music;
-- 验证应用读写正常后：DROP TABLE music_old;

判读要点：任务 4 的”还是 99”就是 DDL 隐式提交的现场证据——MySQL 里 BEGIN 后任何 DDL 都会把之前的事务提交，ROLLBACK 只回滚 DDL 之后的语句。这条铁律的完整实验与解释在TCL。

检验清单

能说出 DDL 与 DML 在回滚、锁、审批三个维度的差异；
能默写 ALTER 十连中的加列/改类型/改列名/加外键，并说出 MySQL 与 PG 的写法差异；
知道 MySQL 不支持 DROP COLUMN IF EXISTS（1064）并用 INFORMATION_SCHEMA 写安全删列；
能背出 DROP/TRUNCATE/DELETE 对照表与安全删除四步；
能解释”事务里放 DDL 会隐式提交”并设计实验证明；
能区分 LIKE 与 CTAS 副本的属性差异。

下一步

约束：ALTER 加的那些约束到底怎么守门；
数据类型：改列类型之前，先选对类型；
TCL 事务：隐式提交铁律的完整原理。

参考与致谢

MySQL 8.0 官方文档 Chapter 13.1 DDL Statements、ALTER TABLE（GPLv2 文档许可）：https://dev.mysql.com/doc/refman/8.0/en/alter-table.html
PostgreSQL 官方文档 ALTER TABLE（PostgreSQL Licence）：https://www.postgresql.org/docs/current/sql-altertable.html
变更工作流与安全删列写法整理自仓库扫描素材的踩坑记录（vocaloid 笔记 1-ddl 节）。

表结构变更（DDL 实战）

知识点地图#

心智模型：DDL 与 DML 的风险差异#

建表：一个能直接抄的模板#

ALTER TABLE 全家桶#

变更工作流：大表改列的完整决策#

DROP / TRUNCATE / DELETE：三兄弟对照#

表复制三式#

临时表#

序列#

动手实践：给曲库做一轮”变更演练”#

检验清单#

下一步#

参考与致谢#

知识点地图

心智模型：DDL 与 DML 的风险差异

建表：一个能直接抄的模板

ALTER TABLE 全家桶

变更工作流：大表改列的完整决策

DROP / TRUNCATE / DELETE：三兄弟对照

表复制三式

临时表

序列

动手实践：给曲库做一轮”变更演练”

检验清单

下一步

参考与致谢