lq/nl-im-service
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
nl-im-service/nl_im_plus.sql
李琦 3417856607 修复了一些问题
2026-07-08 08:18:58 +08:00

379 lines
25 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
/*
Navicat Premium Dump SQL
Source Server : 开发环境-本地
Source Server Type : MySQL
Source Server Version : 80408 (8.4.8)
Source Host : localhost:3306
Source Schema : nl_im_plus
Target Server Type : MySQL
Target Server Version : 80408 (8.4.8)
File Encoding : 65001
Date: 07/07/2026 16:44:26
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for api_request_logs
-- ----------------------------
DROP TABLE IF EXISTS `api_request_logs`;
CREATE TABLE `api_request_logs` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`route` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '请求路由',
`ip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '请求IP',
`ip_location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'IP归属地',
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '请求用户ID',
`method` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '请求方式',
`request_params` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '请求参数',
`response_params` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '返回参数',
`response_code` bigint NULL DEFAULT NULL COMMENT '返回code',
`http_status` bigint NULL DEFAULT NULL COMMENT '返回http状态',
`request_time` datetime NULL DEFAULT NULL COMMENT '请求时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_api_request_logs_route`(`route` ASC) USING BTREE,
INDEX `idx_api_request_logs_ip`(`ip` ASC) USING BTREE,
INDEX `idx_api_request_logs_user_id`(`user_id` ASC) USING BTREE,
INDEX `idx_api_request_logs_response_code`(`response_code` ASC) USING BTREE,
INDEX `idx_api_request_logs_request_time`(`request_time` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '接口请求日志表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for attachments
-- ----------------------------
DROP TABLE IF EXISTS `attachments`;
CREATE TABLE `attachments` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`uploader_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '上传者ID',
`file_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '文件名',
`file_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '文件类型',
`file_size` bigint NULL DEFAULT NULL COMMENT '文件大小（字节）',
`file_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '文件路径',
`file_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '文件URL',
`mime_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'MIME类型',
`width` bigint NULL DEFAULT NULL COMMENT '宽度',
`height` bigint NULL DEFAULT NULL COMMENT '高度',
`duration` bigint NULL DEFAULT NULL COMMENT '时长（秒）',
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_attachments_uploader_id`(`uploader_id` ASC) USING BTREE,
INDEX `idx_attachments_file_type`(`file_type` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '附件表，记录上传的文件信息' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for chat_conversations
-- ----------------------------
DROP TABLE IF EXISTS `chat_conversations`;
CREATE TABLE `chat_conversations` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户ID',
`target_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '目标ID(好友或群)',
`room_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '房间ID',
`type` bigint NULL DEFAULT NULL COMMENT '会话类型(1=私聊,2=群聊)',
`is_top` tinyint(1) NULL DEFAULT 0 COMMENT '是否置顶',
`is_muted` tinyint(1) NULL DEFAULT 0 COMMENT '是否免打扰',
`is_special_care` tinyint(1) NULL DEFAULT 0 COMMENT '是否特别关心',
`unread_count` int NULL DEFAULT 0 COMMENT '未读消息数',
`last_message` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '最后一条消息内容',
`last_time` datetime NULL DEFAULT NULL COMMENT '最后消息时间',
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
`updated_at` datetime(3) NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_chat_conversations_user_id`(`user_id` ASC) USING BTREE,
INDEX `idx_chat_conversations_target_id`(`target_id` ASC) USING BTREE,
INDEX `idx_chat_conversations_room_id`(`room_id` ASC) USING BTREE,
INDEX `idx_chat_conversations_last_time`(`last_time` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户会话表，记录每个用户的最近会话列表和未读信息' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for chat_messages
-- ----------------------------
DROP TABLE IF EXISTS `chat_messages`;
CREATE TABLE `chat_messages` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '消息唯一标识ID',
`room_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '房间ID',
`sender_user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '发送者用户ID',
`sender_ip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '发送者IP',
`ip_location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'IP归属地',
`receiver_user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '接收者用户ID',
`message_type` bigint NULL DEFAULT NULL COMMENT '消息类型',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '消息内容',
`extra` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '扩展字段，存储JSON格式的元数据',
`duration` bigint NULL DEFAULT NULL COMMENT '通话时长(秒)',
`call_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '关联的通话ID',
`call_status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '通话状态',
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_chat_messages_room_id`(`room_id` ASC) USING BTREE,
INDEX `idx_chat_messages_sender_user_id`(`sender_user_id` ASC) USING BTREE,
INDEX `idx_chat_messages_receiver_user_id`(`receiver_user_id` ASC) USING BTREE,
INDEX `idx_chat_messages_call_id`(`call_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 104 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '聊天消息表，持久化存储聊天记录，包括文本、多媒体和信令状态' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for chat_rooms
-- ----------------------------
DROP TABLE IF EXISTS `chat_rooms`;
CREATE TABLE `chat_rooms` (
`room_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '房间ID',
`room_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '房间类型',
`room_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '房间名称',
`room_avatar` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '房间头像',
`owner_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '群主ID(群聊必填,单聊为0)',
`admin_ids` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '管理员ID列表(逗号分隔)',
`creator_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建者ID',
`last_message_time` datetime NULL DEFAULT NULL COMMENT '最后消息时间',
`last_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '最后消息内容',
`announcement` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '群公告',
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
`updated_at` datetime(3) NULL DEFAULT NULL COMMENT '更新时间',
`members` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '成员列表JSON',
PRIMARY KEY (`room_id`) USING BTREE,
INDEX `idx_chat_rooms_room_type`(`room_type` ASC) USING BTREE,
INDEX `idx_chat_rooms_owner_id`(`owner_id` ASC) USING BTREE,
INDEX `idx_chat_rooms_creator_id`(`creator_id` ASC) USING BTREE,
INDEX `idx_chat_rooms_last_message_time`(`last_message_time` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '聊天房间表，支持点对点和群聊' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for contact_groups
-- ----------------------------
DROP TABLE IF EXISTS `contact_groups`;
CREATE TABLE `contact_groups` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户ID',
`group_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '分组名称',
`sort_order` bigint NULL DEFAULT 0 COMMENT '排序顺序',
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_contact_groups_user_id`(`user_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '联系人分组表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for friend_requests
-- ----------------------------
DROP TABLE IF EXISTS `friend_requests`;
CREATE TABLE `friend_requests` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`from_user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '发送者用户ID',
`to_user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '接收者用户ID',
`message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '申请消息',
`status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'pending' COMMENT '状态',
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
`updated_at` datetime(3) NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_friend_requests_from_user_id`(`from_user_id` ASC) USING BTREE,
INDEX `idx_friend_requests_to_user_id`(`to_user_id` ASC) USING BTREE,
INDEX `idx_friend_requests_status`(`status` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '好友申请表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for login_logs
-- ----------------------------
DROP TABLE IF EXISTS `login_logs`;
CREATE TABLE `login_logs` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`account` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '登录账号',
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '登录ID',
`location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '归属地',
`ip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'IP地址',
`success` tinyint(1) NULL DEFAULT NULL COMMENT '成功状态',
`login_time` datetime NULL DEFAULT NULL COMMENT '登录时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_login_logs_account`(`account` ASC) USING BTREE,
INDEX `idx_login_logs_user_id`(`user_id` ASC) USING BTREE,
INDEX `idx_login_logs_ip`(`ip` ASC) USING BTREE,
INDEX `idx_login_logs_success`(`success` ASC) USING BTREE,
INDEX `idx_login_logs_login_time`(`login_time` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '登录日志表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for moment_comments
-- ----------------------------
DROP TABLE IF EXISTS `moment_comments`;
CREATE TABLE `moment_comments` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '评论ID',
`moment_id` bigint NULL DEFAULT NULL COMMENT '动态ID',
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '评论者ID',
`reply_to_comment_id` bigint NULL DEFAULT NULL COMMENT '回复的评论ID',
`reply_to_user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '回复的用户ID',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '评论内容',
`is_deleted` tinyint(1) NULL DEFAULT 0 COMMENT '是否已删除',
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_moment_comments_moment_id`(`moment_id` ASC) USING BTREE,
INDEX `idx_moment_comments_user_id`(`user_id` ASC) USING BTREE,
INDEX `idx_moment_comments_reply_to`(`reply_to_comment_id` ASC) USING BTREE,
INDEX `idx_moment_comments_reply_to_comment_id`(`reply_to_comment_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '动态评论表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for moment_likes
-- ----------------------------
DROP TABLE IF EXISTS `moment_likes`;
CREATE TABLE `moment_likes` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`moment_id` bigint NULL DEFAULT NULL COMMENT '动态ID',
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '点赞用户ID',
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `uk_moment_user`(`moment_id` ASC, `user_id` ASC) USING BTREE,
INDEX `idx_moment_likes_moment_id`(`moment_id` ASC) USING BTREE,
INDEX `idx_moment_likes_user_id`(`user_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '动态点赞表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for moment_notifications
-- ----------------------------
DROP TABLE IF EXISTS `moment_notifications`;
CREATE TABLE `moment_notifications` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '通知ID',
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '接收通知的用户ID',
`from_user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '触发通知的用户ID',
`moment_id` bigint NULL DEFAULT NULL COMMENT '关联的动态ID',
`type` tinyint NULL DEFAULT NULL COMMENT '通知类型',
`comment_id` bigint UNSIGNED NULL DEFAULT NULL COMMENT '关联的评论ID',
`is_read` tinyint(1) NULL DEFAULT 0 COMMENT '是否已读',
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_moment_notifications_user_id`(`user_id` ASC) USING BTREE,
INDEX `idx_moment_notifications_is_read`(`is_read` ASC) USING BTREE,
INDEX `idx_moment_notifications_created_at`(`created_at` ASC) USING BTREE,
INDEX `idx_moment_notifications_moment_id`(`moment_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 23 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '朋友圈通知表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for moments
-- ----------------------------
DROP TABLE IF EXISTS `moments`;
CREATE TABLE `moments` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '动态ID',
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '发布者ID',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '文字内容',
`media_type` tinyint NULL DEFAULT 0 COMMENT '媒体类型',
`media_urls` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '媒体URL列表',
`location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '位置信息',
`visibility` tinyint NULL DEFAULT 0 COMMENT '可见性',
`visible_user_ids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '可见用户ID列表',
`mention_user_ids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '@的用户ID列表',
`topic_tags` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '话题标签',
`like_count` int NULL DEFAULT 0 COMMENT '点赞数',
`comment_count` int NULL DEFAULT 0 COMMENT '评论数',
`is_deleted` tinyint(1) NULL DEFAULT 0 COMMENT '是否已删除',
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
`updated_at` datetime(3) NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_moments_user_id`(`user_id` ASC) USING BTREE,
INDEX `idx_moments_created_at`(`created_at` ASC) USING BTREE,
INDEX `idx_moments_visibility`(`visibility` ASC) USING BTREE,
INDEX `idx_moments_is_deleted`(`is_deleted` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '朋友圈动态表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for room_members
-- ----------------------------
DROP TABLE IF EXISTS `room_members`;
CREATE TABLE `room_members` (
`room_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '房间ID',
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户ID',
`role` tinyint(1) NOT NULL DEFAULT 0 COMMENT '成员角色(0=成员,1=管理员,2=群主)',
`joined_at` datetime(3) NULL DEFAULT NULL COMMENT '加入时间',
`muted_until` datetime NULL DEFAULT NULL COMMENT '禁言到期时间',
PRIMARY KEY (`room_id`, `user_id`) USING BTREE,
INDEX `idx_room_members_user_id`(`user_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '房间成员表，存储房间与用户的关联关系' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for user_contacts
-- ----------------------------
DROP TABLE IF EXISTS `user_contacts`;
CREATE TABLE `user_contacts` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户ID',
`contact_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系人ID',
`room_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '房间ID',
`remark_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注名称',
`group_id` bigint NULL DEFAULT NULL COMMENT '分组ID',
`is_top` tinyint(1) NULL DEFAULT 0 COMMENT '是否置顶',
`is_muted` tinyint(1) NULL DEFAULT 0 COMMENT '是否免打扰',
`is_special_care` tinyint(1) NULL DEFAULT 0 COMMENT '是否特别关心',
`is_blocked` tinyint(1) NULL DEFAULT 0 COMMENT '是否拉黑',
`last_chat_time` datetime NULL DEFAULT NULL COMMENT '最后聊天时间',
`last_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '最后一条消息',
`unread_count` bigint NULL DEFAULT 0 COMMENT '未读消息数',
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
`updated_at` datetime(3) NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_user_contacts_user_id`(`user_id` ASC) USING BTREE,
INDEX `idx_user_contacts_contact_id`(`contact_id` ASC) USING BTREE,
INDEX `idx_user_contacts_room_id`(`room_id` ASC) USING BTREE,
INDEX `idx_user_contacts_group_id`(`group_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户联系人表，存储好友关系、分组、备注等信息' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户唯一ID',
`email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邮箱',
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '手机号',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '密码（加密后）',
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户名称',
`avatar` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户头像',
`desc` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户描述或签名',
`region` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '地区',
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
`updated_at` datetime(3) NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `idx_users_email`(`email` ASC) USING BTREE,
UNIQUE INDEX `idx_users_phone`(`phone` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户基本信息表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for verification_codes
-- ----------------------------
DROP TABLE IF EXISTS `verification_codes`;
CREATE TABLE `verification_codes` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`target` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邮箱或手机号',
`code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '验证码',
`type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '类型',
`expires_at` datetime NULL DEFAULT NULL COMMENT '过期时间',
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_verification_codes_target`(`target` ASC) USING BTREE,
INDEX `idx_verification_codes_expires_at`(`expires_at` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '验证码表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for message_read_receipts
-- ----------------------------
DROP TABLE IF EXISTS `message_read_receipts`;
CREATE TABLE `message_read_receipts` (
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`message_id` bigint UNSIGNED NOT NULL COMMENT '消息ID',
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '已读用户ID',
`read_at` datetime(3) NOT NULL COMMENT '已读时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `uk_msg_user`(`message_id` ASC, `user_id` ASC) USING BTREE,
INDEX `idx_message_read_receipts_user_id`(`user_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '消息已读回执表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for user_settings
-- ----------------------------
DROP TABLE IF EXISTS `user_settings`;
CREATE TABLE `user_settings` (
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户ID',
`setting_key` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '设置键',
`setting_value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '设置值',
`updated_at` datetime(3) NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`user_id`, `setting_key`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户个性化设置表' ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
Reference in New Issue View Git Blame Copy Permalink