数据库原理与选型 / 系统课时

SQL 导出、恢复与数据迁移

SQL 导出、恢复与数据迁移

数据库文件备份适合同版本环境快速恢复,但跨环境迁移时,SQL 文本往往更容易检查、传输和版本化。本课使用 sqlite3 的 .dump 导出建表语句与数据,再恢复到一个全新的数据库,并重点处理对象创建顺序和外键校验。

学习目标

  • 使用 .dump 导出可重新执行的数据库结构与数据
  • 将 SQL 导出文件恢复到新的 SQLite 数据库
  • 在迁移后使用外键检查和关键查询发现不完整数据

SQL 导出包含什么

sqlite3 的 .dump 会把数据库转换成一组可执行 SQL,通常包含表、索引和其他模式对象的定义,以及用于重建数据的 INSERT 语句。与直接复制数据库文件相比,SQL 导出更接近逻辑迁移:目标数据库通过重新执行语句被构建出来。

这种方式的代价也很明确。大型数据库的导出与恢复通常比文件级备份慢,而且恢复结果会受到 SQL 是否完整、目标 SQLite 版本以及对象依赖关系的影响。因此 SQL dump 更适合迁移、审阅和逻辑恢复,而不是替代所有文件级备份。

完成一次导出和恢复

下面创建两个存在外键关系的表,导出完整数据库,再把 SQL 恢复到新的文件中。最后同时检查记录数量和外键完整性。

bash
rm -f source.db target.db backup.sql
sqlite3 source.db "PRAGMA foreign_keys=ON; CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE orders(id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), amount INTEGER NOT NULL); INSERT INTO users VALUES(1, 'Alice'); INSERT INTO orders VALUES(10, 1, 500);"
sqlite3 source.db ".dump" > backup.sql
sqlite3 target.db < backup.sql
sqlite3 target.db "SELECT COUNT(*) FROM users; SELECT COUNT(*) FROM orders; PRAGMA foreign_key_check;"

预期输出:

text
1
1

最后的 PRAGMA foreign_key_check 在没有问题时不返回数据,因此输出只有两个计数。如果它额外打印了行,就意味着存在违反外键约束的记录,需要先修复数据再把迁移结果投入使用。

处理迁移顺序与外键

完整 .dump 已经按照可恢复数据库的目标生成 SQL,因此恢复整个 dump 时通常不需要手工重新排列每条语句。风险更多出现在只迁移部分表、人工拆分 SQL,或者自行编写迁移脚本的场景。

手工迁移有外键关系的数据时,推荐遵守以下顺序:

  1. 先创建被引用的父表,再创建引用它们的子表。
  2. 如果保持外键检查开启,先插入父表记录,再插入子表记录。
  3. 如果迁移过程必须暂时关闭外键检查,完成全部导入后重新启用检查,并执行 PRAGMA foreign_key_check。
  4. 最后再验证索引、触发器、视图以及关键业务查询,而不是只比较数据库文件大小。

检查导出的 SQL

SQL dump 是普通文本,因此可以在恢复前检查它是否包含关键表定义和数据。对于一次迁移,至少应确认导出文件非空,并确认目标对象确实出现在脚本中。

bash
wc -l backup.sql
grep "CREATE TABLE users" backup.sql
grep "CREATE TABLE orders" backup.sql

预期结果示例:

text
<VALUE> backup.sql
CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders(id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), amount INTEGER NOT NULL);

<VALUE> 表示实际行数,不同 sqlite3 版本和数据库内容可能不同。这里关心的不是固定行数,而是导出文件存在且包含需要恢复的模式对象。

把恢复验证作为迁移闸门

迁移成功不能只用“sqlite3 没有报错”判断。至少应检查目标数据库能够正常打开、关键表行数符合预期、PRAGMA foreign_key_check 没有返回异常,并执行一两条能够覆盖实际关联关系的业务查询。

SQL 导出最大的优势是透明:结构和数据都能被阅读与审查;它最大的风险也来自这种逻辑重建过程——任何遗漏、错误顺序或约束处理不当都会进入新数据库。因此可靠的迁移流程应始终是“导出、恢复到新文件、独立验证、再切换”,而不是直接把未经验证的 dump 当作最终成果。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

SQL 导出、恢复与数据迁移

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通