mysqldump和pg_dump分离导出结构与数据需注意隐性依赖、类型兼容性及执行顺序:MySQL需处理外键、字符集、引擎;PostgreSQL需适配扩展、权限、schema路径;跨库迁移须手动校验并替换不兼容类型与语句。
MySQL 用 mysqldump 分离导出结构和数据
直接加 --no-data 或 --no-create-info 就能切开,但容易漏掉外键、字符集、存储引擎这些隐性依赖。默认导出的 create table 语句里带 engine=innodb default charset=utf8mb4,如果目标库版本低或没开对应插件,还原时会报 unknown storage engine 'innodb' 或 unsupported charset。
-
mysqldump -u root -p --no-data --skip-triggers --skip-routines db_name table1 table2 > schema.sql:跳过触发器和存储过程,避免跨库调用失败 -
mysqldump -u root -p --no-create-info --skip-triggers --skip-routines db_name table1 table2 > data.sql:不带建表语句,也别导视图或临时表(mysqldump默认不导视图,但加了--all-databases就可能混进去) - 如果表有外键,先在
schema.sql开头加SET FOREIGN_KEY_CHECKS=0;,结尾加SET FOREIGN_KEY_CHECKS=1;,否则导入时可能因顺序错乱失败
PostgreSQL pg_dump 按需拆分结构与数据
pg_dump 的 --schema-only 和 --data-only 看似干净,但默认包含 CREATE EXTENSION 和权限语句,而目标库未必装了 uuid-ossp 或开了 pg_trgm。更麻烦的是,pg_dump --data-only 仍会输出 COPY 命令里的表名全路径(比如 "public"."users"),若目标库 schema 名不同,就得提前 sed 替换。
-
pg_dump -U postgres -d mydb --schema-only --no-owner --no-privileges --exclude-table-data='*' > schema.sql:排除所有表数据,且去掉 owner 和权限,避免还原时报role "xxx" does not exist -
pg_dump -U postgres -d mydb --data-only --inserts --column-inserts --disable-triggers --exclude-table='log_*' > data.sql:用INSERT代替COPY更兼容,--disable-triggers防止导入中途触发逻辑错误 - 注意
--exclude-table-data和--exclude-table参数行为不同:前者只跳过数据,表结构还在;后者连结构都不要
跨库迁移时字段类型兼容性要手动核对
MySQL 的 TINYINT(1) 常被 ORM 当布尔用,但 PostgreSQL 没原生布尔 tinyint,pg_dump 会转成 smallint;反过来,PostgreSQL 的 JSONB 导进 MySQL 5.7+ 才支持 JSON 类型,旧版只能当 TEXT 存——这时 data.sql 里的 jsonb_build_object 函数会直接报错。
- 导出前先查源库字段定义:
SELECT column_name, data_type, character_maximum_length FROM information_schema.columns WHERE table_name = 'xxx'; - 目标库不支持的类型,得在
schema.sql里手动替换,比如把JSONB改成JSON,把GENERATED ALWAYS AS虚拟列改成普通列 + 应用层计算 - 时间类型尤其危险:
TIMESTAMP WITH TIME ZONE在 MySQL 里没有等价物,硬导会导致时区偏移,建议统一转成TIMESTAMP WITHOUT TIME ZONE再导
SQL 文件里隐藏的执行顺序陷阱
结构文件里如果混了 INSERT INTO xxx SELECT ... 这类依赖其他表的语句,或者数据文件开头有 UPDATE 而不是纯 INSERT,还原时必然失败——因为表刚建完还没数据,或者外键约束还没关。
- 用
grep -n "INSERT\|UPDATE\|DELETE" data.sql | head -5快速扫一遍数据文件是否“干净” - 结构文件里搜
SELECT和INSERT INTO.*SELECT,这类语句必须挪到数据文件末尾,且确保所查表已存在、已有数据 - 如果用了分区表,
mysqldump默认不导分区定义(需加--all-databases或显式指定--tables),但pg_dump会导,得确认目标库版本是否支持相同分区策略
最麻烦的不是导不出,而是导出了看着没问题,还原一半才崩——因为跨库时默认字符集、SQL mode、autocommit 设置不同,这些不会写进 SQL 文件,得靠人工补或脚本预设。










