SQL查询优化：按Item ID获取指定用户的全部双向聊天消息

核心需求：仅筛选指定Item ID（如15）下，指定用户（如ID=3）作为发送方或接收方的所有聊天消息，需排除其他Item的数据，且不能仅返回单条最新记录。

假设的chats表结构（结合常见聊天业务表设计） #

CREATE TABLE chats (
    chat_id INT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT NOT NULL,
    receiver_id INT NOT NULL,
    item_id INT NOT NULL,
    message_content TEXT NOT NULL,
    created_at DATETIME NOT NULL
);

测试插入数据 #

INSERT INTO chats (sender_id, receiver_id, item_id, message_content, created_at)
VALUES
(3, 5, 15, '你好，关于这个商品', '2024-05-20 10:00:00'),
(5, 3, 15, '请问有什么问题？', '2024-05-20 10:01:00'),
(3, 5, 16, '另一个商品的问题', '2024-05-20 10:05:00'),
(3, 7, 15, '这个Item15的消息', '2024-05-20 11:00:00'),
(7, 3, 15, '收到了', '2024-05-20 11:01:00');

修正后的查询SQL #

SELECT chat_id, sender_id, receiver_id, item_id, message_content, created_at
FROM chats
WHERE item_id = 15  -- 严格过滤目标Item ID
  AND (sender_id = 3 OR receiver_id = 3)  -- 指定用户为发送方或接收方
ORDER BY created_at ASC;  -- 按消息发送时间排序，可选

原查询错误原因分析 #

你之前的查询问题大概率出自两点：

• 未添加item_id = 15的过滤条件，导致拉取了其他Item的消息；
• 误用GROUP BY item_id分组，或搭配MAX(created_at)这类聚合函数，导致仅返回每个Item的单条最新记录。

查询结果示例 #

执行修正后的SQL后，会返回Item ID=15下用户3参与的全部4条消息，不会包含Item 16的数据，也不会丢失历史消息。

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