MySQL实现基于最新收发消息排序聊天联系人列表的技术问询

首先，咱们得明确核心需求：先筛选出和当前用户已建立双向连接的所有用户，然后针对每个联系人，找到你和他之间的最新消息标识，最后按这个标识倒序排序（确保最新互动的联系人置顶），同时不能漏掉那些还没和你发过消息的已连接用户。

之前尝试的问题分析 #

你之前的两个查询都有小问题：

1. UPDATE 1里的子查询是取全局的最大消息ID，不是每个联系人单独的最新消息，所以排序结果完全不对，所有用户都会用同一个最大值排序。
2. UPDATE 2用了JOIN messages，这会直接过滤掉那些还没和你发过消息的已连接用户，不符合实际需求——毕竟刚加的好友也得出现在列表里。

正确的MySQL查询方案 #

下面这个查询能完美实现你的需求，我拆解了每一步的逻辑：

SELECT 
    usr.*,
    COALESCE(latest_msg.max_msg_id, 0) AS last_message_id
FROM 
    (
        -- 第一步：获取所有和当前用户双向匹配的已连接用户ID
        SELECT swp_to AS uid 
        FROM swipes 
        WHERE swp_from = :user 
          AND first_swp IN ('like', 'superlike') 
          AND second_swp IN ('like', 'superlike')
        UNION
        SELECT swp_from AS uid 
        FROM swipes 
        WHERE swp_to = :user 
          AND first_swp IN ('like', 'superlike') 
          AND second_swp IN ('like', 'superlike')
    ) AS connected_users
-- 第二步：关联用户表，拿到联系人的详细信息
JOIN users usr ON connected_users.uid = usr.id
-- 第三步：左连接获取每个联系人的最新消息ID（没发过消息的话用0填充）
LEFT JOIN (
    SELECT 
        -- 判断每条消息的对方用户ID
        CASE 
            WHEN incoming_msg_id = :user THEN outgoing_msg_id
            ELSE incoming_msg_id
        END AS contact_id,
        MAX(id) AS max_msg_id -- 用消息ID判断新旧（自增ID越大消息越新）
    FROM messages
    WHERE incoming_msg_id = :user OR outgoing_msg_id = :user
    GROUP BY contact_id
) AS latest_msg ON connected_users.uid = latest_msg.contact_id
-- 第四步：按最新消息ID倒序，没消息的排最后
ORDER BY last_message_id DESC;

代码细节解释 #

1. connected_users子查询：用UNION自动去重，确保每个已连接用户只出现一次，不管是你发起的匹配还是对方发起的。
2. LEFT JOIN latest_msg：左连接保证没有消息的已连接用户也会被保留，COALESCE把NULL（无消息）转成0，这样排序时无消息的用户会自然排在最后。
3. latest_msg子查询：针对每条和你相关的消息，精准定位对方用户ID，然后按对方ID分组取最大的消息ID——这就是你和该联系人的最新互动标识。
4. 排序逻辑：按last_message_id倒序，最新发过消息的联系人直接顶到列表最前面。

额外优化建议 #

如果你的messages表有created_at时间字段（非常推荐添加，比依赖ID判断新旧更可靠），可以把查询里的MAX(id)换成MAX(created_at)，排序时用时间字段：

LEFT JOIN (
    SELECT 
        CASE 
            WHEN incoming_msg_id = :user THEN outgoing_msg_id
            ELSE incoming_msg_id
        END AS contact_id,
        MAX(created_at) AS last_message_time
    FROM messages
    WHERE incoming_msg_id = :user OR outgoing_msg_id = :user
    GROUP BY contact_id
) AS latest_msg ON connected_users.uid = latest_msg.contact_id
ORDER BY last_message_time DESC NULLS LAST;

NULLS LAST会明确把没有消息的用户排在最后（部分MySQL版本支持，不支持的话可以用COALESCE(last_message_time, '1970-01-01')代替）。

内容的提问来源于stack exchange，提问作者Shreyansh Kashyap