两年前我第一次把一个维护了快六年的 MySQL 业务库迁到 PostgreSQL下面统称 PG心里想得很简单把表结构倒过去、改一下连接串SQL 应该大差不差。真正开工之后才发现卡住我的不是数据量不是服务器配置而是一堆看起来很小的语法差异。这篇文章就把我当时逐个踩过的“鸿沟”整理出来重点放在语法迁移上自增主键、引号与大小写、UPSERT、GROUP BY、NULL 排序、DDL 类型映射、日期函数和正则这些最容易让 MySQL 老手懵掉的地方。如果你也要做类似迁移或者只是想在两种数据库之间切换不踩坑可以直接参照这些对照关系来改。1. 为什么语法迁移不能靠“搜索-替换”解决1.1 两种数据库的“性格”差很远MySQL 在很长一段时间里主打“好用、够用”很多 SQL 写法是宽松的PG 则更像一个严守 SQL 标准的“考官”你写得不规范它真的会报错。举个最简单的例子下面这条 SQL 在很多老 MySQL 实例上能跑SELECT name, age, COUNT(*) FROM users GROUP BY name;但在 PG 里它会直接告诉你users.age必须出现在 GROUP BY 子句中或用于聚合函数。原因很本质当你按name分组时age到底取哪一行SQL 标准没有定义。MySQL 的宽松模式默认给了你一个“任意值”PG 不允许这种模棱两可。这类差异才是迁移成本的大头。它们不会在数据拷贝阶段暴露而是在业务代码跑起来之后一个接一个蹦出来。你很难用“搜索替换”一次解决因为问题不是某个关键字不同而是背后逻辑不同。1.2 从“宽松模式”到“严格模式”MySQL 的sql_mode可以调节不少行为比如是否允许GROUP BY选非聚合列、是否允许转成0、除法精度如何处理。PG 没有一套等价的 session 开关它的行为更像“标准模式常开”。这意味着迁移时你要把那些年在宽松模式下写出来的“野 SQL”重新按标准写一遍。这不是坏事但对排期来说确实是变量。我当时的做法是把所有待迁移的 SQL 先在 PG 上跑一遍让报错帮我们列清单而不是靠肉眼 review。1.3 迁移前先统一 SQL 规范如果你跟我一样是从一个老项目开始建议先别急着改代码先在团队里把 SQL 规范统一一下表名、列名统一小写下划线普通字符串用单引号查询显式列出列名GROUP BY里的列和SELECT里的非聚合列严格对应。这个动作能省掉后面一大半问题。否则同样的坑会在不同模块里反复出现你改完一个还有下一个。2. 引号、大小写、反斜杠最容易出“玄学报错”的入口2.1 反引号换成双引号但别滥用MySQL 里最常见的写法是用反引号包裹表名和列名SELECT id, name FROM user WHERE status 1;PG 不支持反引号它是 SQL 标准那一套用双引号SELECT id, name FROM user WHERE status 1;但这里有个容易误导人的点在 PG 里双引号会创建一个“区分大小写、保留原样”的标识符。如果你建表时字段叫Name那每次查询都得写成Name写name会找不到。所以我的建议是迁移时尽量把标识符整理成小写下划线能不加引号就不加引号。别把 MySQL 那种“每列都反引号包起来”的习惯带到 PG否则你会被大小写问题折磨死。2.2 未加引号的标识符会被折叠成小写这是 PG 和 MySQL 一个非常隐蔽的区别。在 PG 里不带引号的标识符会被自动转成小写。也就是说你建表时写CREATE TABLE UserInfo (id int);PG 实际存的名字是userinfo。之后你写SELECT * FROM UserInfoPG 会把它翻译成userinfo所以能查到。但如果你建表时用了双引号CREATE TABLE UserInfo (id int);那表名就是确确实实的UserInfo之后写SELECT * FROM UserInfo会因为被折叠成userinfo而报“表不存在”。从 MySQL 迁过来时如果老表名里有大写我建议统一改成小写或者在第一次建表时就保持不带引号的小写命名。不要在迁移后还依赖大小写混用PG 会让你很痛苦。2.3 字符串常量里的反斜杠默认不再转义MySQL 默认把\n、\这类反斜杠序列当转义符处理PG 默认standard_conforming_stringson普通字符串里反斜杠就是普通字符。举个例子SELECT a\nb;在 MySQL 里返回带换行的两行文本在 PG 里返回字面量a\nb。如果你老代码里大量用\n拼换行迁移后要改成chr(10)或者用 PG 的转义字符串E\n。这个坑特别隐蔽因为通常不会报错只是返回值变了。最典型的是路径字符串和正则表达式原本“看起来对”的结果会悄悄变掉。3. 自增主键迁移从 AUTO_INCREMENT 到 SERIAL 与 IDENTITY3.1 三种写法的对应关系MySQL 的自增主键写法大家很熟CREATE TABLE users ( id BIGINT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, PRIMARY KEY (id) );PG 有两条路可以走。老一点的方式是用SERIAL伪类型CREATE TABLE users ( id serial PRIMARY KEY, username varchar(50) NOT NULL );SERIAL本质上是自动帮你创建了一个序列sequence然后默认取下一个值。现代 PG 更推荐标准写法CREATE TABLE users ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, username varchar(50) NOT NULL );这里我特意用了BY DEFAULT而不是ALWAYS。原因很实际MySQL 允许你显式插入自增列的值老业务代码里很可能有这类操作。GENERATED ALWAYS默认会拦截显式插入你得额外写OVERRIDING SYSTEM VALUE迁移阶段没必要给自己加这种负担。等业务完全稳定再考虑收紧成ALWAYS也不迟。3.2 插入后拿新 ID 的方式变了MySQL 老代码经常这样拿自增 IDINSERT INTO users (username) VALUES (neo); SELECT LAST_INSERT_ID();PG 更推荐直接用RETURNINGINSERT INTO users (username) VALUES (neo) RETURNING id;这种方式的好处是单条 SQL 就能拿到完整行数据而且是事务内安全的不容易出现连接串行执行时取错 ID 的问题。批量插入时也一样INSERT INTO users (username) VALUES (a), (b), (c) RETURNING id, username;如果你用的是 ORM 或 JDBC 的getGeneratedKeys()PG 驱动也支持但底层实际上也是帮你走RETURNING。所以裸 SQL 场景里直接用RETURNING最直观。3.3 手动重置自增起点老项目经常有“清掉测试数据后把自增 ID 回到某个值”的操作。MySQL 写法是ALTER TABLE users AUTO_INCREMENT 10000;PG 里分两种情况。如果是serialALTER SEQUENCE users_id_seq RESTART WITH 10000;如果是GENERATED AS IDENTITYALTER TABLE users ALTER COLUMN id RESTART WITH 10000;注意serial自动创建的序列名一般是“表名_列名_seq”这个命名容易被忽略但重置时必须写对。4. 几个高频 DML 语法差异逐个对齐4.1 UPSERTON DUPLICATE KEY UPDATE换成ON CONFLICTMySQL 里很常见的写法INSERT INTO users (id, email, name) VALUES (1, aexample.com, A) ON DUPLICATE KEY UPDATE name VALUES(name);PG 的等价写法是INSERT INTO users (id, email, name) VALUES (1, aexample.com, A) ON CONFLICT (id) DO UPDATE SET name EXCLUDED.name;两个容易踩的差异点第一PG 不认VALUES()函数冲突行要引用EXCLUDED这个虚拟表。第二ON CONFLICT DO UPDATE必须指定冲突目标。比如表上有两个唯一键你只说ON CONFLICT DO UPDATE不写具体列名PG 会报错因为它在多个唯一索引下不知道该按哪个来判断冲突。MySQL 的ON DUPLICATE KEY UPDATE不需要指定它是碰到任意唯一键冲突就触发。这个语义差异会导致同一个 SQL 在 PG 上必须补上明确的冲突目标。如果你原本用的是INSERT IGNOREPG 里对应的是ON CONFLICT DO NOTHING这个可以不指定目标INSERT INTO users (id, email) VALUES (1, aexample.com) ON CONFLICT DO NOTHING;4.2 UPDATE/DELETE 后面接 LIMITPG 不直接支持在 MySQL 里分批更新或删除经常这么写DELETE FROM logs WHERE status 0 LIMIT 500; UPDATE tasks SET retry_count retry_count 1 WHERE status 0 LIMIT 100;PG 不支持UPDATE ... LIMIT也不支持DELETE ... LIMIT。正确做法是用 CTE 先取出目标 ID再做操作WITH ids AS ( SELECT id FROM logs WHERE status 0 ORDER BY id LIMIT 500 ) DELETE FROM logs USING ids WHERE logs.id ids.id;更新类似WITH ids AS ( SELECT id FROM tasks WHERE status 0 ORDER BY created_at LIMIT 100 ) UPDATE tasks SET retry_count retry_count 1 FROM ids WHERE tasks.id ids.id;这里多说一句如果分批处理不关心“取前多少行里的哪些行”确实可以只写LIMIT不写ORDER BY但结果就不确定了。MySQL 和 PG 都是如此。而既然要对数据做更新/删除我建议无论如何都加上ORDER BY至少保证操作行为和预期一致。4.3 严格 GROUP BY 与“每组取某一行”的改写前面说过老 MySQL 允许GROUP BY时直接选出不在分组里的列。这种 SQL 到了 PG 会直接被拒。正确的处理方式不是到处找ANY_VALUE平替而是把业务需求问清楚如果只是想要分组后的某个聚合值就写聚合SELECT user_id, MAX(age) FROM profile GROUP BY user_id;如果是要“每个用户的最近一条记录”那用DISTINCT ON比聚合更合适SELECT DISTINCT ON (user_id) user_id, age, created_at FROM profile ORDER BY user_id, created_at DESC;DISTINCT ON是 PG 的特色语法非常适合“每组取一行”的场景。迁移时如果遇到这类查询别硬改成GROUP BY再在外面套子查询DISTINCT ON会更清爽。4.4 NULL 排序、整数除法与字符串拼接这几个点看着小真遇到时数据对不上最容易查半天。先说 NULL 排序。MySQL 默认升序时 NULL 排最前降序时 NULL 排最后PG 默认升序时 NULL 排最后降序时 NULL 排最前因为 PG 把 NULL 默认当成“比任何值都大”。如果你想保持 MySQL 的排序结果迁移时要显式写原 MySQL 写法意图PG 迁移写法ORDER BY col ASCNULL 在头部ORDER BY col ASC NULLS FIRSTORDER BY col DESCNULL 在尾部ORDER BY col DESC NULLS LAST再说除法。MySQL 里SELECT 5 / 2;返回2.50这样的小数PG 里整数相除默认得到整数5 / 2是2。如果你需要保留小数必须让其中一个操作数是浮点数或 numericSELECT 5::numeric / 2; SELECT 5.0 / 2;这种差异会影响报表、统计类 SQL 的精度迁移后要逐条检查。最后说拼接。MySQL 老代码爱用CONCATPG 也有CONCAT但两者对 NULL 的处理不一样。SELECT CONCAT(NULL, abc);在 MySQL 里结果是 NULL在 PG 里结果是abc因为 PG 的concat会跳过 NULL。如果你依赖“只要有一项为 NULL 结果就是 NULL”的业务逻辑需要自己写CASE WHEN处理或者用NULLIF包裹。用||拼接时也注意PG 的||对非文本类型不一定会隐式转换建议把数字先转成text再拼。5. DDL 建表语句与类型映射粘贴即报错的高发区5.1 没有 ENGINE也没有 DEFAULT CHARSETMySQL 建表时常见的尾巴) ENGINEInnoDB DEFAULT CHARSETutf8mb4;在 PG 里这两个选项都不存在。PG 表的默认存储引擎就是“事务性存储”你不需要也不能指定ENGINEInnoDB。字符集也不是表级别设置的而是数据库集群在初始化时定好。所以迁移建表脚本时直接删掉这两段即可。这里要特别提醒一个性能语义变化如果你的 MySQL 表之前是 MyISAMSELECT COUNT(*)会非常快因为它有表级计数。PG 没有这种“非事务表”COUNT(*)需要扫描数据大表上会明显变慢。迁移后如果业务对这个查询性能敏感别硬扛考虑用统计信息、物化视图或业务侧自增计数来替代。5.2 常见类型对照表MySQL 类型PG 推荐映射说明TINYINT(1)boolean或smallint如果只存 0/1直接映射成 booleanINT UNSIGNEDbigint无符号 int 最大值仍然在 bigint 范围内BIGINT UNSIGNEDnumeric(20,0)超过bigint范围时用 numeric 兜底DATETIMEtimestamp without time zone原类型不带时区保持语义TIMESTAMPtimestamp with time zoneMySQL 的 timestamp 实际受会话时区影响TEXT/BLOBtext/byteaPG 的 text 可以存大对象不需要长度限制JSONjsonb一般建议用 jsonb性能更好、支持索引ENUM(a,b)text CHECK 约束 或 PG 内置 enumMySQL 的 enum 变更成本低PG 变更 enum 成本高需权衡建表时还常见VARCHAR(255)这个两个数据库都支持不用太担心。真正要留意的是UNSIGNEDPG 没有UNSIGNED选项。如果只是INT UNSIGNED映射成bigint通常没问题但遇到BIGINT UNSIGNED且业务确实会超过bigint上限时就只能用numeric(20,0)了。5.3 ALTER TABLE 的语法习惯要改MySQL 改列常用MODIFY COLUMNALTER TABLE users MODIFY COLUMN age INT NOT NULL;PG 用的是两步走ALTER TABLE users ALTER COLUMN age SET NOT NULL;改类型则是ALTER TABLE users ALTER COLUMN age TYPE bigint USING age::bigint;USING这一步很重要。当 PG 不能自动完成类型转换时比如字符串转数字、字符串转日期你必须告诉它怎么转。如果不加USING很多TYPE操作会直接报错。5.4 索引写法差异MySQL 允许在建表语句里内联定义索引CREATE TABLE t ( id INT PRIMARY KEY, name VARCHAR(50), KEY idx_name (name) );PG 通常把索引独立写在建表语句后面CREATE TABLE t ( id int PRIMARY KEY, name varchar(50) ); CREATE INDEX idx_name ON t (name);大表上加索引建议用 PG 的CREATE INDEX CONCURRENTLY避免长时间阻塞读写。这个关键词在 MySQL 里没有算是一个迁移时需要顺手改掉的习惯。6. 日期函数、聚合函数与正则表达式的“翻译表”6.1 日期时间函数对照MySQLPG说明NOW()now()或CURRENT_TIMESTAMP差异不大CURDATE()CURRENT_DATE返回当前日期DATE_FORMAT(d, %Y-%m-%d)to_char(d, YYYY-MM-DD)格式符写法完全不同STR_TO_DATE(s, %Y-%m-%d)to_date(s, YYYY-MM-DD)字符串转日期DATEDIFF(a, b)a::date - b::datePG 日期相减就是整数天数DATE_ADD(d, INTERVAL 1 DAY)d interval 1 dayPG 的 interval 写法固定UNIX_TIMESTAMP()extract(epoch from now())返回浮点秒数FROM_UNIXTIME(ts)to_timestamp(ts)注意返回 timestamptzDATE_FORMAT的格式符是最容易出错的。%Y-%m-%d %H:%i:%s在 PG 里对应YYYY-MM-DD HH24:MI:SS很多报表 SQL 都被这个点卡过。6.2 聚合与 NULL 处理相关函数MySQL 的IFNULL(a, b)在 PG 里是COALESCE(a, b)MySQL 的IF(condition, a, b)建议改成标准CASE WHEN condition THEN a ELSE b END。GROUP_CONCAT是 MySQL 非常高频的函数PG 里的等价物是string_agg-- MySQL SELECT user_id, GROUP_CONCAT(DISTINCT tag SEPARATOR ,) FROM user_tags GROUP BY user_id; -- PG SELECT user_id, string_agg(DISTINCT tag, ,) FROM user_tags GROUP BY user_id;如果原来的GROUP_CONCAT里有排序需求比如GROUP_CONCAT(tag ORDER BY tag SEPARATOR ,)对应 PG 的写法是SELECT user_id, string_agg(tag, , ORDER BY tag) FROM user_tags GROUP BY user_id;6.3 正则会话要换操作符MySQL 用的是REGEXP关键字SELECT * FROM users WHERE email REGEXP ^[a-z]example\\.com$;PG 用的是一组操作符语义MySQL 写法PG 写法匹配col REGEXP patterncol ~ pattern不匹配col NOT REGEXP patterncol !~ pattern忽略大小写匹配col REGEXP pattern非二进制字符串通常不区分大小写col ~* pattern忽略大小写不匹配col NOT REGEXP patterncol !~* pattern这里有一个来自 MySQL 的隐藏差异MySQL 的REGEXP在普通字符串场景下大多不区分大小写而 PG 的~是区分大小写的。迁移时如果你的老查询依赖“不区分大小写”只把REGEXP换成~还不够应该换成~*。7. 迁移实操顺序与自查清单7.1 先用工具把表结构和数据搬过来最好的策略是先让“数据底座”跑起来再逐条修 SQL。我在迁移时用的是pgloader它可以连接 MySQL自动完成建表、类型转换和数据导入基本命令类似这样pgloader mysql://user:pass127.0.0.1:3306/app postgresql://user:pass127.0.0.1:5432/app但你要清醒一点这类工具解决的是“表结构和数据”不是“业务 SQL”。它不会帮你把ON DUPLICATE KEY UPDATE、GROUP_CONCAT、REGEXP翻译掉。工具只是减少体力活改 SQL 仍然要人工做。7.2 在代码仓库里 grep 这些关键词迁移期我一般会在代码库全文搜索下面这些关键词基本能定位九成以上的问题点反引号例如id这种写法AUTO_INCREMENTENGINE建表配置ON DUPLICATE KEY UPDATEINSERT IGNOREGROUP_CONCATDATE_FORMATUNIX_TIMESTAMPIFNULLREGEXPUPDATE ... LIMIT、DELETE ... LIMIT连接串方面也别漏掉驱动类要从com.mysql.cj.jdbc.Driver换成org.postgresql.Driver端口从3306变成5432URL 前缀从jdbc:mysql://变成jdbc:postgresql://。如果原来 URL 里带了一堆 MySQL 专属参数比如useSSL、characterEncoding基本都要删掉或换成 PG 对应配置。7.3 回归验证时重点盯这几个场景迁移完不是“能连上、能跑通”就完了。我当时的做法是专门列了一张测试 SQL 清单每个场景都要人工对一遍结果插入后返回自增 ID批量 INSERT 遇到唯一键冲突包含 NULL 的排序结果整数除法结果GROUP BY查询在所有模块都能跑日期格式化输出分页查询的 total 和当前页数据正则匹配的命中列表我建议你哪怕觉得很基础也要逐条对。尤其是 NULL 排序和无符号类型这两个点很多“线上数据对不上”的问题都出在这里。最后再说一点个人体会迁移过程中如果某条 SQL 在 PG 上改起来很麻烦不要急着找一个“长得像”的函数糊弄过去。PG 之所以报错往往是因为原来的 SQL 本身就有语义含糊的地方——它逼你把业务问题想清楚这其实是件好事。凡是认真改过去的 SQL后来都成了团队 SQL 规范里最好的反面教材。 
长河君 · 2026/10/10 12:56:32 · 阅读 1.2k MySQL迁移PostgreSQL必踩的语法鸿沟与对照指南
两年前我第一次把一个维护了快六年的 MySQL 业务库迁到 PostgreSQL下面统称 PG心里想得很简单把表结构倒过去、改一下连接串SQL 应该大差不差。真正开工之后才发现卡住我的不是数据量不是服务器配置而是一堆看起来很小的语法差异。这篇文章就把我当时逐个踩过的“鸿沟”整理出来重点放在语法迁移上自增主键、引号与大小写、UPSERT、GROUP BY、NULL 排序、DDL 类型映射、日期函数和正则这些最容易让 MySQL 老手懵掉的地方。如果你也要做类似迁移或者只是想在两种数据库之间切换不踩坑可以直接参照这些对照关系来改。1. 为什么语法迁移不能靠“搜索-替换”解决1.1 两种数据库的“性格”差很远MySQL 在很长一段时间里主打“好用、够用”很多 SQL 写法是宽松的PG 则更像一个严守 SQL 标准的“考官”你写得不规范它真的会报错。举个最简单的例子下面这条 SQL 在很多老 MySQL 实例上能跑SELECT name, age, COUNT(*) FROM users GROUP BY name;但在 PG 里它会直接告诉你users.age必须出现在 GROUP BY 子句中或用于聚合函数。原因很本质当你按name分组时age到底取哪一行SQL 标准没有定义。MySQL 的宽松模式默认给了你一个“任意值”PG 不允许这种模棱两可。这类差异才是迁移成本的大头。它们不会在数据拷贝阶段暴露而是在业务代码跑起来之后一个接一个蹦出来。你很难用“搜索替换”一次解决因为问题不是某个关键字不同而是背后逻辑不同。1.2 从“宽松模式”到“严格模式”MySQL 的sql_mode可以调节不少行为比如是否允许GROUP BY选非聚合列、是否允许转成0、除法精度如何处理。PG 没有一套等价的 session 开关它的行为更像“标准模式常开”。这意味着迁移时你要把那些年在宽松模式下写出来的“野 SQL”重新按标准写一遍。这不是坏事但对排期来说确实是变量。我当时的做法是把所有待迁移的 SQL 先在 PG 上跑一遍让报错帮我们列清单而不是靠肉眼 review。1.3 迁移前先统一 SQL 规范如果你跟我一样是从一个老项目开始建议先别急着改代码先在团队里把 SQL 规范统一一下表名、列名统一小写下划线普通字符串用单引号查询显式列出列名GROUP BY里的列和SELECT里的非聚合列严格对应。这个动作能省掉后面一大半问题。否则同样的坑会在不同模块里反复出现你改完一个还有下一个。2. 引号、大小写、反斜杠最容易出“玄学报错”的入口2.1 反引号换成双引号但别滥用MySQL 里最常见的写法是用反引号包裹表名和列名SELECT id, name FROM user WHERE status 1;PG 不支持反引号它是 SQL 标准那一套用双引号SELECT id, name FROM user WHERE status 1;但这里有个容易误导人的点在 PG 里双引号会创建一个“区分大小写、保留原样”的标识符。如果你建表时字段叫Name那每次查询都得写成Name写name会找不到。所以我的建议是迁移时尽量把标识符整理成小写下划线能不加引号就不加引号。别把 MySQL 那种“每列都反引号包起来”的习惯带到 PG否则你会被大小写问题折磨死。2.2 未加引号的标识符会被折叠成小写这是 PG 和 MySQL 一个非常隐蔽的区别。在 PG 里不带引号的标识符会被自动转成小写。也就是说你建表时写CREATE TABLE UserInfo (id int);PG 实际存的名字是userinfo。之后你写SELECT * FROM UserInfoPG 会把它翻译成userinfo所以能查到。但如果你建表时用了双引号CREATE TABLE UserInfo (id int);那表名就是确确实实的UserInfo之后写SELECT * FROM UserInfo会因为被折叠成userinfo而报“表不存在”。从 MySQL 迁过来时如果老表名里有大写我建议统一改成小写或者在第一次建表时就保持不带引号的小写命名。不要在迁移后还依赖大小写混用PG 会让你很痛苦。2.3 字符串常量里的反斜杠默认不再转义MySQL 默认把\n、\这类反斜杠序列当转义符处理PG 默认standard_conforming_stringson普通字符串里反斜杠就是普通字符。举个例子SELECT a\nb;在 MySQL 里返回带换行的两行文本在 PG 里返回字面量a\nb。如果你老代码里大量用\n拼换行迁移后要改成chr(10)或者用 PG 的转义字符串E\n。这个坑特别隐蔽因为通常不会报错只是返回值变了。最典型的是路径字符串和正则表达式原本“看起来对”的结果会悄悄变掉。3. 自增主键迁移从 AUTO_INCREMENT 到 SERIAL 与 IDENTITY3.1 三种写法的对应关系MySQL 的自增主键写法大家很熟CREATE TABLE users ( id BIGINT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, PRIMARY KEY (id) );PG 有两条路可以走。老一点的方式是用SERIAL伪类型CREATE TABLE users ( id serial PRIMARY KEY, username varchar(50) NOT NULL );SERIAL本质上是自动帮你创建了一个序列sequence然后默认取下一个值。现代 PG 更推荐标准写法CREATE TABLE users ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, username varchar(50) NOT NULL );这里我特意用了BY DEFAULT而不是ALWAYS。原因很实际MySQL 允许你显式插入自增列的值老业务代码里很可能有这类操作。GENERATED ALWAYS默认会拦截显式插入你得额外写OVERRIDING SYSTEM VALUE迁移阶段没必要给自己加这种负担。等业务完全稳定再考虑收紧成ALWAYS也不迟。3.2 插入后拿新 ID 的方式变了MySQL 老代码经常这样拿自增 IDINSERT INTO users (username) VALUES (neo); SELECT LAST_INSERT_ID();PG 更推荐直接用RETURNINGINSERT INTO users (username) VALUES (neo) RETURNING id;这种方式的好处是单条 SQL 就能拿到完整行数据而且是事务内安全的不容易出现连接串行执行时取错 ID 的问题。批量插入时也一样INSERT INTO users (username) VALUES (a), (b), (c) RETURNING id, username;如果你用的是 ORM 或 JDBC 的getGeneratedKeys()PG 驱动也支持但底层实际上也是帮你走RETURNING。所以裸 SQL 场景里直接用RETURNING最直观。3.3 手动重置自增起点老项目经常有“清掉测试数据后把自增 ID 回到某个值”的操作。MySQL 写法是ALTER TABLE users AUTO_INCREMENT 10000;PG 里分两种情况。如果是serialALTER SEQUENCE users_id_seq RESTART WITH 10000;如果是GENERATED AS IDENTITYALTER TABLE users ALTER COLUMN id RESTART WITH 10000;注意serial自动创建的序列名一般是“表名_列名_seq”这个命名容易被忽略但重置时必须写对。4. 几个高频 DML 语法差异逐个对齐4.1 UPSERTON DUPLICATE KEY UPDATE换成ON CONFLICTMySQL 里很常见的写法INSERT INTO users (id, email, name) VALUES (1, aexample.com, A) ON DUPLICATE KEY UPDATE name VALUES(name);PG 的等价写法是INSERT INTO users (id, email, name) VALUES (1, aexample.com, A) ON CONFLICT (id) DO UPDATE SET name EXCLUDED.name;两个容易踩的差异点第一PG 不认VALUES()函数冲突行要引用EXCLUDED这个虚拟表。第二ON CONFLICT DO UPDATE必须指定冲突目标。比如表上有两个唯一键你只说ON CONFLICT DO UPDATE不写具体列名PG 会报错因为它在多个唯一索引下不知道该按哪个来判断冲突。MySQL 的ON DUPLICATE KEY UPDATE不需要指定它是碰到任意唯一键冲突就触发。这个语义差异会导致同一个 SQL 在 PG 上必须补上明确的冲突目标。如果你原本用的是INSERT IGNOREPG 里对应的是ON CONFLICT DO NOTHING这个可以不指定目标INSERT INTO users (id, email) VALUES (1, aexample.com) ON CONFLICT DO NOTHING;4.2 UPDATE/DELETE 后面接 LIMITPG 不直接支持在 MySQL 里分批更新或删除经常这么写DELETE FROM logs WHERE status 0 LIMIT 500; UPDATE tasks SET retry_count retry_count 1 WHERE status 0 LIMIT 100;PG 不支持UPDATE ... LIMIT也不支持DELETE ... LIMIT。正确做法是用 CTE 先取出目标 ID再做操作WITH ids AS ( SELECT id FROM logs WHERE status 0 ORDER BY id LIMIT 500 ) DELETE FROM logs USING ids WHERE logs.id ids.id;更新类似WITH ids AS ( SELECT id FROM tasks WHERE status 0 ORDER BY created_at LIMIT 100 ) UPDATE tasks SET retry_count retry_count 1 FROM ids WHERE tasks.id ids.id;这里多说一句如果分批处理不关心“取前多少行里的哪些行”确实可以只写LIMIT不写ORDER BY但结果就不确定了。MySQL 和 PG 都是如此。而既然要对数据做更新/删除我建议无论如何都加上ORDER BY至少保证操作行为和预期一致。4.3 严格 GROUP BY 与“每组取某一行”的改写前面说过老 MySQL 允许GROUP BY时直接选出不在分组里的列。这种 SQL 到了 PG 会直接被拒。正确的处理方式不是到处找ANY_VALUE平替而是把业务需求问清楚如果只是想要分组后的某个聚合值就写聚合SELECT user_id, MAX(age) FROM profile GROUP BY user_id;如果是要“每个用户的最近一条记录”那用DISTINCT ON比聚合更合适SELECT DISTINCT ON (user_id) user_id, age, created_at FROM profile ORDER BY user_id, created_at DESC;DISTINCT ON是 PG 的特色语法非常适合“每组取一行”的场景。迁移时如果遇到这类查询别硬改成GROUP BY再在外面套子查询DISTINCT ON会更清爽。4.4 NULL 排序、整数除法与字符串拼接这几个点看着小真遇到时数据对不上最容易查半天。先说 NULL 排序。MySQL 默认升序时 NULL 排最前降序时 NULL 排最后PG 默认升序时 NULL 排最后降序时 NULL 排最前因为 PG 把 NULL 默认当成“比任何值都大”。如果你想保持 MySQL 的排序结果迁移时要显式写原 MySQL 写法意图PG 迁移写法ORDER BY col ASCNULL 在头部ORDER BY col ASC NULLS FIRSTORDER BY col DESCNULL 在尾部ORDER BY col DESC NULLS LAST再说除法。MySQL 里SELECT 5 / 2;返回2.50这样的小数PG 里整数相除默认得到整数5 / 2是2。如果你需要保留小数必须让其中一个操作数是浮点数或 numericSELECT 5::numeric / 2; SELECT 5.0 / 2;这种差异会影响报表、统计类 SQL 的精度迁移后要逐条检查。最后说拼接。MySQL 老代码爱用CONCATPG 也有CONCAT但两者对 NULL 的处理不一样。SELECT CONCAT(NULL, abc);在 MySQL 里结果是 NULL在 PG 里结果是abc因为 PG 的concat会跳过 NULL。如果你依赖“只要有一项为 NULL 结果就是 NULL”的业务逻辑需要自己写CASE WHEN处理或者用NULLIF包裹。用||拼接时也注意PG 的||对非文本类型不一定会隐式转换建议把数字先转成text再拼。5. DDL 建表语句与类型映射粘贴即报错的高发区5.1 没有 ENGINE也没有 DEFAULT CHARSETMySQL 建表时常见的尾巴) ENGINEInnoDB DEFAULT CHARSETutf8mb4;在 PG 里这两个选项都不存在。PG 表的默认存储引擎就是“事务性存储”你不需要也不能指定ENGINEInnoDB。字符集也不是表级别设置的而是数据库集群在初始化时定好。所以迁移建表脚本时直接删掉这两段即可。这里要特别提醒一个性能语义变化如果你的 MySQL 表之前是 MyISAMSELECT COUNT(*)会非常快因为它有表级计数。PG 没有这种“非事务表”COUNT(*)需要扫描数据大表上会明显变慢。迁移后如果业务对这个查询性能敏感别硬扛考虑用统计信息、物化视图或业务侧自增计数来替代。5.2 常见类型对照表MySQL 类型PG 推荐映射说明TINYINT(1)boolean或smallint如果只存 0/1直接映射成 booleanINT UNSIGNEDbigint无符号 int 最大值仍然在 bigint 范围内BIGINT UNSIGNEDnumeric(20,0)超过bigint范围时用 numeric 兜底DATETIMEtimestamp without time zone原类型不带时区保持语义TIMESTAMPtimestamp with time zoneMySQL 的 timestamp 实际受会话时区影响TEXT/BLOBtext/byteaPG 的 text 可以存大对象不需要长度限制JSONjsonb一般建议用 jsonb性能更好、支持索引ENUM(a,b)text CHECK 约束 或 PG 内置 enumMySQL 的 enum 变更成本低PG 变更 enum 成本高需权衡建表时还常见VARCHAR(255)这个两个数据库都支持不用太担心。真正要留意的是UNSIGNEDPG 没有UNSIGNED选项。如果只是INT UNSIGNED映射成bigint通常没问题但遇到BIGINT UNSIGNED且业务确实会超过bigint上限时就只能用numeric(20,0)了。5.3 ALTER TABLE 的语法习惯要改MySQL 改列常用MODIFY COLUMNALTER TABLE users MODIFY COLUMN age INT NOT NULL;PG 用的是两步走ALTER TABLE users ALTER COLUMN age SET NOT NULL;改类型则是ALTER TABLE users ALTER COLUMN age TYPE bigint USING age::bigint;USING这一步很重要。当 PG 不能自动完成类型转换时比如字符串转数字、字符串转日期你必须告诉它怎么转。如果不加USING很多TYPE操作会直接报错。5.4 索引写法差异MySQL 允许在建表语句里内联定义索引CREATE TABLE t ( id INT PRIMARY KEY, name VARCHAR(50), KEY idx_name (name) );PG 通常把索引独立写在建表语句后面CREATE TABLE t ( id int PRIMARY KEY, name varchar(50) ); CREATE INDEX idx_name ON t (name);大表上加索引建议用 PG 的CREATE INDEX CONCURRENTLY避免长时间阻塞读写。这个关键词在 MySQL 里没有算是一个迁移时需要顺手改掉的习惯。6. 日期函数、聚合函数与正则表达式的“翻译表”6.1 日期时间函数对照MySQLPG说明NOW()now()或CURRENT_TIMESTAMP差异不大CURDATE()CURRENT_DATE返回当前日期DATE_FORMAT(d, %Y-%m-%d)to_char(d, YYYY-MM-DD)格式符写法完全不同STR_TO_DATE(s, %Y-%m-%d)to_date(s, YYYY-MM-DD)字符串转日期DATEDIFF(a, b)a::date - b::datePG 日期相减就是整数天数DATE_ADD(d, INTERVAL 1 DAY)d interval 1 dayPG 的 interval 写法固定UNIX_TIMESTAMP()extract(epoch from now())返回浮点秒数FROM_UNIXTIME(ts)to_timestamp(ts)注意返回 timestamptzDATE_FORMAT的格式符是最容易出错的。%Y-%m-%d %H:%i:%s在 PG 里对应YYYY-MM-DD HH24:MI:SS很多报表 SQL 都被这个点卡过。6.2 聚合与 NULL 处理相关函数MySQL 的IFNULL(a, b)在 PG 里是COALESCE(a, b)MySQL 的IF(condition, a, b)建议改成标准CASE WHEN condition THEN a ELSE b END。GROUP_CONCAT是 MySQL 非常高频的函数PG 里的等价物是string_agg-- MySQL SELECT user_id, GROUP_CONCAT(DISTINCT tag SEPARATOR ,) FROM user_tags GROUP BY user_id; -- PG SELECT user_id, string_agg(DISTINCT tag, ,) FROM user_tags GROUP BY user_id;如果原来的GROUP_CONCAT里有排序需求比如GROUP_CONCAT(tag ORDER BY tag SEPARATOR ,)对应 PG 的写法是SELECT user_id, string_agg(tag, , ORDER BY tag) FROM user_tags GROUP BY user_id;6.3 正则会话要换操作符MySQL 用的是REGEXP关键字SELECT * FROM users WHERE email REGEXP ^[a-z]example\\.com$;PG 用的是一组操作符语义MySQL 写法PG 写法匹配col REGEXP patterncol ~ pattern不匹配col NOT REGEXP patterncol !~ pattern忽略大小写匹配col REGEXP pattern非二进制字符串通常不区分大小写col ~* pattern忽略大小写不匹配col NOT REGEXP patterncol !~* pattern这里有一个来自 MySQL 的隐藏差异MySQL 的REGEXP在普通字符串场景下大多不区分大小写而 PG 的~是区分大小写的。迁移时如果你的老查询依赖“不区分大小写”只把REGEXP换成~还不够应该换成~*。7. 迁移实操顺序与自查清单7.1 先用工具把表结构和数据搬过来最好的策略是先让“数据底座”跑起来再逐条修 SQL。我在迁移时用的是pgloader它可以连接 MySQL自动完成建表、类型转换和数据导入基本命令类似这样pgloader mysql://user:pass127.0.0.1:3306/app postgresql://user:pass127.0.0.1:5432/app但你要清醒一点这类工具解决的是“表结构和数据”不是“业务 SQL”。它不会帮你把ON DUPLICATE KEY UPDATE、GROUP_CONCAT、REGEXP翻译掉。工具只是减少体力活改 SQL 仍然要人工做。7.2 在代码仓库里 grep 这些关键词迁移期我一般会在代码库全文搜索下面这些关键词基本能定位九成以上的问题点反引号例如id这种写法AUTO_INCREMENTENGINE建表配置ON DUPLICATE KEY UPDATEINSERT IGNOREGROUP_CONCATDATE_FORMATUNIX_TIMESTAMPIFNULLREGEXPUPDATE ... LIMIT、DELETE ... LIMIT连接串方面也别漏掉驱动类要从com.mysql.cj.jdbc.Driver换成org.postgresql.Driver端口从3306变成5432URL 前缀从jdbc:mysql://变成jdbc:postgresql://。如果原来 URL 里带了一堆 MySQL 专属参数比如useSSL、characterEncoding基本都要删掉或换成 PG 对应配置。7.3 回归验证时重点盯这几个场景迁移完不是“能连上、能跑通”就完了。我当时的做法是专门列了一张测试 SQL 清单每个场景都要人工对一遍结果插入后返回自增 ID批量 INSERT 遇到唯一键冲突包含 NULL 的排序结果整数除法结果GROUP BY查询在所有模块都能跑日期格式化输出分页查询的 total 和当前页数据正则匹配的命中列表我建议你哪怕觉得很基础也要逐条对。尤其是 NULL 排序和无符号类型这两个点很多“线上数据对不上”的问题都出在这里。最后再说一点个人体会迁移过程中如果某条 SQL 在 PG 上改起来很麻烦不要急着找一个“长得像”的函数糊弄过去。PG 之所以报错往往是因为原来的 SQL 本身就有语义含糊的地方——它逼你把业务问题想清楚这其实是件好事。凡是认真改过去的 SQL后来都成了团队 SQL 规范里最好的反面教材。