MySQL查询问题：未读/近10天已读消息排序异常

看起来你遇到的问题是当用OR组合未读条件和近10天条件时，排序逻辑没按预期的“未读优先”展示，对吧？这通常是因为OR会把两种条件的结果混在一起，而你的ORDER BY没有明确区分优先级，或者OR导致查询优化器无法有效利用索引，进而影响排序准确性。

我先帮你梳理核心需求：

• 优先返回message_threads中标记为未读的消息
• 已读消息仅保留最近10天内的记录
• 最终结果需按“未读在前，同优先级下按时间倒序”展示

下面给你两种可行的解决方案：

方案一：添加优先级字段，明确排序规则 #

这种方法通过给每条记录标记优先级值，让未读消息的优先级高于已读消息，再结合时间排序，确保结果完全符合预期。

假设你的message_threads表有is_unread字段（1=未读，0=已读），messages表有created_at字段记录消息时间，关联条件为messages.thread_id = message_threads.thread_id、messages.customer_id = customers.customer_id，完整查询如下：

SELECT 
    c.customer_name,
    c.customer_profile_image,
    m.message_id,
    m.contract_id,
    m.message_subject,
    m.message_content,
    mt.is_unread,
    -- 标记优先级：未读消息优先级为1，已读为0
    CASE WHEN mt.is_unread = 1 THEN 1 ELSE 0 END AS priority
FROM 
    customers c
JOIN 
    messages m ON c.customer_id = m.customer_id
JOIN 
    message_threads mt ON m.thread_id = mt.thread_id
WHERE 
    mt.is_unread = 1 
    OR (mt.is_unread = 0 AND m.created_at >= DATE_SUB(NOW(), INTERVAL 10 DAY))
ORDER BY 
    priority DESC,  -- 未读消息优先展示
    m.created_at DESC;  -- 同优先级下按消息时间倒序

为什么这个方案有效？ #

通过CASE语句生成的priority字段，让所有未读消息的优先级值更高，ORDER BY priority DESC会强制把未读消息放在最前面，之后再按消息时间排序，完美匹配你的需求。同时这个写法也避免了OR条件导致的排序逻辑混乱。

方案二：使用UNION ALL拆分查询 #

如果你的OR条件导致索引失效（比如无法利用is_unread或created_at的索引），可以用UNION ALL分别查询未读消息和近10天已读消息，再合并结果。这种方式更直观，也更容易保证排序准确性：

-- 先查询未读消息，按时间倒序
SELECT 
    c.customer_name,
    c.customer_profile_image,
    m.message_id,
    m.contract_id,
    m.message_subject,
    m.message_content,
    mt.is_unread
FROM 
    customers c
JOIN 
    messages m ON c.customer_id = m.customer_id
JOIN 
    message_threads mt ON m.thread_id = mt.thread_id
WHERE 
    mt.is_unread = 1
ORDER BY 
    m.created_at DESC

UNION ALL

-- 再查询近10天的已读消息，按时间倒序
SELECT 
    c.customer_name,
    c.customer_profile_image,
    m.message_id,
    m.contract_id,
    m.message_subject,
    m.message_content,
    mt.is_unread
FROM 
    customers c
JOIN 
    messages m ON c.customer_id = m.customer_id
JOIN 
    message_threads mt ON m.thread_id = mt.thread_id
WHERE 
    mt.is_unread = 0 
    AND m.created_at >= DATE_SUB(NOW(), INTERVAL 10 DAY)
ORDER BY 
    is_unread DESC,  -- 未读（1）在前，已读（0）在后
    created_at DESC;

注意事项： #

• UNION ALL不会去重，比UNION性能更好，因为你的两个查询条件（未读/已读）互斥，不会产生重复记录
• 最终的ORDER BY会对合并后的整个结果集生效，确保整体排序符合要求

原查询失效的原因分析 #

你之前用OR后排序失效，大概率是因为：

1. 没有明确的优先级排序字段，仅按时间排序会导致未读消息可能被旧的已读消息压在后面
2. OR条件让查询优化器无法使用合适的索引，导致排序时无法高效按预期顺序返回结果

上面两种方案都能解决问题，你可以根据自己的表结构和性能需求选择其中一种。

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