MySQL 迁 PG 避坑实录：GROUP BY 与分页语义差异适配清单

问题背景：能迁过去，不代表结果一致

很多 MySQL 到 PostgreSQL 的迁移项目，在结构迁移阶段看起来很顺利：表建好了、字段类型也换成了 PostgreSQL 对应类型，应用启动也没有立刻报错。但一旦进入灰度读流量或报表校验，最常见的问题集中在两个地方：GROUP BY 的合法性判断，以及分页语句的边界语义。

MySQL 在 sql_mode 未严格限制时，允许 SELECT 列表中出现未参与 GROUP BY 的非聚合列；PostgreSQL 遵循 SQL 标准，默认要求这些列在分组逻辑上可被唯一识别。另一方面，MySQL 与 PostgreSQL 都支持 LIMIT ... OFFSET ...，但“没有稳定排序时数据库如何返回数据”并不是可移植逻辑。迁移后出现翻页重复、漏数据、总数不一致，往往不是索引问题，而是 SQL 方言和语义假设没有被显式处理。

本文给出一份面向迁移实战的适配清单，覆盖问题识别、写法对比、可直接运行的 PostgreSQL SQL 示例，以及上线前必须验证的坑点。

方案对比：先统一语义，再做语法替换

迁移时不建议只做正则替换。反引号、分页关键字这类问题可以工具化，但 GROUP BY 隐含取值、排序稳定性属于业务语义，需要先确认原 SQL 到底依赖了什么行为。

常见策略有三种：

继续依赖宽松模式：PostgreSQL 没有与 MySQL ONLY_FULL_GROUP_BY 完全等价的全局开关，不能通过简单参数复制 MySQL 的宽松行为。
逐条改写 SQL：准确率最高，适合核心交易、对账、报表 SQL，建议作为默认方案。
统一分页与查询规范：把“必须唯一排序”“禁止选择未分组列”写入 DAO 或 ORM 规范，避免迁移后新代码继续产生方言债务。

实操步骤一：适配 GROUP BY 的非分组列

先准备可运行的测试表和数据：

sql

-- PostgreSQL 示例表：订单表
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id          bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    customer_id bigint NOT NULL,
    status      text NOT NULL,
    amount      numeric(12,2) NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (customer_id, status, amount, created_at) VALUES
(1, 'PAID',     100.00, '2024-01-01 10:00:00+08'),
(1, 'PAID',      80.00, '2024-01-02 10:00:00+08'),
(2, 'CANCELLED', 50.00, '2024-01-03 10:00:00+08'),
(2, 'PAID',     200.00, '2024-01-04 10:00:00+08');

MySQL 中可能存在这样的写法：按客户分组，却同时查询订单状态。

sql

-- MySQL 宽松模式下可能执行，但 status 的取值并不稳定
SELECT customer_id, status, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

PostgreSQL 会直接报错：

text

ERROR: column "orders.status" must appear in the GROUP BY clause
or be used in an aggregate function

这不是 PostgreSQL “更严格”这么简单，而是因为同一个 customer_id 下可能有多个 status，数据库无法判断你要哪一个。正确迁移方式是把业务意图表达出来。

如果只是想做状态汇总，应把 status 加入分组：

sql

-- 方案 1：客户 + 状态维度汇总，语义最清晰
SELECT
    customer_id,
    status,
    SUM(amount) AS total_amount,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, status
ORDER BY customer_id, status;

如果想取每个客户最新一笔订单的状态，再统计金额，不要依赖 MySQL 隐式取值，应使用窗口函数：

sql

