MariaDB如何实现先排序后分组获取每个会话的最新消息

原有方案失效原因 #

MariaDB 5.7及以上版本优化器会默认忽略无LIMIT的子查询内部ORDER BY语句，你的「先排序后分组」属于不符合SQL标准的历史兼容写法，在新版本中无法保证取到分组后的首行数据，因此失效。

最优实现方案 #

MariaDB 10.2+已原生支持窗口函数，使用ROW_NUMBER()窗口函数可以精准实现每个会话取最新一条消息的需求，同时兼容群聊、私聊场景，无需拆分UNION查询。

可直接运行的查询代码 #

SELECT * FROM (
    SELECT
        conversation.id AS conversation_id,
        -- 适配群聊/私聊的会话名称
        CASE 
            WHEN conversation.is_group = 1 THEN conversation.name
            ELSE CONCAT(user2.first_name, ' ', user2.last_name) 
        END AS conversation_name,
        conversation.is_group AS conversation_isgroup,
        -- 私聊取对方用户ID，群聊返回群主ID可按需调整
        CASE
            WHEN conversation.is_group = 0 THEN (SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1)
            ELSE conversation.owner_id
        END AS conversation_owner_id,
        message.id AS message_id,
        message.type AS message_type,
        message.body AS message_body,
        message.filename AS message_filename,
        message.created_at AS message_time,
        message.user_id AS message_user_id,
        CONCAT(user.first_name, ' ', user.last_name) AS message_user_name,
        -- 按会话分组，消息ID倒序编号（自增ID比时间更精准）
        ROW_NUMBER() OVER (PARTITION BY conversation.id ORDER BY message.id DESC) AS rn
    FROM conversation
    INNER JOIN conversation_member
        ON conversation_member.conversation_id = conversation.id
        AND conversation_member.user_id = 1 -- 直接过滤当前用户的会话缩小查询范围
    LEFT JOIN message
        ON message.conversation_id = conversation.id
    LEFT JOIN user
        ON user.id = message.user_id
    LEFT JOIN user AS user2
        ON user2.id = IF(conversation.is_group = 0, 
            (SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1),
            NULL
        )
    -- 可按需调整会话过滤条件
    WHERE (conversation.is_group = 1 OR (conversation.is_group = 0 AND user2.id IS NOT NULL))
) AS session_list
WHERE rn = 1 -- 仅保留每个会话最新的一条消息
-- 所有会话按最新消息时间倒序排列，无消息的空会话排在最后
ORDER BY message_time DESC NULLS LAST;

逻辑说明 #

• 窗口函数PARTITION BY conversation.id会将会话ID相同的消息划为同一组
• 组内按消息ID倒序排序，自增ID不会出现同时间消息排序错误的问题，比用创建时间排序更精准
• rn=1过滤后每组仅保留排序第一的最新消息
• 外层排序逻辑和主流IM会话列表逻辑完全一致，如需调整空会话的排序位置修改NULLS LAST为NULLS FIRST即可
• 如果不需要保留无消息的空会话，把LEFT JOIN message改为INNER JOIN即可

内容的提问来源于stack exchange，提问作者Max Base