数据库结构迁移：从手工SQL到可回滚流程

  1.  Home
  2. Text  
    3. Share to：

一、为什么"改表结构"是最容易出事的一次上线

写代码出 bug，最坏的结果是一个页面报错或者某个功能失灵，回滚一次发版就能恢复。改数据库表结构不一样：代码可以 git revert，数据改错了、字段删掉了、类型转换精度丢了，就不是一次回滚能救回来的。而个人站长的站点有个特点——很少有完整的测试环境，往往是"本地改好、直接传到线上、当场跑"，一旦 ALTER TABLE 执行到一半失败，站就挂在那儿。

更麻烦的是，自建站的表结构从来不是"一次设计、永久不变"。半年后加一个字段、一年后给某个字段建索引、两年后发现某张表设计错了要拆，这些都是必然发生的。如果每次都是手工上服务器敲 SQL，那么这些操作没有任何记录、没有回滚方案、也没法在换服务器时重放一遍。所以真正要建立的不是"更小心地敲 SQL"，而是一套可重复执行、可回滚、有记录的迁移流程。

下面这套流程是按个人站长的实际条件设计的：一台服务器、一个 PHP 应用、没有专业的 DBA、没有 CI 流水线也能跑起来。

二、先分清三类变更：加、改、拆

数据库变更的风险等级差别极大，处理方式也完全不同。绝大多数事故都出在"把三类当成一类处理"。

第一类：只增不改（最安全）

新增表、新增字段（且允许 NULL 或有默认值）、新增索引。这类变更通常不影响正在运行的旧代码，因为旧代码不知道新字段的存在，照常增删改查。这是唯一可以"先上数据库、后上代码"的类型。

MySQL 8.0 里新增字段如果是 ALGORITHM=INSTANT（加在表末尾、不改变行格式），几乎是瞬间完成的，不锁表。写成这样更保险：

ALTER TABLE typecho_contents
  ADD COLUMN view_count INT UNSIGNED NOT NULL DEFAULT 0,
  ALGORITHM=INSTANT, LOCK=NONE;

注意 ALGORITHM=INSTANT 只在特定条件下可用，MySQL 会报错告诉你不可用（而不是悄悄降级成拷贝表），这正好是一种保护。MySQL 5.7 没有 INSTANT，加字段要重建表，大表上会很慢，见第四节。

第二类：改变现有语义（中等风险）

改字段类型、改字符集、加 NOT NULL 约束、加唯一索引。风险在于旧代码可能依赖原来的类型或允许空值。比如把 VARCHAR(20) 改成 VARCHAR(255) 是安全的，反过来就可能截断数据；把允许为空的字段改成 NOT NULL，如果库里已经有 NULL，语句会失败。

这类变更必须代码与结构同时上线，或者分两步走：先让代码同时兼容新旧两种形态，再改结构，最后清理兼容代码。这个过程看着啰嗦，但它是唯一能保证"任何一刻出事都能回滚"的方式。

第三类：删除或重命名（最危险）

删字段、删表、改字段名。这类变更一旦执行就无法撤销，因为数据没了。原则很简单：永远不要在同一次发布里删字段。

正确做法是分三次发布。第一次：代码停止读写该字段（但字段还在）。第二次：观察一周，确认没有地方用到（看慢查询日志、看应用日志里有没有相关报错）。第三次：才真正 DROP COLUMN，而且删之前先 mysqldump 一张只含该字段的表做冷备。

重命名字段同理，用"新增新字段 → 代码双写 → 回填历史数据 → 代码只读新字段 → 删旧字段"这个五步流程。RENAME COLUMN 看着很方便，但在分布式或者有缓存的场景下，改名的瞬间就会有一批请求打到不存在的列上。

三、迁移文件：一个目录加一张记录表

迁移系统的核心非常简单，只有两个要素：按顺序编号的 SQL 文件，和一张记录哪些已经执行过的表。不需要引入任何框架。

