设计聊天表实现类似CSDN私信功能

要实现类似CSDN的私信功能，我们需要设计一个高效、可扩展的数据库结构。以下是完整的聊天表设计方案：

核心表结构

1. 用户表 (Users)

CREATE TABLE Users (
    user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(255),
    status ENUM('online', 'offline', 'busy') DEFAULT 'offline',
    last_active DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2. 会话表 (Conversations)

CREATE TABLE Conversations (
    conversation_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    conversation_type ENUM('private', 'group') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_message_id BIGINT,
    INDEX (last_message_id)
);

3. 会话参与者表 (ConversationParticipants)

CREATE TABLE ConversationParticipants (
    participant_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    conversation_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    left_at DATETIME,
    is_muted BOOLEAN DEFAULT FALSE,
    is_blocked BOOLEAN DEFAULT FALSE,
    unread_count INT DEFAULT 0,
    FOREIGN KEY (conversation_id) REFERENCES Conversations(conversation_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
    UNIQUE KEY (conversation_id, user_id),
    INDEX (user_id)
);

4. 消息表 (Messages)

CREATE TABLE Messages (
    message_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    conversation_id BIGINT NOT NULL,
    sender_id BIGINT NOT NULL,
    content TEXT,
    message_type ENUM('text', 'image', 'file', 'link', 'system') DEFAULT 'text',
    status ENUM('sent', 'delivered', 'read') DEFAULT 'sent',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME,
    is_recalled BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (conversation_id) REFERENCES Conversations(conversation_id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES Users(user_id) ON DELETE CASCADE,
    INDEX (conversation_id, created_at),
    INDEX (sender_id)
);

5. 消息已读状态表 (MessageReadStatus)

CREATE TABLE MessageReadStatus (
    read_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    message_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    read_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (message_id) REFERENCES Messages(message_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
    UNIQUE KEY (message_id, user_id)
);

6. 附件表 (Attachments)

CREATE TABLE Attachments (
    attachment_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    message_id BIGINT NOT NULL,
    file_url VARCHAR(255) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_size INT NOT NULL,
    file_type VARCHAR(100) NOT NULL,
    thumbnail_url VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (message_id) REFERENCES Messages(message_id) ON DELETE CASCADE
);

高级功能扩展表

7. 黑名单表 (Blacklist)

CREATE TABLE Blacklist (
    blacklist_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    blocked_user_id BIGINT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (blocked_user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
    UNIQUE KEY (user_id, blocked_user_id)
);

8. 会话设置表 (ConversationSettings)

CREATE TABLE ConversationSettings (
    setting_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    conversation_id BIGINT NOT NULL,
    notification_enabled BOOLEAN DEFAULT TRUE,
    custom_name VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (conversation_id) REFERENCES Conversations(conversation_id) ON DELETE CASCADE,
    UNIQUE KEY (user_id, conversation_id)
);

关键业务逻辑实现

创建私信会话

-- 1. 检查是否已存在会话
SELECT conversation_id FROM ConversationParticipants 
WHERE user_id IN (user1_id, user2_id)
GROUP BY conversation_id
HAVING COUNT(DISTINCT user_id) = 2;

-- 2. 如果不存在则创建新会话
START TRANSACTION;
INSERT INTO Conversations (conversation_type) VALUES ('private');
SET @conversation_id = LAST_INSERT_ID();
INSERT INTO ConversationParticipants (conversation_id, user_id) VALUES (@conversation_id, user1_id);
INSERT INTO ConversationParticipants (conversation_id, user_id) VALUES (@conversation_id, user2_id);
COMMIT;

发送消息

START TRANSACTION;
-- 1. 插入消息
INSERT INTO Messages (conversation_id, sender_id, content, status) 
VALUES (conversation_id, sender_id, 'Hello!', 'sent');
SET @message_id = LAST_INSERT_ID();

-- 2. 更新会话最后消息
UPDATE Conversations SET last_message_id = @message_id, updated_at = NOW() 
WHERE conversation_id = conversation_id;

-- 3. 更新参与者未读计数(排除发送者)
UPDATE ConversationParticipants 
SET unread_count = unread_count + 1 
WHERE conversation_id = conversation_id AND user_id != sender_id;
COMMIT;

标记消息为已读

START TRANSACTION;
-- 1. 记录已读状态
INSERT INTO MessageReadStatus (message_id, user_id) 
VALUES (message_id, user_id) 
ON DUPLICATE KEY UPDATE read_at = NOW();

-- 2. 更新未读计数
UPDATE ConversationParticipants 
SET unread_count = (
    SELECT COUNT(*) FROM Messages m
    LEFT JOIN MessageReadStatus mrs ON m.message_id = mrs.message_id AND mrs.user_id = user_id
    WHERE m.conversation_id = conversation_id 
    AND mrs.read_id IS NULL
    AND m.sender_id != user_id
)
WHERE conversation_id = conversation_id AND user_id = user_id;
COMMIT;

性能优化建议

1. 索引优化：

   • 为所有外键添加索引
   • 为频繁查询的组合条件添加复合索引
   • 为时间范围查询添加索引

2. 分表策略：

   • 对于大型系统，考虑按时间或用户ID范围对消息表进行分表

3. 缓存策略：

   • 使用Redis缓存最近会话列表
   • 缓存用户在线状态
   • 实现消息队列处理高并发写入

4. 数据归档：

   • 对历史消息进行归档处理，减少主表数据量

这个设计提供了完整的私信功能实现方案，包括核心聊天功能、已读状态跟踪、附件支持和高级设置等。根据实际业务需求，可以进一步调整或扩展表结构。