⚡

数据库迁移最佳实践 database-migrations

本技能提供数据库迁移的完整指南，涵盖PostgreSQL、MySQL及主流ORM（如Prisma、Django、TypeORM）下的安全模式变更、数据迁移、零停机部署和回滚策略。核心内容包括迁移原则、安全检查清单、具体SQL示例、各工具工作流以及避免生产事故的反模式。适用于后端开发、DevOps工程师和数据库管理员，确保数据库演进过程可靠、可逆且不影响服务可用性。关键词：数据库迁移，模式变更，零停机部署，数据回填，迁移回滚，ORM迁移，生产环境安全，DDL最佳实践。

后端开发 0 次安装 0 次浏览 更新于 2/27/2026

name: 数据库迁移 description: 针对PostgreSQL、MySQL及常用ORM（Prisma、Drizzle、Django、TypeORM、golang-migrate）的数据库迁移最佳实践，涵盖模式变更、数据迁移、回滚和无停机部署。

数据库迁移模式

用于生产系统的安全、可逆的数据库模式变更。

何时使用

  • 创建或修改数据库表
  • 添加/删除列或索引
  • 运行数据迁移（回填、转换）
  • 规划无停机模式变更
  • 为新项目设置迁移工具

核心原则

  1. 每个变更都是一次迁移 — 切勿手动修改生产数据库
  2. 迁移在生产环境中是单向的 — 回滚使用新的正向迁移
  3. 模式迁移和数据迁移是分开的 — 切勿将DDL和DML混在一次迁移中
  4. 针对生产规模的数据测试迁移 — 在100行上有效的迁移可能在1000万行上锁定
  5. 迁移一旦部署就不可变 — 切勿编辑已在生产环境中运行的迁移

迁移安全检查清单

在应用任何迁移之前：

  • [ ] 迁移同时包含UP和DOWN（或明确标记为不可逆）
  • [ ] 对大表没有全表锁（使用并发操作）
  • [ ] 新列有默认值或可为空（切勿在没有默认值的情况下添加NOT NULL）
  • [ ] 索引是并发创建的（对于现有表，不与CREATE TABLE内联创建）
  • [ ] 数据回填与模式变更分开进行迁移
  • [ ] 已针对生产数据副本进行测试
  • [ ] 回滚计划已记录

PostgreSQL 模式

安全地添加列

-- 良好：可为空的列，无锁
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- 良好：带默认值的列（Postgres 11+ 是瞬时的，无需重写）
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

-- 不良：对现有表添加没有默认值的NOT NULL列（需要完全重写）
ALTER TABLE users ADD COLUMN role TEXT NOT NULL;
-- 这会锁定表并重写每一行

无停机添加索引

-- 不良：在大表上阻塞写入
CREATE INDEX idx_users_email ON users (email);

-- 良好：非阻塞，允许并发写入
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- 注意：CONCURRENTLY 不能在事务块内运行
-- 大多数迁移工具需要对此进行特殊处理

重命名列（无停机）

切勿在生产中直接重命名。使用扩展-收缩模式：

-- 步骤1：添加新列（迁移001）
ALTER TABLE users ADD COLUMN display_name TEXT;

-- 步骤2：回填数据（迁移002，数据迁移）
UPDATE users SET display_name = username WHERE display_name IS NULL;

-- 步骤3：更新应用程序代码以读写两列
-- 部署应用程序更改

-- 步骤4：停止写入旧列，删除它（迁移003）
ALTER TABLE users DROP COLUMN username;

安全地删除列

-- 步骤1：移除所有应用程序对该列的引用
-- 步骤2：部署不引用该列的应用程序
-- 步骤3：在下一次迁移中删除列
ALTER TABLE orders DROP COLUMN legacy_status;

-- 对于Django：使用SeparateDatabaseAndState从模型中移除
-- 而不生成DROP COLUMN（然后在下次迁移中删除）

大型数据迁移

-- 不良：在一个事务中更新所有行（锁定表）
UPDATE users SET normalized_email = LOWER(email);

-- 良好：分批更新并显示进度
DO $$
DECLARE
  batch_size INT := 10000;
  rows_updated INT;
BEGIN
  LOOP
    UPDATE users
    SET normalized_email = LOWER(email)
    WHERE id IN (
      SELECT id FROM users
      WHERE normalized_email IS NULL
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    RAISE NOTICE '已更新 % 行', rows_updated;
    EXIT WHEN rows_updated = 0;
    COMMIT;
  END LOOP;
END $$;

Prisma (TypeScript/Node.js)

工作流程

# 根据模式更改创建迁移
npx prisma migrate dev --name add_user_avatar

# 在生产环境中应用待处理的迁移
npx prisma migrate deploy

# 重置数据库（仅限开发）
npx prisma migrate reset

# 模式更改后生成客户端
npx prisma generate

模式示例

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  avatarUrl String?  @map("avatar_url")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  orders    Order[]

  @@map("users")
  @@index([email])
}