-- 方案 2：先取每个客户最新订单，再关联汇总
WITH latest_order AS (
    SELECT
        customer_id,
        status,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY created_at DESC, id DESC
        ) AS rn
    FROM orders
),
customer_summary AS (
    SELECT
        customer_id,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT
    s.customer_id,
    l.status AS latest_status,
    s.total_amount
FROM customer_summary s
JOIN latest_order l
  ON l.customer_id = s.customer_id
WHERE l.rn = 1
ORDER BY s.customer_id;

如果需求是取一组值中的最大值、最小值或字符串聚合，也可以使用标准聚合函数：

sql

-- 方案 3：显式表达“取哪个状态”
SELECT
    customer_id,
    MAX(status) AS max_status,
    STRING_AGG(DISTINCT status, ',' ORDER BY status) AS all_statuses,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

实操步骤二：修复分页语义与排序不稳定

MySQL 和 PostgreSQL 都支持以下写法：

sql

SELECT id, customer_id, amount, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

但如果 created_at 不唯一，数据库只保证按 created_at 排序，不保证相同时间内的行顺序。分页执行期间若有插入、更新或执行计划变化，同一行可能在两页重复，也可能被跳过。

迁移时应把排序补成全序唯一。如果 id 是主键，推荐追加 id：

sql

-- 稳定分页：ORDER BY 的组合必须能唯一确定一行
SELECT id, customer_id, amount, created_at
FROM orders
WHERE created_at <= '2024-01-31 23:59:59+08'
ORDER BY created_at DESC, id DESC
LIMIT 10 OFFSET 0;

深分页场景下，OFFSET 越大，数据库需要跳过的行越多，PostgreSQL 中还可能因为索引扫描、排序成本上升导致响应变慢。对“下一页”场景，更推荐游标分页，也叫 keyset pagination：

sql

-- 第一页
SELECT id, customer_id, amount, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 10;

-- 假设上一页最后一行为 created_at = '2024-01-04 10:00:00+08'，id = 4
-- 第二页使用游标条件，避免 OFFSET 扫描前 10 行
SELECT id, customer_id, amount, created_at
FROM orders
WHERE (created_at, id) < (
    TIMESTAMPTZ '2024-01-04 10:00:00+08',
    4
)
ORDER BY created_at DESC, id DESC
LIMIT 10;

为了让游标分页稳定走索引，可以建立匹配排序方向的复合索引：

sql

-- 支撑 ORDER BY created_at DESC, id DESC
CREATE INDEX CONCURRENTLY IF NOT EXISTS
    idx_orders_created_id_desc
ON orders (created_at DESC, id DESC);

需要注意，(created_at, id) < (...) 这种行构造器写法与 ORDER BY created_at DESC, id DESC 的方向要严格对应。若条件方向、排序方向混用，索引利用率和结果边界都可能出问题。

迁移清单：上线前逐项核对

SQL 方言与语法适配

把 MySQL 反引号 `name` 改为 PostgreSQL 双引号 "name"，但更推荐使用不带引号的小写标识符，避免大小写敏感问题。
MySQL 的 IFNULL 改为 COALESCE，DATE()、NOW() 等函数按 PostgreSQL 类型系统重写。
MySQL 自增主键迁移为 GENERATED ALWAYS AS IDENTITY 或 serial/bigserial，新项目更推荐 identity column。
字符串字面量统一使用单引号；双引号在 PostgreSQL 中表示标识符，不表示字符串。

GROUP BY 检查项

检查 SELECT、HAVING、ORDER BY 中的非聚合列是否都在 GROUP BY 中，或能否被函数依赖唯一确定。
对 MySQL 宽松模式下的“随手取一列”，必须向业务确认最新、最大、最小、任意一个，还是应该增加分组维度。
报表 SQL 不要只验证“能执行”，还要用相同输入数据比对分组行数、汇总值和维度组合。

分页检查项

所有 LIMIT/OFFSET 查询必须有唯一排序字段，通常是业务排序字段加主键。
页面允许跳页时保留 OFFSET，但深分页应限制最大页数或改为搜索条件过滤。
无限滚动、下一页加载优先使用游标分页，并建立匹配的复合索引。

避坑总结：这些错误现象在迁移后最常见

现象：PostgreSQL 报 column must appear in the GROUP BY clause。
原因通常是直接搬迁了 MySQL 宽松模式 SQL，SELECT 中存在未分组列。不要尝试寻找全局开关绕过，应把该列加入 GROUP BY，或用 MAX、ROW_NUMBER()、STRING_AGG 等方式显式表达业务含义。
现象：分页第二页出现和第一页相同的数据。
常见写法是 ORDER BY created_at DESC LIMIT 10 OFFSET 10，但 created_at 有重复值。修正方法是追加唯一键：ORDER BY created_at DESC, id DESC，并确认排序组合在业务上唯一。
现象：迁移后字段名突然变成小写，或查询提示 relation does not exist。
MySQL 中标识符大小写规则受平台和参数影响，PostgreSQL 对带双引号的标识符大小写敏感。建表若写成 "UserOrder"，查询也必须带同样双引号。建议迁移规范统一使用小写蛇形命名，避免双引号标识符。
现象：OFFSET 深分页在 PostgreSQL 中越翻越慢。
这不是单纯调大 work_mem 就能解决的问题，数据库仍要跳过大量行。应改为游标分页，或用时间、状态、ID 范围条件收窄数据集，再配合复合索引验证执行计划。
现象：报表总数相同，但明细对不上。
MySQL 隐式选出的非分组列可能刚好符合测试数据预期，却不代表确定规则。上线前应构造同一分组内多状态、多时间、空值和重复排序值的数据，用 PostgreSQL 重写 SQL 后逐行比对。

把 GROUP BY 和分页这两类高频差异前置到迁移清单中，能显著减少切换后的 SQL 报错、结果不一致和性能回退。真正可靠的迁移不是“让 SQL 跑起来”，而是让 PostgreSQL 中的结果顺序、聚合口径和边界行为都可以被明确验证。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

MySQL 迁 PG 避坑实录：GROUP BY 与分页语义差异适配清单 ​

问题背景：能迁过去，不代表结果一致 ​

方案对比：先统一语义，再做语法替换 ​

实操步骤一：适配 GROUP BY 的非分组列 ​

实操步骤二：修复分页语义与排序不稳定 ​

迁移清单：上线前逐项核对 ​

SQL 方言与语法适配 ​

GROUP BY 检查项 ​

分页检查项 ​

避坑总结：这些错误现象在迁移后最常见 ​