数据库落地执行指南

目标

本文档用于把 docs/数据库 下的表设计真正落到本地 MySQL 中，并给出建表后的检查 SQL。

当前项目默认数据库配置来自 conf/app.ini：

[mysql]
host = 127.0.0.1
port = 3306
username = root
password = admin
database = flow_talk
parse_time = true

执行前置

确认本地 MySQL 已启动，并可以使用项目配置连接：

mysql -h 127.0.0.1 -P 3306 -u root -p

创建数据库：

CREATE DATABASE IF NOT EXISTS flow_talk
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE flow_talk;

建表顺序

必须按依赖顺序执行：

1. 数据库表创建-人员：创建 users
2. 数据库表创建-会话：创建 conversations、conversation_members
3. 数据库表创建-消息：创建 messages
4. 数据库表创建-设备：创建 user_devices
5. 数据库表创建-消息回执：创建 message_receipts

一次性建表后的总检查

执行完所有建表 SQL 后，检查表是否存在：

SHOW TABLES;

预期至少包含：

users
conversations
conversation_members
messages
user_devices
message_receipts

检查表结构：

DESC users;
DESC conversations;
DESC conversation_members;
DESC messages;
DESC user_devices;
DESC message_receipts;

检查关键索引：

SHOW INDEX FROM users;
SHOW INDEX FROM conversations;
SHOW INDEX FROM conversation_members;
SHOW INDEX FROM messages;
SHOW INDEX FROM user_devices;
SHOW INDEX FROM message_receipts;

最小数据验证

以下 SQL 用于验证关键唯一约束和关系字段是否能支撑后续代码实现。

插入两个本地用户：

服务端不对本地用户密码做加密或哈希；通过注册接口创建用户时，密码同样会以明文写入 users.password：

INSERT INTO users (username, password, nickname, auth_source, status)
VALUES
  ('alice', '123456', 'Alice', 'local', 1),
  ('bob', '123456', 'Bob', 'local', 1);

创建单聊会话：

INSERT INTO conversations (type, direct_key)
VALUES ('direct', '1:2');

INSERT INTO conversation_members (conversation_id, user_id, role, status)
VALUES
  (1, 1, 'member', 'active'),
  (1, 2, 'member', '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 = 1, last_message_at = NOW()
WHERE id = 1;

标记用户 2 已读：

UPDATE conversation_members
SET last_read_message_id = 1, last_read_at = NOW()
WHERE conversation_id = 1 AND user_id = 2;

上报设备：

INSERT INTO user_devices (user_id, data)
VALUES (1, JSON_OBJECT('platform', 'web', 'device_id', 'web-001'));

写入消息回执：

INSERT INTO message_receipts (message_id, user_id, status)
VALUES (1, 2, 'read');

常见约束验证

验证用户名唯一：

INSERT INTO users (username, password, nickname, auth_source, status)
VALUES ('alice', '123456', 'Alice2', 'local', 1);

预期结果：失败，命中 uk_users_username。

验证单聊唯一：

INSERT INTO conversations (type, direct_key)
VALUES ('direct', '1:2');

预期结果：失败，命中 uk_conversations_direct_key。

验证会话成员唯一：

INSERT INTO conversation_members (conversation_id, user_id, role, status)
VALUES (1, 1, 'member', 'active');

预期结果：失败，命中 uk_conversation_members_conversation_user。

验证消息幂等唯一：

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

预期结果：失败，命中 uk_messages_sender_client_msg。

与代码实现的关系

当前 SQL 不声明数据库外键，因此代码实现必须负责以下校验：

• 写入 conversation_members 前确认 users.id 和 conversations.id 存在
• 写入 messages 前确认发送者是会话 active 成员
• 更新已读游标前确认消息属于当前会话
• 写入 user_devices 前确认请求体 user_id 来自当前登录态
• 写入 message_receipts 前确认消息和接收用户有效

这些校验应放在 models 层，controller 只负责参数绑定、当前用户读取和 HTTP 响应转换。