如何写 SQL 语句把 Wechat 的最近会话数据从数据库加载出来
2018/1/4...大约 2 分钟
此文章不允许转载, 违者必究...
目的: 如何写 SQL 语句把 Wechat 的
最近会话数据从数据库加载出来.
目录:
- content
<!-- Markdown 目录语法 --><!-- Vue 目录组件 -->加载下面截图所需的数据
补充一点: 下面的
最近会话需要按最后一条消息的时间排序.
数据库建模
补充一点: 使用的是 sqlite3
tb_conversation 表
| 字段 | 含义 |
|---|---|
| _id | 主键 |
| chat_with | 与谁聊天 |
| type | 0 私聊, 1 群组 ... |
tb_chat_message 表
| 字段 | 含义 |
|---|---|
| _id | 主键 |
| msg_id | 消息的唯一ID, 是个 UUID |
| chat_with | 与谁聊天 |
| timestamp | 消息的时间戳 |
| type | 0 文本, 1 图片, 3 复合消息 ... |
tb_chat_message_content_text 表
含义: 文本类型的消息内容
| 字段 | 含义 |
|---|---|
| _id | 主键 |
| msg_id | 属于哪条消息? |
| body | 具体内容, TEXT sqlite3类型 |
tb_chat_message_content_image 表
含义: 图片类型的消息内容
| 字段 | 含义 |
|---|---|
| _id | 主键 |
| msg_id | 属于哪条消息? |
| download_url | 下载地址 |
还有其他 tb_chat_message_content_XXXYYYZZZZ 表 ... 就不一一列举了
SQL 语句怎么写?
SELECT
chat_with,
tb_conversation.type as conversation_type
msg_id,
MAX(timestamp) AS tp,
FROM
tb_chat_message
JOIN tb_conversation ON tb_chat_message.chat_with = tb_conversation.chat_with
AND (
tb_conversation.type = 0
OR tb_conversation.type = 1
)
GROUP BY
chat_with
ORDER BY
tp
讲解
1 先连接查询所有最近会话的消息, 另外 sqlite3 的 JOIN 其实是 INNER JOIN, 可以加个 LEFT 在前面比较一下查询结果
JOIN tb_conversation ON tb_chat_message.chat_with = tb_conversation.chat_with
AND (
tb_conversation.type = 0
OR tb_conversation.type = 1
)
2 对上面的结果分组
GROUP BY
chat_with
3 分别为每一组选出消息时间最大的那条记录 (其实就是每个会话的最后一条消息)
MAX(timestamp) AS tp,
4 排序
ORDER BY
tp
查询速度
环境 :
- tb_chat_message 表 有 10 000 条数据
- tb_conversation 表 有 100 条数据
- 测试环境 : window7 配置一般吧...
耗时 : 0.207 s
总结
上面的内容还算比较简单, 用过 Wechat 的人都知道真实情况还包含 置顶, 草稿, 公众号, 订阅号...
这样子的排序规则可复杂了... ( _ )/~~拜拜