自定义SQL迁移

对于Prisma无法表达的操作（并发索引、数据回填）：

# 创建空迁移，然后手动编辑SQL
npx prisma migrate dev --create-only --name add_email_index

-- migrations/20240115_add_email_index/migration.sql
-- Prisma无法生成CONCURRENTLY，因此我们手动编写
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email);

Drizzle (TypeScript/Node.js)

工作流程

# 根据模式更改生成迁移
npx drizzle-kit generate

# 应用迁移
npx drizzle-kit migrate

# 直接推送模式（仅限开发，不生成迁移文件）
npx drizzle-kit push

模式示例

import { pgTable, text, timestamp, uuid, boolean } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").notNull().unique(),
  name: text("name"),
  isActive: boolean("is_active").notNull().default(true),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

Django (Python)

工作流程

# 根据模型更改生成迁移
python manage.py makemigrations

# 应用迁移
python manage.py migrate

# 显示迁移状态
python manage.py showmigrations

# 为自定义SQL生成空迁移
python manage.py makemigrations --empty app_name -n description

数据迁移

from django.db import migrations

def backfill_display_names(apps, schema_editor):
    User = apps.get_model("accounts", "User")
    batch_size = 5000
    users = User.objects.filter(display_name="")
    while users.exists():
        batch = list(users[:batch_size])
        for user in batch:
            user.display_name = user.username
        User.objects.bulk_update(batch, ["display_name"], batch_size=batch_size)

def reverse_backfill(apps, schema_editor):
    pass  # 数据迁移，无需反向操作

class Migration(migrations.Migration):
    dependencies = [("accounts", "0015_add_display_name")]

    operations = [
        migrations.RunPython(backfill_display_names, reverse_backfill),
    ]

SeparateDatabaseAndState

从Django模型中移除列，而不立即从数据库中删除：

class Migration(migrations.Migration):
    operations = [
        migrations.SeparateDatabaseAndState(
            state_operations=[
                migrations.RemoveField(model_name="user", name="legacy_field"),
            ],
            database_operations=[],  # 暂时不触及数据库
        ),
    ]

golang-migrate (Go)

工作流程

# 创建迁移对
migrate create -ext sql -dir migrations -seq add_user_avatar

# 应用所有待处理的迁移
migrate -path migrations -database "$DATABASE_URL" up

# 回滚最后一次迁移
migrate -path migrations -database "$DATABASE_URL" down 1

# 强制版本（修复脏状态）
migrate -path migrations -database "$DATABASE_URL" force VERSION

迁移文件

-- migrations/000003_add_user_avatar.up.sql
ALTER TABLE users ADD COLUMN avatar_url TEXT;
CREATE INDEX CONCURRENTLY idx_users_avatar ON users (avatar_url) WHERE avatar_url IS NOT NULL;

-- migrations/000003_add_user_avatar.down.sql
DROP INDEX IF EXISTS idx_users_avatar;
ALTER TABLE users DROP COLUMN IF EXISTS avatar_url;

无停机迁移策略

对于关键的生产变更，遵循扩展-收缩模式：

阶段1：扩展
  - 添加新列/表（可为空或带默认值）
  - 部署：应用程序同时写入新旧两列
  - 回填现有数据

阶段2：迁移
  - 部署：应用程序从新列读取，同时写入新旧两列
  - 验证数据一致性

阶段3：收缩
  - 部署：应用程序仅使用新列
  - 在单独的迁移中删除旧列/表

时间线示例

第1天：迁移添加new_status列（可为空）
第1天：部署应用程序v2 — 同时写入status和new_status
第2天：为现有行运行回填迁移
第3天：部署应用程序v3 — 仅从new_status读取
第7天：迁移删除旧的status列

反模式

反模式 失败原因 更好的方法
在生产环境中手动执行SQL 无审计跟踪，不可重复 始终使用迁移文件
编辑已部署的迁移 导致环境间差异 创建新的迁移
没有默认值的NOT NULL 锁定表，重写所有行 添加可为空列，回填，然后添加约束
在大表上内联创建索引 构建期间阻塞写入 CREATE INDEX CONCURRENTLY
将模式和数据放在一次迁移中 难以回滚，事务时间长 分开迁移
在移除代码前删除列 应用程序因缺少列而报错 先移除代码，下次部署再删除列