PHP项目站内消息如何设计数据表

本文目录导读：

1. 方案一：标准通用方案（推荐）
2. 方案二：高性能大并发方案（百万用户）
3. 设计时的一些建议

在PHP项目中设计站内消息系统,表结构的设计直接决定了系统的性能（查询速度）和功能扩展性（已读/未读、撤回、群发等）。

以下推荐两种主流的设计方案：标准方案（适合90%的通用项目） 和 高性能大并发方案（适合百万级用户）。

---

标准通用方案（推荐）

这种方案的核心思想是消息与用户关联解耦，通过“消息发送表”和“消息接收表”分离，既可以存发送详情，又方便查询某个用户的消息列表。

数据表结构

表1：messages —— 消息主表

存储消息的（发件人、标题、正文、时间）。

CREATE TABLE `messages` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT COMMENT '消息ID',
    `from_uid` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '发送者用户ID | 0表示系统消息',
    `msg_type` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '消息类型：1私信 2系统通知 3群发/公告', VARCHAR(255) NOT NULL DEFAULT '' COMMENT '消息标题',
    `content` TEXT NOT NULL COMMENT '消息正文（支持HTML或纯文本）',
    `is_draft` TINYINT NOT NULL DEFAULT 0 COMMENT '是否草稿（管理员预写）',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发送时间',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_sender` (`from_uid`, `created_at`),
    INDEX `idx_type_time` (`msg_type`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='消息主表';

表2：message_recipients —— 消息收件人表

存储谁收到了这条消息，这是查询“我的消息”时最核心的表。

CREATE TABLE `message_recipients` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT,
    `message_id` BIGINT UNSIGNED NOT NULL COMMENT '关联messages主表',
    `to_uid` INT UNSIGNED NOT NULL COMMENT '接收者用户ID',
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态：0未读 1已读 2已删除（软删除） 3撤回',
    `read_at` DATETIME DEFAULT NULL COMMENT '阅读时间',
    `del_at` DATETIME DEFAULT NULL COMMENT '删除时间',
    `is_starred` TINYINT NOT NULL DEFAULT 0 COMMENT '是否标记星标（重要消息）',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_message_user` (`message_id`, `to_uid`) COMMENT '唯一索引：确保同一条消息不会重复发同一个人',
    INDEX `idx_user_status` (`to_uid`, `status`, `id` DESC) COMMENT '最核心查询索引：按用户、状态排序'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='消息接收表';

---

核心业务SQL示例（PHP + PDO）

1 发送站内信（1对1）

// 开启事务
$pdo->beginTransaction();
// 1. 插入消息主表
$stmt = $pdo->prepare("INSERT INTO messages (from_uid, msg_type, title, content) VALUES (?, ?, ?, ?)");
$stmt->execute([$fromUid, 1, $title, $content]);
$messageId = $pdo->lastInsertId();
// 2. 批量插入收件人（即使只有1个人也用array）
$recipients = [1001, 1002, 1003]; // 收件人ID数组
$sql = "INSERT INTO message_recipients (message_id, to_uid, status) VALUES ";
$values = [];
$params = [];
foreach ($recipients as $uid) {
    $values[] = "(?, ?, 0)";
    $params[] = $messageId;
    $params[] = $uid;
}
$sql .= implode(',', $values);
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$pdo->commit();

2 查询某用户的未读消息

// 分页查询 50条未读
$sql = "SELECT m.id, m.from_uid, m.title, m.content, m.created_at, mr.status
        FROM message_recipients mr
        JOIN messages m ON m.id = mr.message_id
        WHERE mr.to_uid = ? AND mr.status = 0
        ORDER BY mr.id DESC
        LIMIT 50";
$stmt = $pdo->prepare($sql);
$stmt->execute([$currentUserId]);
$unread = $stmt->fetchAll();

3 标记为已读

$sql = "UPDATE message_recipients 
        SET status = 1, read_at = NOW() 
        WHERE to_uid = ? AND message_id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$userId, $messageId]);

4 用户删除消息（软删除）

// 注意：只标记删除，不是物理删除，且只影响自己的视角
$sql = "UPDATE message_recipients 
        SET status = 2, del_at = NOW() 
        WHERE to_uid = ? AND message_id = ?";

---

高性能大并发方案（百万用户）

当用户量很大,且消息需要群发（比如给100万用户发公告），方案一的message_recipients表写入压力极大（100万行插入），此时可以利用读写分离思想：

核心思路：

• 收件箱表（inbox）：只存 “未读”和“已读” 的消息ID，用户读完以后，可以将记录移入历史表或改为删除。
• 全量消息表（messages）+ 用户已读表（message_reads）：群发时只需要写1条消息，不写接收表，用户查询消息时，用 用户ID JOIN 已读表 判断状态。

表结构简化为：

-- 全量消息表（所有消息）
CREATE TABLE messages (同上）；
-- 用户已读记录表（只存已读的用户和消息ID）
CREATE TABLE message_reads (
    id BIGINT AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    message_id BIGINT UNSIGNED NOT NULL,
    read_at DATETIME,
    PRIMARY KEY (id),
    UNIQUE KEY (user_id, message_id)
);
-- 系统群发消息时：
INSERT INTO messages ...;  -- 只写一次
-- 用户查询自己的未读消息时：
SELECT * FROM messages
WHERE msg_type = 3  -- 系统公告
  AND id NOT IN (SELECT message_id FROM message_reads WHERE user_id = ?);

优点：群发不写接收表，节省海量IO。 缺点：查询未读消息时NOT IN性能会随着数据量增大变慢，需要配合created_at > 用户注册时间等条件做范围扫描。

---

设计时的一些建议

1. 消息类型：建议在messages表里用msg_type区分私信和系统消息，不要混在同一个逻辑里，因为系统消息往往全部用户可见，而私信只有双方可见。
2. 软删除与撤回：撤回操作建议更新messages表中is_draft或额外字段，接收方的status标记为“撤回”（也可以直接用status=3来表示撤回，撤回后接收方不能再看到消息正文）。
3. 分页查询：对message_recipients表的查询一定要使用索引覆盖，避免ORDER BY id DESC时发生文件排序。
4. 文本格式：content字段建议用TEXT，如果是富文本需要支持HTML，记得在输出时做XSS过滤（PHP使用htmlspecialchars或strip_tags）。
5. 附件支持：如果有附件，可以增加message_attachments表（id, message_id, filename, url, size），不要直接存在主表里。
6. 敏感词过滤：发送消息前（Controller层）进行敏感词过滤，不要依赖数据库。

• 大部分Web应用（用户数<10万）：用 方案一（messages + message_recipients），架构清晰，查询快。
• SaaS平台、百万级用户系统：用 方案二（messages + message_reads） 或 收件箱/发件箱模式。
• 超实时IM（如在线客服）：直接使用 WebSocket + Redis，把消息先写入消息队列（如RabbitMQ/Kafka），再异步落库，不适合直接用MySQL做实时推送。