xiangqinxiangmu/service/websocket/target/classes/sql/chat_tables.sql

/*
 即时聊天系统数据库表结构
 
 设计说明：
 1. chat_conversation: 会话表，记录用户之间的聊天会话，存储在MySQL用于快速查询会话列表
 2. 聊天消息详情存储在MongoDB中，更适合海量消息存储和查询
 3. 在线状态使用Redis管理
 
 Date: 2025-10-16
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for chat_conversation
-- 聊天会话表：记录用户之间的会话信息，用于快速查询会话列表
-- ----------------------------
DROP TABLE IF EXISTS `chat_conversation`;
CREATE TABLE `chat_conversation` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '会话ID',
  `user_id` BIGINT NOT NULL COMMENT '用户ID',
  `target_user_id` BIGINT NOT NULL COMMENT '对方用户ID',
  `target_user_name` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '对方用户名称（冗余字段，减少关联查询）',
  `target_user_avatar` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '对方头像URL（冗余字段）',
  `last_message` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '最后一条消息内容',
  `last_message_type` TINYINT NULL DEFAULT 1 COMMENT '最后消息类型（1:文本 2:图片 3:语音 4:视频）',
  `last_message_time` DATETIME NULL DEFAULT NULL COMMENT '最后消息时间',
  `unread_count` INT NOT NULL DEFAULT 0 COMMENT '未读消息数量',
  `is_pinned` TINYINT NOT NULL DEFAULT 0 COMMENT '是否置顶（0:否 1:是）',
  `is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除（0:否 1:是）',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_user_target` (`user_id`, `target_user_id`) USING BTREE COMMENT '唯一索引：确保用户对用户的会话唯一',
  KEY `idx_user_time` (`user_id`, `last_message_time`) USING BTREE COMMENT '索引：用户会话列表查询，按时间倒序',
  KEY `idx_target_user` (`target_user_id`) USING BTREE COMMENT '索引：查询与某用户的所有会话'
) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='聊天会话表' ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Table structure for chat_system_message
-- 系统消息表：存储系统通知、好友申请等非聊天消息
-- ----------------------------
DROP TABLE IF EXISTS `chat_system_message`;
CREATE TABLE `chat_system_message` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '消息ID',
  `user_id` BIGINT NOT NULL COMMENT '接收用户ID',
  `message_type` TINYINT NOT NULL COMMENT '消息类型（1:系统通知 2:好友申请 3:匹配成功）',
  `title` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '消息标题',
  `content` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '消息内容',
  `extra_data` JSON NULL COMMENT '额外数据（JSON格式）',
  `is_read` TINYINT NOT NULL DEFAULT 0 COMMENT '是否已读（0:未读 1:已读）',
  `is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除（0:否 1:是）',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `read_time` DATETIME NULL DEFAULT NULL COMMENT '阅读时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_user_read` (`user_id`, `is_read`, `create_time`) USING BTREE COMMENT '索引：查询用户未读消息'
) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='系统消息表' ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Table structure for chat_friend
-- 好友关系表：存储用户之间的好友关系
-- ----------------------------
DROP TABLE IF EXISTS `chat_friend`;
CREATE TABLE `chat_friend` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '好友关系ID',
  `user_id` BIGINT NOT NULL COMMENT '用户ID',
  `friend_id` BIGINT NOT NULL COMMENT '好友ID',
  `friend_name` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '好友备注名',
  `friend_avatar` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '好友头像',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '好友状态（1:正常 2:已拉黑）',
  `is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除（0:否 1:是）',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_user_friend` (`user_id`, `friend_id`) USING BTREE COMMENT '唯一索引：确保好友关系唯一',
  KEY `idx_user` (`user_id`, `status`) USING BTREE COMMENT '索引：查询用户好友列表',
  KEY `idx_friend` (`friend_id`) USING BTREE COMMENT '索引：反向查询'
) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='好友关系表' ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Table structure for chat_message
-- 聊天消息表：存储所有聊天消息详情（改用MySQL存储）
-- ----------------------------
DROP TABLE IF EXISTS `chat_message`;
CREATE TABLE `chat_message` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '消息主键ID',
  `message_id` VARCHAR(64) NOT NULL COMMENT '消息唯一ID（UUID）',
  `conversation_id` VARCHAR(64) NOT NULL COMMENT '会话ID（格式：userId小_userId大）',
  `from_user_id` BIGINT NOT NULL COMMENT '发送者用户ID',
  `from_user_name` VARCHAR(50) NULL COMMENT '发送者用户名',
  `from_user_avatar` VARCHAR(500) NULL COMMENT '发送者头像',
  `to_user_id` BIGINT NOT NULL COMMENT '接收者用户ID',
  `to_user_name` VARCHAR(50) NULL COMMENT '接收者用户名',
  `message_type` TINYINT NOT NULL DEFAULT 1 COMMENT '消息类型（1:文本 2:图片 3:语音 4:视频 5:文件）',
  `content` TEXT NULL COMMENT '消息内容（文本内容或媒体描述）',
  `media_url` VARCHAR(500) NULL COMMENT '媒体文件URL',
  `thumbnail_url` VARCHAR(500) NULL COMMENT '缩略图URL',
  `media_size` BIGINT NULL COMMENT '文件大小（字节）',
  `duration` INT NULL COMMENT '语音/视频时长（秒）',
  `send_status` TINYINT NOT NULL DEFAULT 1 COMMENT '发送状态（1:发送中 2:已送达 3:已读 4:失败）',
  `send_time` DATETIME NOT NULL COMMENT '发送时间',
  `deliver_time` DATETIME NULL COMMENT '送达时间',
  `read_time` DATETIME NULL COMMENT '阅读时间',
  `retry_count` INT NOT NULL DEFAULT 0 COMMENT '重试次数',
  `is_recalled` TINYINT NOT NULL DEFAULT 0 COMMENT '是否已撤回（0:否 1:是）',
  `recall_time` DATETIME NULL COMMENT '撤回时间',
  `extra_data` JSON NULL COMMENT '额外数据（JSON格式）',
  `is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除（0:否 1:是）',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_message_id` (`message_id`) COMMENT '唯一索引：消息ID唯一',
  KEY `idx_conversation_time` (`conversation_id`, `send_time` DESC) COMMENT '索引：查询会话消息列表',
  KEY `idx_to_user_status` (`to_user_id`, `send_status`) COMMENT '索引：查询未送达/未读消息',
  KEY `idx_from_user_time` (`from_user_id`, `send_time`) COMMENT '索引：查询用户发送的消息'
) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='聊天消息表（MySQL存储）' ROW_FORMAT=DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;