如何编写可回滚的数据库变更脚本？

本文目录导读：

1. 核心原则
2. 常见场景的 Rollback 脚本模板
3. 使用迁移框架实现自动化（实战方案）
4. 高级技巧与数据安全
5. 编写清单

编写可回滚的数据库变更脚本（也称为可逆迁移或版本化迁移）是数据库 DevOps 和 CI/CD 的核心实践，其核心思想是：每一次向前变更（Up/Migrate），都必须有一个精确对应的向后回滚脚本（Down/Rollback）。

以下是编写可回滚数据库脚本的详细指南、策略以及代码示例。

核心原则

1. 成对出现：每个变更（如 V1.0.0__add_users_table.sql）必须有一个回滚脚本（如 V1.0.0__add_users_table__rollback.sql）。
2. 幂等性：回滚脚本应该能多次执行而不报错（例如使用 IF EXISTS 和 IF NOT EXISTS）。
3. 原子性：单个脚本的变化（尤其是 DDL）应尽量小而专注，避免将创建表、添加数据、建立索引都放在同一个脚本里,否则回滚颗粒太粗。
4. 严肃对待数据：回滚删除列或表时，如果数据仍在生产环境,需要制定数据备份和恢复策略。
5. 自动化执行：通过迁移工具（如 Flyway、Liquibase、Alembic、golang-migrate）自动管理脚本的顺序和状态。

常见场景的 Rollback 脚本模板

创建/删除表

• Up (V1_create_users.sql):

  CREATE TABLE users (
      id BIGSERIAL PRIMARY KEY,
      username VARCHAR(50) NOT NULL,
      email VARCHAR(255) UNIQUE NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );

• Down (V1_create_users__rollback.sql):

  -- 安全：使用 IF EXISTS 防止重复执行报错
  DROP TABLE IF EXISTS users CASCADE;

添加/删除列

• Up (V2_add_age_to_users.sql):

  ALTER TABLE users ADD COLUMN age INT;
  -- 如果该列有 NOT NULL 约束，需要先设置默认值，再修改约束
  -- ALTER TABLE users ADD COLUMN age INT NOT NULL DEFAULT 0;

• Down (V2_add_age_to_users__rollback.sql):

  -- 1. 备份数据（可选，但强烈推荐）
  -- CREATE TABLE users_age_backup AS SELECT id, age FROM users;
  -- 2. 删除列
  ALTER TABLE users DROP COLUMN IF EXISTS age;

修改列类型（风险最高）

这类操作通常不可直接回滚（因为数据可能已不可逆转换）。

• 策略：分三步走

  1. 新增一个临时列。
  2. 执行数据转换脚本（Up）。
  3. 删除旧列,重命名新列。

• Up (V3_change_column_type.sql):

  -- Step 1: 新增临时列
  ALTER TABLE products ADD COLUMN price_new DECIMAL(10,2);
  -- Step 2: 迁移数据 (字符串转数字)
  UPDATE products SET price_new = price::DECIMAL(10,2) WHERE price IS NOT NULL;
  -- Step 3: 删除旧列，重命名新列
  ALTER TABLE products DROP COLUMN price;
  ALTER TABLE products RENAME COLUMN price_new TO price;

• Down (V3_change_column_type__rollback.sql):

  -- 逆操作：假设原类型为 VARCHAR(50)
  ALTER TABLE products ADD COLUMN price_old VARCHAR(50);
  -- 数据转换 (数字转字符串)，注意：精度不会丢失，但格式可能变化
  UPDATE products SET price_old = price::VARCHAR(50);
  ALTER TABLE products DROP COLUMN price;
  ALTER TABLE products RENAME COLUMN price_old TO price;

添加/删除约束或索引

• Up (V4_add_unique_index.sql):

  CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email);

• Down (V4_add_unique_index__rollback.sql):

  DROP INDEX IF EXISTS idx_users_email;

修改数据（DML 操作）

这类操作最需要谨慎,因为回滚可能会丢失或冲突。

• Up (V5_update_old_status.sql):

  -- 将所有 'inactive' 状态改为 'archived'
  UPDATE orders SET status = 'archived' WHERE status = 'inactive' AND created_at < '2023-01-01';

• Down (V5_update_old_status__rollback.sql):

  -- 注意：回滚时，如果期间有新的 'archived' 被写入，可能会误恢复
  -- 最佳实践：在 Up 脚本中，先将受影响的数据备份到一张历史表
  -- 假设有 backup 表：rollback_orders_20230101
  UPDATE orders o
  SET status = 'inactive'
  FROM rollback_orders_20230101 r
  WHERE o.id = r.id;

使用迁移框架实现自动化（实战方案）

手动管理脚本很麻烦,推荐使用专业工具。

方案 A：Flyway（Java/SQL，也支持其他语言）

• 命名规范：V<版本号>__<描述>.sql 和 U<版本号>__<描述>.sql （Undo 插件需企业版）或 R__<描述>.sql（可重复执行）。
• 普通做法：通常不依赖 Flyway 的内置 Undo（因为企业版收费），而是自己成对管理 V 和 _rollback 文件，在执行回滚时，手动执行对应的 _rollback 脚本。

方案 B：Liquibase（通用，适合大型团队）

• 核心概念：changeset 是原子变更。

• 回滚方式：

  1. 在 XML/YAML/JSON 中显式编写 <rollback>
  2. 或执行 liquibase rollbackCount 1（回滚最近一个 changeset），Liquibase 会自动执行 <rollback> 中的 SQL。
  3. 也可以 liquibase rollbackToDate 2023-01-01。

  <changeSet id="1" author="zhangsan">
      <createTable tableName="users">
          <column name="id" type="bigint" autoIncrement="true"/>
          <column name="name" type="varchar(50)"/>
      </createTable>
      <rollback>
          <!-- 明确的回滚指令 -->
          DROP TABLE IF EXISTS users;
      </rollback>
  </changeSet>

-- Up: V1_create_table.sql
BEGIN;
CREATE TABLE users (...);
COMMIT;
-- Down: V1_create_table_rollback.sql
BEGIN;
DROP TABLE IF EXISTS users;
COMMIT;

-- Up: V6_remove_duplicate_emails.sql
-- 1. 备份将被删除的记录
CREATE TABLE rollback_v6_remove_dupes AS
SELECT * FROM users
WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);
-- 2. 执行删除
DELETE FROM users
WHERE id IN (SELECT id FROM rollback_v6_remove_dupes);
-- Down: V6_remove_duplicate_emails__rollback.sql
INSERT INTO users (SELECT * FROM rollback_v6_remove_dupes);
DROP TABLE IF EXISTS rollback_v6_remove_dupes;