CREATE TABLE IF NOT EXISTS schema_migrations (
  version    VARCHAR(64) NOT NULL PRIMARY KEY,
  applied_at DATETIME NOT NULL,
  checksum   CHAR(64) NOT NULL,
  duration_ms INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

迁移文件放在项目里的 migrations/ 目录，命名用"时间戳 + 描述"，这样天然有序，多人协作也不会撞号：

migrations/
  20260115_0001_add_view_count_to_contents.sql
  20260203_0002_create_visit_log.sql
  20260310_0003_add_index_on_visit_log_created.sql

执行器要做的事只有四步：扫描目录、按版本号排序、查出 schema_migrations 里已有的版本、对没执行过的逐个执行并插入记录。PHP 写出来不到五十行：

$files = glob(__DIR__ . '/migrations/*.sql');
sort($files, SORT_STRING);
$applied = $db->query('SELECT version FROM schema_migrations')
              ->fetchAll(PDO::FETCH_COLUMN);
foreach ($files as $f) {
    $ver = basename($f, '.sql');
    if (in_array($ver, $applied, true)) { continue; }
    $sql = file_get_contents($f);
    $t0  = microtime(true);
    $db->exec($sql);                       // 见下面"多语句"的坑
    $db->prepare('INSERT INTO schema_migrations VALUES (?,NOW(),?,?)')
       ->execute([$ver, hash('sha256', $sql), (int)((microtime(true)-$t0)*1000)]);
    echo "applied $ver\n";
}

checksum 这一列非常重要，它记录的是文件内容的哈希。已经执行过的迁移文件绝不允许再修改——如果哪天你发现某个迁移写错了想改，校验和会对不上，执行器应当报错提醒你"这个文件改过了"。这不是刁难，而是保证"任何一台机器上跑一遍，结果都一样"的唯一办法。发现写错了，正确的做法是新加一个迁移去修正，而不是回头改旧的。

duration_ms 是给未来自己看的：哪天发现某次迁移花了 40 秒，就知道那张表已经大到需要用别的策略了。

四、大表 ALTER：MySQL 5.7 上的麻烦

MySQL 5.7 及更早版本上，ALTER TABLE 很多操作是"拷贝整表"：建一个影子表、把数据逐行复制过去、再改名替换。一张一千万行的表可能锁表几十分钟，期间所有写入都排队，站点直接卡死。

最稳的方案是 pt-online-schema-change（Percona Toolkit 里的工具），它的原理是：建一个结构一致的空表、在原表上挂三个触发器把增量同步到新表、分批把老数据拷过去、最后原子改名。整个过程原表可读可写，只在最后 rename 时锁一下：

pt-online-schema-change --alter "ADD COLUMN related_ids TEXT NULL" \
  D=zz1984,t=typecho_contents \
  --alter-foreign-keys-method auto \
  --max-load Threads_running=25 \
  --critical-load Threads_running=60 \
  --chunk-time 0.3 \
  --print --dry-run                    # 先 dry-run，确认无误再去掉

--max-load 和 --critical-load 是保护机制：线程数超过上限就自动暂停、超过临界值就中止。--chunk-time 0.3 让每个批次控制在 0.3 秒内，避免长事务。先 dry-run 是必须的，它会打印将要执行的 SQL 而不真正执行。

用这个工具前要确认三件事：表上不能已经有同名触发器；不能有外键依赖（或者要用 --alter-foreign-keys-method 处理）；磁盘剩余空间要够放一份完整的表副本。第三点最容易被忽略，拷到一半磁盘满了，是这类操作里最难收拾的场面。

MySQL 8.0 好很多，加字段、加索引大多支持 ALGORITHM=INPLACE 或 INSTANT，但仍然要确认：ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE 如果能执行成功就是在线变更，报错就是需要拷贝表，此时再考虑 pt-online-schema-change。写迁移时把所有 ALTER 都显式加上 ALGORITHM=INPLACE, LOCK=NONE，让数据库替你判断"这条能不能在线做"，比靠经验猜靠谱得多。

五、索引迁移的三个坑

加索引看似最简单，实际上最容易被忽略它的代价。第一个坑是重复索引。如果已经有 (a, b) 的联合索引，再建一个 (a) 的单列索引就是完全多余的——最左前缀已经覆盖。清理前先用 SHOW INDEX FROM 表名 把现有索引列出来，或者用 sys.schema_redundant_indexes 视图（MySQL 5.7+ 自带）自动找冗余：

SELECT * FROM sys.schema_redundant_indexes\G

第二个坑是索引命名没规范。手工建的索引用 MySQL 自动生成的 idx_1、idx_2，时间长了没人知道哪个对应哪个查询。统一用 idx_表名_字段名 的命名，删的时候才能确认删对了。

第三个坑是加索引时的元数据锁。即使 ALGORITHM=INPLACE，ALTER 开始和结束时仍需要短暂的元数据锁（MDL）。如果此刻正好有一个长事务在跑（比如一个没提交的 SELECT ... FOR UPDATE），ALTER 会等待这个事务，而等待期间所有新来的查询都排在 ALTER 后面，于是整站卡死。这是 MySQL 里最经典的"改索引把站搞挂"事故。

所以执行任何 ALTER 之前，先看一眼长事务：

SELECT trx_id, trx_started, trx_mysql_thread_id,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS secs, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started LIMIT 5;

有超过 10 秒的事务就先处理它。另外可以设一个较低的 lock_wait_timeout（会话级，比如 10 秒），让 ALTER 拿不到锁时快速失败，而不是无限等待拖垮全站。

六、上线前的四个动作

迁移执行前有四个动作，每一个都只要几十秒，但能救你一次：

  • 备份要精确到表。整库备份动辄几 G，恢复时太慢。只备份将被修改的那张表：mysqldump -uroot -p --single-transaction zz1984 typecho_contents > /backup/contents_before.sql。--single-transaction 保证 InnoDB 表在备份期间不锁表且数据一致。
  • 先看表有多大。SELECT table_rows, ROUND(data_length/1024/1024) AS data_mb, ROUND(index_length/1024/1024) AS idx_mb FROM information_schema.tables WHERE table_schema='zz1984' AND table_name='typecho_contents'; 行数是估算值，但量级够用。超过 500 万行就别直接 alter。
  • 在影子库上跑一遍。用 mysqldump 把表导到本地或另一台机器，导入后跑一遍迁移文件，确认语法没问题、耗时能接受。这一步能发现 90% 的语法与约束问题。
  • 写好回滚语句。加字段的回滚是 DROP COLUMN（但要注意数据丢了），加索引的回滚是 DROP INDEX，改类型的回滚是新加一个迁移改回去。回滚脚本要在执行前就写好放在文件里，不能"出事了再想"。

执行时机也要选。公众号、资讯站有明确的流量分布，凌晨三点的写入量可能只有中午的十分之一。选在低峰期执行长事务，风险会小很多。执行期间开一个窗口跑 SHOW PROCESSLIST 或 SHOW ENGINE INNODB STATUS，看着线程堆积情况，超过阈值就 KILL 掉 ALTER（ALTER 被 kill 会回滚，但也会耗时，所以这只是紧急止血）。

七、字符集与排序规则：最容易埋雷的一类迁移

自建站最常见的一次"莫名其妙的数据丢失"来自字符集迁移。把 utf8 改成 utf8mb4 这件事本身是对的——utf8 在 MySQL 里其实是三字节的 utf8mb3，存不了 emoji 和部分生僻字——但改法有讲究。

整表转换用 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci，它会同时改字段定义与索引。大表上这是一次完整的表重建，必须用 pt-online-schema-change。更重要的是连接字符集也要改：应用连接串里要写 charset=utf8mb4，否则字段虽然是 utf8mb4，写入时连接层仍按三字节处理，遇到四字节字符照样截断——而且不报错，后端内容会被静默切断，前半段写进去、后半段消失。

这种"写入时不报错但内容变短"的现象，在自建站里出过很多次事故：一篇文章突然只发出去一半，或者某个标题后面的内容全没了。判断方法是对比"提交内容长度"与"库里读回来的长度"，不一致就说明链路里丢了字节。排查顺序是：应用连接字符集 → 字段字符集 → 排序规则。三处必须一致，且都是 utf8mb4。

排序规则的坑小一些但也要注意。utf8mb4_general_ci 和 utf8mb4_unicode_ci 在中文排序上结果不同，后者更符合 Unicode 标准；utf8mb4_0900_ai_ci 是 MySQL 8.0 的默认值，5.7 不支持。同一个库里混用多个排序规则会导致 JOIN 时索引失效（因为比较前要转换），所以要么全库统一，要么明确知道哪张表用哪个。

八、换服务器时如何重放全部历史

迁移系统还有一个巨大好处：迁移服务器变得非常简单。新机器上建好空库，装好代码，跑一次迁移执行器，所有的表、字段、索引、初始数据都按历史顺序重放出来，结果与线上完全一致。这比"导出旧库、导入新库"可靠得多——后者依赖旧库当下的物理状态，而且在旧库上手工改过的、没记录的东西，一旦换机器就丢了。

重放时的顺序要注意：先跑迁移建结构，再导数据。如果先导数据，数据里可能带旧结构，再跑迁移就会撞车。对于已经开始产生数据的迁移（比如"给所有文章回填 view_count 默认值"），要区分两种写法：一种只改结构（ADD COLUMN），重放时安全；另一种会在迁移里写数据（UPDATE ... SET view_count = 0），这种应该拆出去，单独作为"数据初始化"脚本，不放进迁移。原则是：迁移只改结构，数据修正走单独的、幂等的脚本。

数据脚本要幂等，意思是跑一次和跑十次结果一样。判据很简单：UPDATE ... WHERE 条件 里的条件能不能让第二次执行影响 0 行。能就是幂等的，不能就要重新想。

九、五个真实踩过的坑

  • 多语句执行被截断。PDO 的 exec() 默认只执行一条语句，一个迁移文件里写了三条 ALTER，结果只跑了第一条，后面两条悄悄跳过，而 schema_migrations 已经记了"执行过"。解决办法是一个文件只放一条语句（最推荐），或者用 PDO::MYSQL_ATTR_MULTI_STATEMENTS 并逐条 nextRowset()，或者干脆用 mysql 命令行执行整个文件。
  • 默认值写成函数。DEFAULT NOW() 在 MySQL 5.7 之前的 DATETIME 字段上不合法，只有 TIMESTAMP 支持。迁移执行时报错，但迁移记录没写进去，下次跑又撞同样的错。所以要"先 dry-run 或先在影子库跑"。
  • NOT NULL 加在已有数据上。ADD COLUMN x INT NOT NULL（无默认值）在严格模式下会失败或者给旧行塞 0。规范写法是 NOT NULL DEFAULT 0，把两个都写上。
  • 迁移与代码不同步。代码已经查 view_count 字段，迁移还没跑，线上直接 SQL 报错全站白屏。解决办法是迁移先于代码部署（这要求迁移是"只增不改"的类型），或者上代码时先做兼容判断。这就是为什么第一类变更最安全——它天然可以先行。
  • 忘了清理测试数据。在本地跑迁移时插入的测试数据跟着迁移文件一起上了线。所以迁移文件里的 INSERT 语句要极其谨慎，最好一条都没有。

十、最小可用的落地清单

如果你的站现在还是"手工敲 SQL"，那么用半天时间把这套东西搭起来，收益会比你想的大：

  • 建 migrations/ 目录与 schema_migrations 表（见第三节建表语句）。
  • 写一个 50 行的执行器，支持三个参数：默认执行待执行迁移、--dry-run 只打印、--status 列出已执行与待执行。
  • 把最近半年手工执行过的 SQL 整理成迁移文件，按时间顺序编号，一次性 INSERT 进 schema_migrations 标成已执行（不算真正执行，只补齐记录）。这一步让历史变得可追溯。
  • 在发版流程里加一步：部署代码前先跑迁移。哪怕只是一个人维护，也写进发版脚本里，不要靠记忆。
  • 每个迁移文件开头写一行注释：这次改什么、为什么改、风险等级、回滚方式是什么。半年后回来看，这行注释比代码本身更有价值。

数据库迁移这件事，本质上不是技术问题，而是流程问题。技术和工具都现成，难的是"每次都走流程"。而流程一旦建立起来，你会发现自己敢改表了——以前不敢碰的东西现在敢碰，因为你知道最坏的情况怎么退回去。这才是迁移系统真正的价值。

Last modification：November 1st, 2026 at 01:26 pm
© 允许规范转载

相关文章

Leave a Comment