提到从 MySQL 迁到 PostgreSQL很多人的第一反应是“语法不都差不多嘛”。真到了改代码的时候才发现两者之间的差异远不止换一个驱动、改一条连接串那么简单。反引号到底能不能用、自增列为什么突然报错、GROUP BY 为什么比以前严格那么多、limit 后面的两个数怎么没了——这些全是直接从 MySQL 换到 PG 时必然会撞上的“语法问题”。这篇内容不是什么官方迁移手册而是基于我自己把一个老系统的几十张表、几十个接口、一堆历史 SQL 全部切换成 PostgreSQL 的实际经历整理出来的语法差异清单。MySQL 用户看这份东西能少走很多弯路PG 老手也可以当作一份迁移对照速查表。1. 项目背景为什么会有这道“语法鸿沟”1.1 MySQL 与 PG 的定位差异MySQL 和 PostgreSQL 虽然都是关系型数据库但它们的设计哲学差别很大。MySQL 长期走的是“快速上手、开箱即用”的路线很多语法为了用户方便做出了妥协比如反引号、宽松的 GROUP BY、允许在 SELECT 里写乱七八杂的列甚至支持limit 10, 20这种很“贴地气”的写法。PostgreSQL 则一直把“标准”和“严谨”放在前面绝大部分语法遵循 SQL 标准出错了不允许含糊。这种差异放到实际项目里就成了迁移时一道一道具体的坎。你从 MySQL 复制一条看起来正常的 SQL 到 PG 里很可能第一个错误就是语法错误。不是你写错了而是两边的“方言”不同。我给这种差异起个名字“语法方言”。所谓“鸿沟”本质是两套 SQL 语法习惯的对撞理解了核心理念这条沟是完全可以填平的。1.2 迁移时最容易踩坑的领域从我这次迁移来看踩坑主要集中在四个方向一是标识符和字符串的处理方式二是主键自增机制的剧变三是 SQL 语句语法细节的差异四是数据类型和内置函数因为命名不同导致的无谓报错。这四个方向占到了整个迁移期排错量的 80% 以上。另一个经常被忽略的问题是现有的 ORM 框架或者底层 SQL 工具可能在中间做了一层“翻译”比如 MyBatis 或 JPA 会自动适配数据库方言这让一部分语法差异被掩盖了。但只要你项目里有手写 SQL、存储过程、定时脚本或者还在用老的 JDBC Statement翻译层就救不了你。所以真正能保障迁移顺利的还是得自己把这些语法规则理清楚。2. 引号、字符串与大小写第一道基础坎2.1 反引号 vs 双引号别再把墙当草地了MySQL 里最经典的写法是给表名、字段名加反引号用来防止和关键字冲突比如SELECT id, name FROM user WHERE status 1;这套写法在 PostgreSQL 里会直接报语法错误。PG 根本不认识反引号它只认双引号而且在 PG 的规则里双引号不是用来“防止冲突”的而是用来“保留大小写”的。比如SELECT id, name FROM user WHERE status 1;这个语句在 PG 中能执行但这么用其实是在给自己挖坑。因为 PG 对未加双引号的表名和字段名会自动折叠成小写如果你建表时用了双引号包裹大写或者混合大小写的名字那以后每次查询都必须精确带上双引号和同样的大小写否则就会报“relation does not exist”。我踩过一个具体的坑MySQL 里有个表叫OrderInfo迁移时我用工具直接建表工具自动把它变成了 PG 的小写表名orderinfo。但业务代码里仍然用驼峰格式查询结果 PG 把未加引号的OrderInfo折叠成orderinfo反而能查中看着没毛病。但如果代码里有些地方写成OrderInfo立刻找不到表折腾了半天才发现是大小写折叠的问题。提示迁移到 PG 后建议统一把所有表名、字段名设计成小写。如果历史代码里已经大量使用驼峰或者大小写混合的命名宁可建表时也用双引号固定大小写并保证代码里每个 SQL 都精确匹配否则这是一颗随时会爆的雷。2.2 字符串类型与转义规则MySQL 里的字符串可以用单引号也可以用双引号。PG 里标准字符串只能使用单引号双引号是用来表示数据库对象标识符的不是字符串字面量。下面这条 MySQL SQL 到了 PG 里就会出问题SELECT * FROM t WHERE name 张三;在 PG 里双引号会被解释为一个名为“张三”的列而不是字符串“张三”所以会直接报“column 张三 does not exist”。正确写法是SELECT * FROM t WHERE name 张三;这一点如果是在用 ORM框架通常会帮你处理但在原生 SQL、存储过程、SQL 脚本里这是非常常见的坑。再说转义。MySQL 默认情况下反斜杠\是字符串转义符所以a\b可以表示ab。PG 的默认行为是关闭反斜杠转义的也就是说a\b在 PG 里会被解析成“a\”然后 b 变成了未闭合字符串直接报表错。如果你确实需要在字符串里包含反斜杠PG 提供了standard_conforming_strings参数默认是 on代表反斜杠不转义如果你要转义就得用E前缀SELECT Ea\\b;这类转义问题在迁移大量历史 SQL 时特别烦人尤其是正则表达式、文件路径、Windows 风格反斜杠这类内容。最直接的解决办法是迁移后全局搜一遍含反斜杠字符串的 SQL逐条手工改成E或者用chr(92)拼字符。2.3 大小写敏感的另一种含义MySQL 在 Windows 上表名不区分大小写在 Linux 上默认区分但列名不区分大小写。PG 的规则是不加引号的对象名一律折叠成小写加引号的对象名则严格区分大小写。所以SELECT * FROM Orders和SELECT * FROM ORDERS在 PG 里等价都会查询orders这个表但如果写Orders查的就是一个名为Orders、和orders不同的表。这个规则对迁移最大的影响是如果你的 MySQL 建表语句或导出工具生成了带反引号的混合大小写名字导入 PG 后要么全都小写要么全都必须带引号固定大小写。我建议所有对象统一小写因为 PG 的默认行为就是小写代码里不写引号最省心。3. 自增主键、序列与默认值从 AUTO_INCREMENT 到 SERIAL/IDENTITY3.1 自增主键的两种“翻译方式”MySQL 的经典自增主键写法CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY (id) );这个结构到 PG 之后不能直接用。PG 里最接近的替代方式是SERIAL伪类型CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(50) );SERIAL会同时创建一个整数列和一个名为users_id_seq的序列并给列设置了默认值nextval(users_id_seq)。这样每次插入时如果没有指定 idPG 会自动从序列取值行为和 MySQL 的 AUTO_INCREMENT 基本一致但底层机制完全不同。除此之外PG 10 还提供了更标准的语法GENERATED AS IDENTITYCREATE TABLE users ( id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(50) );推荐在新建表时直接使用 IDENTITY 语法因为它的语义比 SERIAL 更清晰而且不会和普通序列混在一起产生管理上的麻烦。但如果是迁移旧表我个人更倾向 SERIAL因为很多迁移工具和第三方脚本都已经兼容了这一写法。3.2 序列的完整理解与同步问题PG 的序列独立于表存在这带来一个 MySQL 不需要考虑的问题如果你手动插入了一条指定 id 的记录序列并不会自动向上调整。比如你现在表里最大 id 是 100序列的当前值可能还是 1你手工插入了 id1000 的一条记录之后再用正常的默认值套路插入下一条时会报主键冲突因为序列还在从 1 开始往后走撞到已有的 id 1000 上了。解决方法是手动把序列同步到最大值SELECT setval(users_id_seq, max(id)) FROM users;注意setval的第二个参数传入的是目标值执行之后下一次nextval会返回目标值加 1。所以如果你想后续 id 从 1001 开始就 setval 到 1000如果希望直接从 1001 开始也可以传 1001但要注意语义。这种“序列不同步”的问题在迁移数据时极其常见。任何采用“先迁数据再开放写入”的迁移方案迁完后第一件事就是把所有表的序列重新重置一遍。不然业务上线后第一个插单就会撞主键而且撞的是你不知道什么时候插入的历史数据。3.3 迁移中需要修改的建表语句除了AUTO_INCREMENTMySQL 建表中还有一批 PG 不认识的关键字。比如字段类型DATETIME换成TIMESTAMPTINYINT(1)换成BOOLEANINT UNSIGNED换成INTEGER CHECK (col 0)或者用DOMAIN封装。为了避免折腾我建议迁移时不要直接用工具转换建表脚本而是先让工具生成 PG 版的 DDL然后人工逐行过一遍。重点看哪里用了ENGINEInnoDB、COMMENT、AUTO_INCREMENT、UNSIGNED、ON UPDATE CURRENT_TIMESTAMP这些在 PG 中的表现都不太一样。分享一个常用写法MySQL 里ON UPDATE CURRENT_TIMESTAMP在 PG 里不会自动更新。需要先建触发器或者让应用层每次更新时显式带updated_at now()。如果项目里很多表都依赖这个特性建议用触发器统一处理避免漏改。4. 增删改查语法差异SQL 语句里的“方言”4.1 分页语法LIMIT 和 OFFSET 的顺序MySQL 的分页可以这样写SELECT * FROM orders LIMIT 10, 20;这个语句的含义是“跳过 10 条取 20 条”。PostgreSQL 不认这个写法会报syntax error at or near ,。PG 正确的写法是SELECT * FROM orders LIMIT 20 OFFSET 10;或者更符合 SQL 标准的写法是SELECT * FROM orders OFFSET 10 FETCH FIRST 20 ROWS ONLY;需要注意的是 OFFSET 子句必须放在 LIMIT 之前吗其实 PG 的语法是 LIMIT 和 OFFSET 可以相互独立但顺序通常是LIMIT n OFFSET m。如果你写OFFSET m LIMIT n其实 PG 也支持但为了统一还是按标准顺序写。这个差异对代码的影响很大尤其是在使用 MyBatis 手写分页插件或自研分页组件时。最好把分页 SQL 生成逻辑单独封装按数据库类型切换两个模板。4.2 GROUP BY 的严格性以前能跑现在直接报错MySQL 在没有开启ONLY_FULL_GROUP_BY模式时允许一个很舒服的写法SELECT user_id, user_name, MAX(order_time) FROM orders GROUP BY user_id;这里 GROUP BY 只写了user_id但 SELECT 里却多了一个user_name。在 MySQL 宽松模式下user_name会从当前分组的第一行里随便取一个值不报错。但 PostgreSQL 从一开始就严格遵循标准凡是没有出现在 GROUP BY 里的列必须包含在聚合函数中否则直接报column orders.user_name must appear in the GROUP BY clause or be used in an aggregate function。所以迁移之后必须在 GROUP BY 里补充所有非聚合列SELECT user_id, user_name, MAX(order_time) FROM orders GROUP BY user_id, user_name;如果原来的 SQL 很复杂靠“随便取一个值”的写法跑出来的业务迁移后就得认真梳理逻辑了。到底是补充到 GROUP BY 里还是改成MIN(user_name)或MAX(user_name)聚合取决于业务真正想要什么。这是迁移中工作量不小的一块不要想着用空参数糊弄过去。4.3 INSERT 冲突处理ON DUPLICATE KEY UPDATE 的替代MySQL 的插入或更新这种“UPSERT”很简洁INSERT INTO users (id, name, email) VALUES (1, 张三, zhangsanexample.com) ON DUPLICATE KEY UPDATE email VALUES(email);PostgreSQL 没有ON DUPLICATE KEY UPDATE但有更灵活的ON CONFLICT ... DO UPDATE SETINSERT INTO users (id, name, email) VALUES (1, 张三, zhangsanexample.com) ON CONFLICT (id) DO UPDATE SET email EXCLUDED.email;这里EXCLUDED代表“本次欲插入但产生冲突的行”等价于 MySQL 的VALUES()引用方式。对于联合唯一约束可以写ON CONFLICT (col1, col2)当冲突目标是一个唯一索引而普通 INSERT 没有暴露约束时可以写ON CONFLICT ON CONSTRAINT constraint_name。这个差异是“功能对等、写法不同”不算难但有一个细节要注意PG 的ON CONFLICT DO UPDATE要求括号里的列必须与某个唯一约束或唯一索引匹配否则报错。MySQL 是根据主键或唯一键自动兜底PG 更严格必须明确指出冲突依据。4.4 多表更新与删除写法MySQL 支持多表 UPDATE 的写法UPDATE orders o JOIN users u ON o.user_id u.id SET o.user_name u.name WHERE u.status 1;PG 不支持这种UPDATE ... JOIN语法标准做法是使用UPDATE ... FROMUPDATE orders o SET user_name u.name FROM users u WHERE o.user_id u.id AND u.status 1;注意 PG 的UPDATE ... FROM里不需要 JOIN ON就把关联条件写在 WHERE 里而且 FROM 子句可以放多张表。这个语法刚开始看会不习惯但写起来其实更接近 SQL 标准。DELETE 也有类似区别。MySQL 的多表删除DELETE o FROM orders o JOIN users u ON o.user_id u.id WHERE u.status 0;PG 对应写法DELETE FROM orders o USING users u WHERE o.user_id u.id AND u.status 0;虽然功能类似但 PostgreSQL 的DELETE ... USING只能删除 FROM 指定的那张表而 MySQL 可以同时删除多张表。如果你原来在一句 DELETE 里同时清掉 orders 和 users 相关的两张表迁移时就要拆成多条 DELETE。5. 数据类型与内置函数换个名字你就不会写了5.1 常用数据类型对照表数据类型差异是最容易在迁移过程中被忽略的因为很多类型名字看上去“差不多”。这里列一份常用对照MySQLPostgreSQL备注INT/TINYINT/SMALLINT/...INTEGER/SMALLINT 等TINYINT(1) 常表示布尔PG 用 BOOLEANBIGINT UNSIGNEDNUMERIC(20,0) 或 BIGINT CHECKPG 没有无符号整型VARCHAR(n)VARCHAR(n)按字符数限制基本兼容TEXTTEXTMySQL TEXT 不能有默认值PG 可以DATETIME/TIMESTAMPTIMESTAMPPG 默认 time zone 问题需要注意BLOBBYTEA二进制对象类型不同ENUMENUM 或 CHECKPG 有 ENUM但修改枚举值方式不同DECIMALNUMERIC基本等价JSONJSON / JSONBPG 的 JSONB 更常用只改类型名还不行类型的行为细节也可能不同。比如 MySQL 的TIMESTAMP默认是TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPPG 的表创建时如果不指定默认值插入时就不会自动填充。而 PG 的TIMESTAMP还分为WITH TIME ZONE和WITHOUT TIME ZONE迁移时间字段时必须想清楚取哪个时区语义。5.2 IF 函数PG 没有 IF()MySQL 里经常用 IF() 做条件取值SELECT IF(status 1, 已启用, 已停用) AS status_name FROM users;PG 里没有IF()函数但推荐使用标准 CASE 表达式SELECT CASE WHEN status 1 THEN 已启用 ELSE 已停用 END AS status_name FROM users;迁移中有很多动态 SQL 是用 IF() 拼出来的比如权限过滤、动态排序。改成 CASE 后往往冗长不少。如果只是简单判断能否用COALESCE替代类似 MySQL 的IFNULL那会清爽很多。但条件分支型的 IF 基本只能改成 CASE WHEN。一个容易忽略的点MySQL 中IF(expr, a, b)如果第一个参数是非零数字就是真。比如IF(1, x, y)返回 x。PG 的CASE判断时条件必须是一个布尔表达式数字不能直接当作布尔值。如果你的旧代码里有IF(id, 有, 无)这种写法迁移后要写成CASE WHEN id 0 THEN 有 ELSE 无 END很麻烦。5.3 字符串聚合GROUP_CONCAT 与 string_aggMySQL 把一列多行拼成一个字符串用的是 GROUP_CONCATSELECT GROUP_CONCAT(name ORDER BY id SEPARATOR 、) FROM users;PG 的标准实现是array_agg或者string_aggSELECT string_agg(name, 、 ORDER BY id) FROM users;两者最大的区别string_agg的返回类型是 TEXT而且如果所有值都是 NULL它返回空字符串但分组不会消失MySQL 的GROUP_CONCAT遇到 NULL 会忽略。此外 PG 中string_agg里要指定 ORDER BY 时可以直接放在聚合函数内部这点和 MySQL 的 GROUP_CONCAT 类似。但要注意 PG 的string_agg对 NULL 的处理它会把 NULL 忽略掉还是当空字符串加进去实测下来NULL 值会被忽略不参与拼接这和 MySQL 一样。但是如果所有参与值都是 NULLstring_agg返回的是 NULL而GROUP_CONCAT返回的是空字符串需要做COALESCE兜底。5.4 布尔值与数字的转换MySQL 里BOOLEAN本质上是TINYINT(1)可以写入true/false也可以写入1/0。PG 中是真正的布尔类型值只有true/false/TRUE/FALSE/t/f等等不能直接把整数塞进布尔列。比如UPDATE users SET is_active 1 WHERE id 1;在 PG 里这句会报column is_active is of type boolean but expression is of type integer。要改成UPDATE users SET is_active true WHERE id 1;如果是从外部系统读取数据或者做数据导入建议先把数值映射成布尔值。这个坑在 Java 或 Python 的 JDBC/psycopg 驱动里会被自动处理一部分但如果你用 SQL 脚本或者直接手工执行就一定会遇到。6. 索引、约束与系统视图语法差异背后的逻辑6.1 索引定义语法差异MySQL 可以给 VARCHAR 列定义前缀索引CREATE INDEX idx_name ON users (name(10));PostgreSQL 不支持这种写法会直接报语法错误。但你可以用表达式索引实现类似的效果CREATE INDEX idx_name ON users (left(name, 10));两种“前缀索引”在生产行为上并不完全等价。MySQL 的前缀索引只能影响索引本身的存储和匹配长度但 PG 的表达式索引是运算结果的索引查询时必须也写出同样的表达式否则优化器未必会使用。所以在迁移时如果原查询写的是WHERE name LIKE abc%前缀索引可能命中PG 表达式索引则需要先确认查询是否能转换为WHERE left(name, 10) abc否则索引可能用不上。此外 PG 提供了CREATE INDEX CONCURRENTLY可以在线建索引不锁写这个比 MySQL 平时建索引更友好。迁移过程中如果要给大表补索引建议直接用这个语法。6.2 NULL 排序默认顺序完全不同这个坑非常隐蔽。MySQL 里排序时 NULL 被视为小于任何非 NULL 值所以ORDER BY col ASC时 NULL 排在最前面。PG 的默认行为则刚好相反NULL 被视为大于任何非 NULL 值所以ORDER BY col ASC时 NULL 排在最后面。如果你在 MySQL 里写ORDER BY created_at DESC结果里没有时间的记录会排在最前到 PG 里后会变成排在最后。如果业务依赖这种顺序就必须显式加上NULLS FIRST或NULLS LASTSELECT * FROM orders ORDER BY created_at DESC NULLS LAST;不光排序。唯一约束的 NULL 行为也有差异MySQL 的UNIQUE列允许存在多个 NULLPG 也同样允许这一点上两者倒是挺一致的。但在复合唯一索引里MySQL 和 PG 对 NULL 的去重逻辑并不完全相同迁移时如果要保证数据一致性需要专门检查。6.3 information_schema 的差异做迁移通常要写一批脚本去读取表结构、生成建表语句很多人习惯查information_schema。这个视图 MySQL 和 PG 都有但字段类型和内容不完全一致。比如 MySQL 的COLUMNS.DATA_TYPE会返回bigint、int、varchar这类名字而 PG 的DATA_TYPE可能返回integer、character varying。同样在information_schema.tables中MySQL 有ENGINE字段而 PG 没有PG 有table_schema表示表所在的 schemaMySQL 的table_schema相当于数据库名。所以如果你写了一个通用脚本在两边跑最好先判断一下数据库品牌再决定具体查哪个字段。能用数据库自带的命令就尽量用自带的比如 MySQL 用SHOW CREATE TABLEPG 用 psql 的\d 表名不要只依赖 information_schema。6.4 层级命名空间database 与 schemaMySQL 习惯把“库”当成“库”一个服务连一个库跨库查询直接dbname.tablename。PG 的层级是“实例 - 数据库 - schema - 表”。“数据库”里包含多个“schema”默认 schema 是public所以在 PG 中表名域通常写作public.users。迁移时经常看到这样的代码MySQL 里DELETE FROM mydb.orders WHERE ...PG 里如果是DELETE FROM mydb.public.orders就会连跨两层是有问题的。PG 不能用“数据库名.表名”跨库查询必须通过postgres_fdw或dblink做跨库访问。如果你的业务原来依赖跨库 join迁移到 PG 后要么把多库合并成同一个数据库要么就老老实实引入外部表扩展。这个属于架构层面的调整不是简单改 SQL 就能解决的。7. 常见问题与迁移建议踩坑实录7.1 典型迁移报错速查表为了让你少撞几回墙我把这次迁移里遇到的高频报错和对应原因整理成了速查表报错信息原因处理方式syntax error at or near 使用了 MySQL 反引号改成双引号或去掉引号column xxx does not exist双引号将字符串误当成列名字符串用单引号relation xxx does not exist大小写折叠或 schema 未指定统一小写并带上 schema 如 public.xxxmust appear in the GROUP BY clausePG 严格 GROUP BYSELECT 的列都加入 GROUP BY 或使用聚合函数does not support the LIMIT m, n syntax分页写法不兼容改为LIMIT n OFFSET mcolumn is_active is of type boolean but expression is of type integer布尔类型与整数混用用 true/false 替换 1/0text cannot be cast to integer/ 类型转换报错隐式类型转换规则不同显式加上::int或CAST(... AS integer)sequence xxx_id_seq does not exist序列名不一致或自增列未同步检查 setvalINSERT has more expressions than target columns插入列表和值列表不匹配检查列数量ON CONFLICT (col) does not match unique index冲突目标不是唯一约束调整唯一索引或指定约束名这些报错大多能在几分钟内定位特别是统一定位“反引号、GROUP BY、分页”三类问题后速度会快很多。7.2 迁库工具与人工修正的取舍完全手写迁移脚本太容易出错了我建议用工具做第一轮转换人工监督修正。常见的工具包括pgloader、ora2pg不是支持 Oracle→PG 吗但目前很多工具也兼容 MySQL、AWS DMS等。我这次用pgloader做了全库的整表迁移表结构和数据能搬过来但生成的索引、序列、默认值仍然有不少需要手动微调。无论工具怎么转换最终落库前一定要在目标库里做一轮冒烟测试SELECT 几条关键数据、执行几个核心接口、跑一遍常用管理报表让问题在测试环境里暴露。工具解决的是“搬数据”语法差异的“搬家”永远得靠人改。7.3 实际项目中我总结的几条迁移经验如果让我再迁一次我会提前做好这几件事先统一代码里的 SQL 风格。迁移前先扫描整个项目把所有反引号全部删掉、字符串统一单引号、分页统一改掉最后再看能不能跑。优先改框架而不是死磕每一条 SQL。如果你的项目使用 MyBatis修好它的方言配置很多语法差异会被自动屏蔽一半。但手写 SQL 依然躲不掉所以还是要把语法差异表发给所有会写 SQL 的开发人员学习。上线前别忘了做数据校验。数据迁移后不仅要看主键序列是否同步还要对比几个关键表的行数、唯一约束、外键关系。另外要注意CHECK约束和NOT NULL约束是否在工具搬运时丢失这类元数据问题往往会在后续业务写入时冷不丁炸出来。最后再分享一个小技巧如果团队里同时维护 MySQL 和 PG 两套环境建议把公共 SQL 抽象成固定的模板尽量少写“方言味”很重的语句。比如分页、UPSERT、条件聚合这类操作要么交给 ORM要么单独抽一层。这样以后再出什么新需求也不至于被数据库方言绑住手脚。上面这些坑我基本都是踩着实实在在的业务线上填平的希望这份对照能让你迁得顺一点。