用Navicat把MySQL的数据搬到PostgreSQL其实是最快也最容易让人误以为已经完工的一步。点几下鼠标表建好了数据也过去了但等你真正开始跑业务的时候报错一条接一条主键冲突、表不存在、类型对不上……这不是Navicat不行而是这种图形化传输工具天生只解决“搬过去”的问题不解决“语义还原”的问题。我这个笔记就是围绕迁移完成之后的后续处理展开的适合刚用Navicat完成MySQL到PostgreSQL迁移、正在被一堆报错折腾的开发者也适合准备做迁移、想提前知道哪里有雷的人。1. 为什么迁移完才是真正的开始1.1 Navicat迁移实际做了哪些事情很多人以为“数据传输”就是把MySQL里的表结构、数据原封不动复制到PostgreSQL里其实不是。Navicat内部的逻辑大致是这样的先读MySQL的表结构元数据然后做一层有限的类型映射生成建表语句再把数据逐条插入到PostgreSQL。它能带过去的对象主要是表、字段、索引、主键外键这些基础元素但很多数据库特有的语义会在转换过程中丢失。MySQL里的AUTO_INCREMENT在Navicat转换后多半只是变成普通的整型字段不会自动创建PostgreSQL里的序列MySQL里的enum类型转过去通常直接变成varchar枚举约束全部丢失MySQL里datetime字段的默认值CURRENT_TIMESTAMP到PostgreSQL这边默认值可能就没了视图、存储过程、触发器这类对象Navicat基本不会帮你搬就算搬了语法也不兼容。所以迁移完成后千万不要把Navicat的日志“传输成功”当成真正的成功。它只是完成了“数据搬家”而后续的语义修复、对象重建、类型校验才是真正决定项目能不能跑起来的关键。1.2 迁移后最容易翻车的三个问题从我的实操经验来看迁移后最先暴露的问题通常集中在三块。第一块是自增主键。MySQL里插入数据可以不写主键数据库自动生成但PostgreSQL里如果没有正确绑定序列插入语句就会报主键为空的错误或者主键重复。这个问题几乎每个迁移项目都会遇到而且往往要等业务代码真正写入时才爆发。第二块是表名和字段名的大小写引号问题。Navicat在生成PostgreSQL表结构时对标识符的处理策略会给表名和字段名加上双引号原样保留大小写。这样导致的结果就是你在PostgreSQL里执行SELECT * FROM Order会报错“relation order does not exist”必须写成SELECT * FROM Order。如果业务代码里到处都是不加引号的SQL那基本就是全线报错。第三块是类型语义的悄悄变化。MySQL的tinyint(1)转过去变成smallint以前按布尔用的字段到了PostgreSQL变成整数MySQL允许的0000-00-00日期PostgreSQL直接不认字符串比较的大小写规则也变了。这些坑不是那种一眼能看出来的报错而是数据对了、逻辑却不对了特别隐蔽。2. 先做一轮全库“体检”2.1 表级核对数量、行数、主键迁移完成后我建议你先别急着写业务先用最笨的办法把全库核对一遍。表数量的对比可以直接用系统表查。在PostgreSQL里执行SELECT count(*) FROM information_schema.tables WHERE table_schema public;然后去MySQL里执行SHOW TABLES;数一下数量。数量对不上说明传输时有表被跳过或者失败先去Navicat的日志里找报错记录把失败的表单独重新传输。表数量对齐了接下来逐表核对行数。Navicat传输在大表上偶尔会因为类型转换失败、字段超长导致部分数据没有插入但它不会整体失败而是记录错误后继续。所以行数对比不能省。PostgreSQL里如果表太多可以写一个小查询批量生成count语句SELECT SELECT || tablename || AS tbl, count(*) FROM || tablename || ; FROM pg_tables WHERE schemaname public;把生成的结果去执行再把每张表的结果和MySQL对比一遍。这个步骤看着笨但在迁移项目中是最能兜底的。另外还要检查主键和唯一约束有没有完整迁移过来SELECT tc.table_name, kcu.column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name kcu.constraint_name AND tc.table_schema kcu.table_schema WHERE tc.constraint_type PRIMARY KEY AND tc.table_schema public ORDER BY tc.table_name;对照MySQL那边的主键信息缺失的补上。这一步别拖主键丢了轻则影响性能重则导致数据重复写入。2.2 类型与默认值逐列排查表结构层面最容易出问题的是默认值和类型映射。Navicat转换后很多列的默认值要么丢失要么语义不对。我习惯用下面这条SQL把整库的列默认值全部拉出来过一遍SELECT table_name, column_name, data_type, column_default, character_maximum_length FROM information_schema.columns WHERE table_schema public ORDER BY table_name, ordinal_position;重点看几类整型主键列的默认值有没有nextval时间字段的默认值是不是CURRENT_TIMESTAMP有没有列的类型变成了和业务预期不一致的smallint、text。类型方面MySQL里的datetime到PostgreSQL一般会变成timestamp without time zone大部分场景问题不大。但如果你原来的业务用到CURRENT_TIMESTAMP作为默认值而迁移后默认值丢了插入数据时就会出现“没有为字段指定值且没有默认值”的报错。补默认值的写法是ALTER TABLE 表名 ALTER COLUMN 字段名 SET DEFAULT now();如果是把MySQL的CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP迁移过来PostgreSQL没有ON UPDATE自动更新的概念。这个得靠触发器模拟或者业务层手动维护更新时间字段。这一点很容易被忽略但往往是后续数据不一致的隐患。2.3 字符集与乱码检查MySQL那边如果是utf8mb4PostgreSQL直接用UTF8就行字符集本身没有障碍。但如果源库是latin1、gbk这类编码Navicat传输时如果连接字符集设置不对很容易导致中文数据变成乱码。检查方法不用太复杂在PostgreSQL里直接查几条包含中文的数据肉眼比对或者程序里比对都行。更系统的做法是抽样对比MySQL端查出来的值和PostgreSQL端查出来的值逐条比对。另外要注意MySQL的utf8mb4支持emoji和生僻字PostgreSQL的UTF8同样支持但如果Navicat在传输过程中把连接字符集设置成了utf8不带mb4的旧概念某些字符就会显示成问号或者乱码。如果发现这种情况不要试图在PostgreSQL里修复而是要从源头重传把Navicat的连接编码统一设置成UTF-8后再传一次。3. 序列修复与自增主键重置3.1 自增在PostgreSQL里到底是什么这是MySQL开发者在接触PostgreSQL时最容易绕晕的概念。MySQL的AUTO_INCREMENT是表上的一个属性简单粗暴PostgreSQL没有这个属性它用的是序列对象也就是sequence本质上是一个独立的计数器表字段通过DEFAULT nextval(序列名)和它关联。Navicat在把MySQL表结构转换到PostgreSQL时对自增列的处理经常是半吊子的有时候它把字段类型转成integer但没绑序列有时候它设置了DEFAULT nextval但序列没创建有时候序列创建了但起始值还是1没有回填当前表的最大ID。这三种情况都会导致同一类问题——插入数据时主键冲突或者主键为空。要判断当前库里哪些字段关联了自增序列执行这条SQLSELECT table_name, column_name, column_default FROM information_schema.columns WHERE table_schema public AND column_default LIKE nextval%;如果查询出来你的主键列不在结果里那就说明这个表的自增关联丢了需要手动补。3.2 批量创建序列与回填最大值给单张表修复自增比较简单三步走创建序列、绑定默认值、把序列跳到当前最大ID。手工操作是这样的CREATE SEQUENCE IF NOT EXISTS 表名_id_seq START WITH 1; ALTER TABLE 表名 ALTER COLUMN id SET DEFAULT nextval(表名_id_seq); SELECT setval(表名_id_seq, (SELECT MAX(id) FROM 表名));但几十张表要是都这么手工搞太费精力了。我写过一个批量处理的DO块可以把库里所有主键是整型但没有绑定序列的表一次性修复。核心思路是遍历主键约束找出主键列判断它的默认值是否包含nextval没有的就去创建序列并绑定然后把序列的当前值设置成表里最大值或1。DO $$ DECLARE r record; seq_name text; has_seq boolean; BEGIN FOR r IN SELECT c.table_name, c.column_name, c.data_type FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name kcu.constraint_name AND tc.table_schema kcu.table_schema JOIN information_schema.columns c ON c.table_name tc.table_name AND c.column_name kcu.column_name AND c.table_schema tc.table_schema WHERE tc.constraint_type PRIMARY KEY AND tc.table_schema public AND c.data_type IN (integer, bigint) LOOP EXECUTE SELECT count(*) 0 FROM pg_class WHERE relname || quote_literal(r.table_name || _ || r.column_name || _seq) INTO has_seq; IF NOT has_seq THEN seq_name : r.table_name || _ || r.column_name || _seq; EXECUTE CREATE SEQUENCE || quote_ident(seq_name); EXECUTE format(ALTER TABLE %I ALTER COLUMN %I SET DEFAULT nextval(%L), r.table_name, r.column_name, seq_name); EXECUTE format(SELECT setval(%L, COALESCE((SELECT MAX(%I) FROM %I), 1), true), seq_name, r.column_name, r.table_name); RAISE NOTICE fixed sequence for %.%, r.table_name, r.column_name; END IF; END LOOP; END $$;这个脚本在我自己的迁移项目中跑过多次比较稳。需要说明的是它只修复主键为整型或大整型的表如果你的主键是uuid或者varchar不需要序列脚本也不会误处理。跑完之后再执行前面的检查SQL确认一遍所有主键列的默认值都应该带nextval了。另外还有一个细节如果原MySQL表里的数据有跳跃ID比如最大ID是10000但实际只有500行序列会跳到10000之后继续递增这是正常行为不用担心。但如果你希望新数据从1开始连续排那就把setval的第二个参数改成1只对空表有意义有数据的情况下强行从1开始会立刻撞主键。3.3 标识符大小写批量改名Navicat转换过来的表名和字段名很多是带着双引号、保留原始大小写的。比如MySQL里那张UserInfo表在PostgreSQL里成了UserInfo普通SQL怎么写都查不到。检查这种问题的SQL很简单SELECT tablename FROM pg_tables WHERE schemaname public AND tablename lower(tablename);凡是在结果里的都是带着引号大小写敏感的表。字段同理SELECT table_name, column_name FROM information_schema.columns WHERE table_schema public AND column_name lower(column_name);处理方式我建议一步到位把表名和字段名全部改成小写、去掉引号依赖。生成改名语句的SQL如下SELECT ALTER TABLE || tablename || RENAME TO || lower(tablename) || ; FROM pg_tables WHERE schemaname public AND tablename lower(tablename);执行生成的语句之前务必确认业务代码里没有依赖于大写表名的UserInfo式写法。如果有业务SQL也要跟着改成小写形式。这个改动虽然牵一发动全身但从长远看是值得的否则每个查询都要小心翼翼加引号排查问题的时候心态会很崩。4. SQL方言迁移与函数改写对照4.1 高频函数对照清单数据都进来了自增也修好了接下来真正耗时间的其实是业务SQL的适配。MySQL和PostgreSQL虽然都是关系型数据库但SQL方言差异比想象中大。我把迁移过程中最常遇到的函数差异整理成了对照表可以直接参考。MySQL写法PostgreSQL替代写法说明IFNULL(a, b)COALESCE(a, b)PostgreSQL原生支持COALESCE语义一致GROUP_CONCAT(x SEPARATOR ,)STRING_AGG(x, ,)注意两者对NULL的处理略有差异DATE_FORMAT(ts, %Y-%m-%d)TO_CHAR(ts, YYYY-MM-DD)格式串占位符规则不同迁移后容易出错UNIX_TIMESTAMP()EXTRACT(EPOCH FROM NOW())返回秒数注意类型是numericSUBSTRING_INDEX(str, ,, 2)SPLIT_PART(str, ,, 2)语义不完全等价边界情况要测试IF(expr, a, b)CASE WHEN expr THEN a ELSE b ENDPostgreSQL没有IF函数FIND_IN_SET(x, str)x ANY(STRING_TO_ARRAY(str, ,))无直接对应函数建议改写逻辑LIMIT offset, countLIMIT count OFFSET offset参数顺序正好相反INSERT IGNOREINSERT ... ON CONFLICT DO NOTHING需要指定冲突约束REPLACE INTOINSERT ... ON CONFLICT (key) DO UPDATE SET ...语义有差异建议谨慎迁移这里最坑的是DATE_FORMAT到TO_CHAR的转换格式串的字母含义完全不同%Y在PostgreSQL里不支持必须改成YYYY。这种错误一般会直接报错还算容易发现但GROUP_CONCAT改成STRING_AGG后如果分组字段的排序顺序依赖原来GROUP_CONCAT(x ORDER BY y)里的排序改写时也要保留ORDER BY-- MySQL SELECT dept_id, GROUP_CONCAT(emp_name ORDER BY emp_id) FROM emp GROUP BY dept_id; -- PostgreSQL SELECT dept_id, STRING_AGG(emp_name, , ORDER BY emp_id) FROM emp GROUP BY dept_id;还有一点容易被忽视MySQL的GROUP BY在ONLY_FULL_GROUP_BY未开启时查询的非聚合列可以随便写PostgreSQL在这点上是严格模式所有非聚合列必须出现在GROUP BY里。迁移后这类SQL会直接报错不是简单的函数替换问题需要重构查询逻辑。4.2 视图、触发器、存储过程重写要点Navicat对视图、触发器、存储过程这类对象的搬迁能力基本可以忽略这类对象十有八九要手工重建。先说视图。MySQL里视图可以直接带ORDER BYPostgreSQL不允许视图里出现无意义的ORDER BY除非同时有LIMIT。另外MySQL视图里常用的IFNULL、DATE_FORMAT函数都要替换成PostgreSQL版本。一个典型的例子-- MySQL CREATE VIEW v_user AS SELECT id, IFNULL(nickname, 未设置) AS nickname FROM user; -- PostgreSQL CREATE VIEW v_user AS SELECT id, COALESCE(nickname, 未设置) AS nickname FROM user;触发器在PostgreSQL里的写法要拆成两步。先创建一个返回trigger类型的函数再把这个函数绑定到表上。MySQL里那种直接在触发器里写逻辑的方式在PostgreSQL里不存在。示例CREATE OR REPLACE FUNCTION trg_set_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at now(); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER t_user_updated_at BEFORE UPDATE ON user FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();注意PostgreSQL新版用的是EXECUTE FUNCTION老版本是EXECUTE PROCEDURE两个都能用但FUNCTION是推荐写法。存储过程这块差异更大。MySQL的DELIMITER、BEGIN...END、DECLARE语法和PostgreSQL的PL/pgSQL完全是两套。PostgreSQL里一般用CREATE OR REPLACE FUNCTION参数用$1、$2或者命名参数返回多行结果集会用到RETURNS TABLE。这里不展开全部语法只想强调一个原则不要试图写一个自动转换脚本去翻译存储过程手工逐个重写虽然费时间但可控性高得多。业务逻辑复杂的存储过程建议先在纸上把逻辑画清楚再用PostgreSQL方言重新实现。4.3 布尔与空值语义的坑这个坑很隐蔽因为它不报错但逻辑就是不对。MySQL里tinyint(1)经常被用来表示布尔值0和1。Navicat转过来后通常变成smallint。业务代码里那种WHERE is_active 1的写法在PostgreSQL里不会报错因为字段确实是整数。但如果哪天你手动改了字段类型成boolean那WHERE is_active 1就会报“column is of type boolean but expression is of type integer”。反过来如果Navicat把某些字段转成了boolean业务代码里写WHERE is_active true没问题但如果写 1就会报错。所以迁移后建议统一排查所有tinyint(1)类型的源字段明确到底业务是按布尔语义还是按整数语义使用选择一种并在应用层统一。还有一个空值的坑。MySQL里很多人习惯用空字符串来表示“没有值”查询时也经常WHERE name 。但PostgreSQL里和NULL是两回事如果原来的数据里既存在NULL也存在空字符串迁移后拿WHERE name 去查会漏掉那些原本就是NULL的数据。这种情况下建议把业务逻辑改成WHERE name IS NULL OR name 或者迁移前就先定好规范把不需要区分NULL和空串的字段统一转成NULL。5. 性能与索引差异5.1 索引与约束的迁移差异索引这块Navicat会尝试把MySQL的普通索引带过去但有几个点需要额外处理。索引命名空间问题MySQL里不同表的索引可以重名PostgreSQL里同一个schema下的索引名必须唯一。如果两张表都有idx_nameNavicat转换时就会在第二张表上报错。迁移后检查一下发现索引缺失的用新的命名规范重建。MySQL的全文索引在PostgreSQL里没有直接对应物。如果原来的业务依赖MATCH ... AGAINST做全文检索迁移到PostgreSQL后有两条路一是用GIN索引加上tsvector列实现二是上pg_trgm扩展做模糊匹配。这属于功能重构不是简单改语法。外键约束也是需要特别关注的。如果迁移时Navicat按原顺序插入数据而两张表有外键关联先插子表后插父表就会报错。Navicat一般会跳过外键约束直接导数据这样反而省事。导完数据后再手工重建外键如果有数据违反外键约束重建时会报错正好可以排查出脏数据。5.2 排序规则与模糊查询性能MySQL的utf8mb4_unicode_ci排序规则默认是不区分大小写的但PostgreSQL的默认排序是区分大小写的且按数据库的locale规则排序。这就导致同一个WHERE name abc在MySQL里能匹配到ABC在PostgreSQL里匹配不到。如果业务确实需要不区分大小写的等值匹配可以用citext扩展CREATE EXTENSION citext;然后把相关字段类型改成citext或者查询时显式使用ILIKE。模糊查询的性能也值得提前关注。MySQL里WHERE name LIKE %abc%如果是小表还能忍受数据量一大就慢。PostgreSQL里普通B-tree索引对%abc%这种前导通配符完全无效。建议要么用pg_trgm扩展建GIN索引要么考虑上全文检索。这个改造不是迁移必须但如果迁移后的库要继续承载原有业务量绕不开。另外还有一个和性能相关的配置PostgreSQL的search_path。如果数据和索引都在publicschema下一般不用管但如果Navicat迁移时把原来的数据库名建成了一个新的schema连接串上就要显式指定currentSchema否则应用会去public找表然后报“relation does not exist”。这个错误特别容易让新手误判成表丢了。6. 常见报错与排查速查表6.1 报错清单把迁移后最常见的报错整理成一张速查表方便遇到问题先翻这里。报错信息原因处理方式relation xxx does not exist表名/字段名带引号大小写敏感批量小写化表名和字段名或SQL里加双引号null value in column id violates not-null constraint自增序列没有绑定或没有回填创建序列并绑定默认值setval到当前最大值duplicate key value violates unique constraint序列起始值小于已有最大ID或数据本身重复重置序列清理重复数据column flag is of type smallint but expression is of type boolean业务SQL用布尔表达式匹配整型字段修改SQL或改字段类型invalid input syntax for type timestampMySQL里存在0000-00-00非法日期迁移前把非法日期改成NULL或合法值value too long for type character varying(n)源字段数据超出目标字段长度在PostgreSQL里扩大varchar长度或截断数据function to_char(timestamp, unknown) does not exist函数签名或参数类型不匹配确认字段实际类型cast成需要的类型permission denied for schema public数据库用户没有schema权限给迁移用户授权schema权限以上每一条我都实际踩过尤其是第一条和第二条基本是每次迁移都会碰到。6.2 实战踩坑记录分享一个我印象最深的真实场景。某次迁移一张订单表Navicat传输显示成功表数据行数也和MySQL一致。但第二天业务方反馈新增订单接口一调用就报“主键为空”。排查发现Navicat确实把id字段转成了integer但表上没有默认值也没有创建序列。显然这个项目里业务代码写了id列让数据库生成MySQL自动递增没问题PostgreSQL没有序列就没法自动生成。修复过程就是先创建序列CREATE SEQUENCE orders_id_seq START WITH 1; ALTER TABLE orders ALTER COLUMN id SET DEFAULT nextval(orders_id_seq); SELECT setval(orders_id_seq, (SELECT MAX(id) FROM orders));修完之后新插入的订单ID从最大ID1继续走数据上下衔接正常。这个问题本身不难难在发现因为表能查、数据齐全表面一切正常只有写操作才暴露。另一个案例是大写表名导致的诡异报错。那张表叫DataLogMySQL里业务代码一直写DataLog一切正常。迁移到PostgreSQL后业务SQL把所有查询都报“relation datalog does not exist”。查了一圈发现表名实际是DataLogNavicat把它原样保留且加了双引号。最终我用一条ALTER TABLE DataLog RENAME TO datalog;解决同时把所有大写表名批量处理了一遍。还有一个比较隐蔽的隐患MySQL端有一张表存过0000-00-00 00:00:00的默认值Navicat传输的时候居然没报错但后续应用程序读取这个字段时直接抛异常。原因是PostgreSQL内部存储的是非法时间读取时校验失败。处理方法是扫描所有时间字段把早于1000-01-01的异常值全部改成NULL或者合理默认值。这种数据问题往往不是迁移时报出来的而是业务运行到某个角落才炸排查起来很费劲。迁移这件事表面上是工具操作实际上是语义对齐。Navicat能帮你把数据搬运到位但真正的工程量在搬运之后的校验、修复和适配。我个人建议凡是准备做MySQL到PostgreSQL迁移的项目至少预留出和迁移同等的时间来做后续处理多花点精力把自增序列、大小写标识符、类型语义这三件基础事做扎实后面跑业务会省下大把时间。最后分享一个小技巧所有检查SQL和修复脚本处理完之后都留一份存档下次迁移直接改改表名复用。我每次做这类迁移都会积累一批自己的脚本越到后面越省事。