MySQL 5.7.12：获取各collectionId最高版本的已发布记录

表结构详情 #

项目中维护的Collections表核心字段如下：

• id(INT)：记录唯一ID
• collectionId(String UUID)：集合唯一标识，同一集合的不同版本该值一致
• versionNo(INT)：版本号，同一集合内版本号递增
• status(VARCHAR)：状态，可选值为PUBLISHED/NEW/PURCHASED/DELETED/ARCHIVED

示例数据 #

id     collectionId                            versionNo   status
5   17af2c88-888d-4d9a-b7f0-dfcbac376434    1       PUBLISHED
80  17af2c88-888d-4d9a-b7f0-dfcbac376434    2       PUBLISHED
109 17af2c88-888d-4d9a-b7f0-dfcbac376434    3       NEW
6   d8451652-6b9e-426b-b883-dc8a96ec0010    1       PUBLISHED

需求说明 #

需要查询每个集合中版本号最高且状态为PUBLISHED的完整记录，期望输出如下：

id     collectionId                            versionNo   status
80  17af2c88-888d-4d9a-b7f0-dfcbac376434    2       PUBLISHED
6   d8451652-6b9e-426b-b883-dc8a96ec0010    1       PUBLISHED

现有查询的问题分析 #

你尝试的两个查询存在以下问题：

1. 第一个查询：select * from Collections where status="PUBLISHED" group by collectionId having versionNo=max(versionNo);

   • MySQL 5.7默认开启ONLY_FULL_GROUP_BY模式，GROUP BY后SELECT的字段必须是分组字段或聚合函数，SELECT *违反该规则；
   • HAVING子句中的versionNo是分组后随机选取的一条记录的版本号，无法保证等于该分组的MAX(versionNo)，逻辑不成立。

2. 第二个查询：select T1.* from Collections T1 inner join Collections T2 on T1.collectionId = T2.collectionId AND T1.id <> T2.id where T1.status="PUBLISHED" AND T1.versionNo > T2.versionNo;

   • INNER JOIN会过滤掉仅存在一条PUBLISHED版本的集合（比如示例中d8451652-6b9e-426b-b883-dc8a96ec0010），因为没有可关联的T2记录；
   • 若集合存在多个PUBLISHED版本，该查询会返回所有比其他版本号大的记录，而非仅最大版本的那条，导致结果重复。

解决方案（适配MySQL 5.7.12） #

方法一：子查询+关联原表 #

先筛选出每个集合的最高PUBLISHED版本号，再关联原表获取完整记录：

SELECT c.*
FROM Collections c
INNER JOIN (
    SELECT collectionId, MAX(versionNo) AS max_version
    FROM Collections
    WHERE status = 'PUBLISHED'
    GROUP BY collectionId
) AS sub 
    ON c.collectionId = sub.collectionId 
    AND c.versionNo = sub.max_version
    AND c.status = 'PUBLISHED';

方法二：NOT EXISTS子查询 #

通过判断当前记录是否为同集合下最高版本的PUBLISHED记录来筛选：

SELECT c1.*
FROM Collections c1
WHERE c1.status = 'PUBLISHED'
AND NOT EXISTS (
    SELECT 1
    FROM Collections c2
    WHERE c2.collectionId = c1.collectionId
    AND c2.status = 'PUBLISHED'
    AND c2.versionNo > c1.versionNo
);

这两种方法都能正确返回每个集合的最高版本PUBLISHED记录，既不会遗漏单版本集合，也不会出现重复结果。

内容的提问来源于stack exchange，提问作者Yatin Gupta