数据库表创建-消息

版本归属

用于 v3 消息与历史记录版。

表清单

• messages

v3 同时开始维护以下会话字段：

• conversations.last_message_id
• conversations.last_message_at
• conversation_members.last_read_message_id
• conversation_members.last_read_at

messages

消息表。所有单聊和群聊消息统一存储在这里。

执行 SQL

CREATE TABLE IF NOT EXISTS messages (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  conversation_id BIGINT NOT NULL,
  sender_id BIGINT NOT NULL,
  client_msg_id VARCHAR(64) NOT NULL,
  message_type ENUM('text', 'image', 'file', 'system') NOT NULL,
  content JSON NOT NULL,
  status ENUM('normal', 'recalled') NOT NULL DEFAULT 'normal',
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_messages_sender_client_msg (sender_id, client_msg_id),
  KEY idx_messages_conversation_id_id (conversation_id, id),
  KEY idx_messages_sent_at (sent_at)
);

字段说明：

• id：消息 ID，也是历史消息分页游标
• conversation_id：所属会话 ID
• sender_id：发送者用户 ID
• client_msg_id：客户端生成的消息 ID，用于断线重试幂等
• message_type：消息类型，支持 text、image、file、system
• content：消息内容 JSON，不同消息类型使用不同结构
• status：消息状态，normal 表示正常展示，recalled 表示已软撤回
• sent_at：服务端确认的发送时间
• created_at：记录创建时间

索引说明：

• uk_messages_sender_client_msg：保证同一发送者重复提交同一个 client_msg_id 时只生成一条消息
• idx_messages_conversation_id_id：用于按会话分页拉取历史消息
• idx_messages_sent_at：用于按时间查询消息

消息内容示例：

文本消息：

{
  "text": "hello"
}

图片消息：

{
  "url": "https://example.com/a.png",
  "width": 800,
  "height": 600
}

文件消息：

{
  "url": "https://example.com/a.pdf",
  "name": "a.pdf",
  "size": 12345
}

业务约束：

• 发送消息前必须校验发送者是会话 active 成员
• 写入 messages 和更新 conversations.last_message_id、conversations.last_message_at 必须在同一个事务中完成
• 重复提交同一 (sender_id, client_msg_id) 时返回已有消息，不重复入库
• v3 使用 conversation_members.last_read_message_id 计算会话级未读数
• 精细到单条消息的已读回执不在 v3 落地
• 撤回消息只更新 status = 'recalled'，不删除原始 content

已有表升级到支持撤回状态：

ALTER TABLE messages
MODIFY status ENUM('normal', 'recalled') NOT NULL DEFAULT 'normal';

建表后检查

DESC messages;
SHOW INDEX FROM messages;

最小验证

以下 SQL 假设已经存在 conversation_id = 1，且 user_id = 1 是该会话 active 成员。

插入文本消息：

INSERT INTO messages (conversation_id, sender_id, client_msg_id, message_type, content)
VALUES (1, 1, 'client-001', 'text', JSON_OBJECT('text', 'hello'));

更新会话最后消息：

UPDATE conversations
SET last_message_id = LAST_INSERT_ID(), last_message_at = NOW()
WHERE id = 1;

分页查询历史消息：

SELECT id, conversation_id, sender_id, message_type, content, status, sent_at
FROM messages
WHERE conversation_id = 1 AND id < 999999999
ORDER BY id DESC
LIMIT 20;

计算用户 2 未读数：

SELECT COUNT(*) AS unread_count
FROM messages m
JOIN conversation_members cm ON cm.conversation_id = m.conversation_id
WHERE cm.conversation_id = 1
  AND cm.user_id = 2
  AND cm.status = 'active'
  AND m.sender_id <> 2
  AND m.id > COALESCE(cm.last_read_message_id, 0)
  AND m.status = 'normal';

验证消息幂等唯一：

INSERT INTO messages (conversation_id, sender_id, client_msg_id, message_type, content)
VALUES (1, 1, 'client-001', 'text', JSON_OBJECT('text', 'retry'));

预期结果：失败，命中 uk_messages_sender_client_msg。代码中应捕获该冲突并返回已有消息。

代码落地点

• models/message_model.go 定义 Message
• content 在 Go 中建议使用 json.RawMessage
• 发送消息时必须在同一事务中写入 messages 并更新 conversations
• 历史消息分页以 messages.id 作为游标
• 未读数基于 conversation_members.last_read_message_id 计算