聊天记录列表mysql查询,如何设计聊天数据库?

悉地网



986 3 4
3个回答

Tinywan

WebSocket教程:消息持久化实现与应用 https://mp.weixin.qq.com/s/3uQY6HrFvv-1jTXxl_z9Kw

charlescc

聊天记录表(messages)
message_id:消息唯一标识(主键)
sender_id:发送者ID(关联到用户表的user_id)
receiver_id:接收者ID(关联到用户表的user_id,如果是群聊,此处可以是群组ID)
message_type:消息类型(例如:文本、图片、视频等)
content:消息内容
timestamp:发送时间

再建一个聊天列表表(用户id,跟联系人id,唯一索引)。

聊天列表表(chat_list)
list_id:聊天列表唯一标识(主键)
user_id:用户ID (关联到用户表的user_id)
contact_id:联系人ID(对于单聊,是对方的用户ID;对于群聊,是群组ID)
last_message_id:最后一条消息的ID 关联到消息表的message_id
unread_count:未读消息数

每当一个新消息被发送,你需要执行以下操作:

在聊天记录表messages表中插入新消息记录时,聊天列表表的逻辑如下:
检查chat_list表中是否已存在与该消息相关的记录(基于user_id和contact_id)。
如果存在,更新该记录的last_message_id和增加unread_count。
如果不存在,插入一条新记录到chat_list,并设置last_message_id和unread_count。

最后查询列表 就很简单了。

SELECT cl.user_id, cl.contact_id, cl.unread_count, m.content, m.timestamp
FROM chat_list cl
JOIN messages m ON cl.last_message_id = m.message_id
WHERE cl.user_id = ?  
ORDER BY m.timestamp DESC;
  • 悉地网 2024-04-12

    多谢大佬分享,我来试一下

  • 悉地网 2024-04-13

    聊天列表用户,最后一条消息,最后发送时间

  • charlescc 2024-04-13

    有last_message_id,不就什么都有了么? 语句都写好了

  • 悉地网 2024-04-13

    太感谢,我想了3天也查询出来,赶紧试下你的方案

  • 悉地网 2024-04-17

    但是两个用户聊天是相互的啊,他们的未读消息数也是不同的
    发送一条消息chat_list表是不是要同时创建2条记录?

  • 悉地网 2024-04-17

    还得关联表查询用户资料啊
    包括头像?用户名?

  • appf 2024-04-17

    chat_list 表中 unread_count
    如果2人在时时对话,
    发送人增加未读count 和 接收人清除未读count ,
    同写sql
    大佬您的是怎么处理的

  • 悉地网 2024-04-17

    chat_list 我是同时创建2条记录?

  • appf 2024-04-17

    chat_list 发消息时创建一条就可以了,
    我表达的是
    a-b 对话 a和b都在聊天窗口

    a 发消息时 chat_list 表中的count +1
    b 在聊天窗口时,消息已读,请求清理 count =1 未0 ,a-b这一步的sq count 字段反复写入清空

    让a-b时时聊天中如何不产生 chat_list 的重复写入优化

    我在用的方法是点击进对话存入 缓存该用户对话窗口ID 让后端判断

  • 悉地网 2024-04-18

    可能你的更高级一点吧

ichynul

session对话表。
发送人id与接收人id拼一起,按大小排序。
如无论1发给2还是2发给1,记录的值都是:1_2,这样就只有一条记录。

id:1 (自增主键)
key:1_2 (两个人id按大小排序用_连接)
uid1:1  (两个人中小那个id)
uid2:2 (两个人中大那个id)
last_message_id:1 (共用,对话最新那条消息id)
last_read_message_id1:0(小id已读消息id)
last_read_message_id2:1(大id已读消息id)
unread_count_1:1(小id未读数量)
unread_count_2:0(大id未读数量)
  • 悉地网 2024-04-17

    说的好像有道理,好方法

  • 悉地网 2024-04-17

    只是
    last_read_message_id1:0(小id已读消息id)
    last_read_message_id2:1(大id已读消息id)
    这点不太明白

  • ichynul 2024-04-17

    已读id,小于等于这个id的消息是已读,大于的消息是未读。
    对话双方两个人,就有两个这种id

  • 悉地网 2024-04-17

    这个不要也没事吧?

  • 悉地网 2024-04-17

    查询时比较麻烦吧

×
🔝