396 lines
22 KiB
SQL
396 lines
22 KiB
SQL
/*
|
||
Navicat Premium Dump SQL
|
||
|
||
Source Server : Mac
|
||
Source Server Type : MySQL
|
||
Source Server Version : 80041 (8.0.41)
|
||
Source Host : localhost:3306
|
||
Source Schema : novel
|
||
|
||
Target Server Type : MySQL
|
||
Target Server Version : 80041 (8.0.41)
|
||
File Encoding : 65001
|
||
|
||
Date: 23/07/2025 11:36:45
|
||
*/
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for admins
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `admins`;
|
||
CREATE TABLE `admins` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '管理员ID',
|
||
`username` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '管理员用户名',
|
||
`password_hash` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '密码哈希',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` timestamp NULL DEFAULT NULL COMMENT '软删除时间戳',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_username_deleted` (`username`,`deleted_at`),
|
||
KEY `idx_email_deleted` (`deleted_at`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='系统管理员表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for authors
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `authors`;
|
||
CREATE TABLE `authors` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '作者ID',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`pen_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '笔名',
|
||
`bio` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '作者简介',
|
||
`follower_count` int unsigned NOT NULL DEFAULT '0' COMMENT '粉丝数量',
|
||
`status` tinyint NOT NULL DEFAULT '1' COMMENT '状态：1=正常，2=禁用',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` timestamp NULL DEFAULT NULL COMMENT '软删除时间戳',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_id` (`user_id`),
|
||
KEY `idx_status` (`status`),
|
||
CONSTRAINT `fk_authors_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='作者表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for book_ratings
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `book_ratings`;
|
||
CREATE TABLE `book_ratings` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '评分ID',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`book_id` bigint NOT NULL COMMENT '小说ID',
|
||
`score` decimal(4,2) NOT NULL COMMENT '评分（0~10）',
|
||
`comment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '用户评论',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_book` (`user_id`,`book_id`),
|
||
KEY `idx_book_id` (`book_id`),
|
||
CONSTRAINT `fk_book_ratings_book_id` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`),
|
||
CONSTRAINT `fk_book_ratings_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='小说评分与评论表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for book_recommendations
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `book_recommendations`;
|
||
CREATE TABLE `book_recommendations` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`book_id` bigint NOT NULL COMMENT '书籍ID，关联 books 表',
|
||
`type` tinyint NOT NULL DEFAULT '1' COMMENT '推荐类型：1=首页Banner，2=编辑推荐，3=分类推荐等',
|
||
`cover_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '推荐封面图（横图）',
|
||
`sort_order` int DEFAULT '0' COMMENT '展示排序，越小越靠前',
|
||
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用：1=启用，0=禁用',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` timestamp NULL DEFAULT NULL COMMENT '软删除时间戳',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_book_id_deleted` (`book_id`,`deleted_at`),
|
||
KEY `idx_type_sort_deleted` (`type`,`sort_order`,`deleted_at`),
|
||
CONSTRAINT `fk_book_recommendations_book_id` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='书籍推荐配置表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for books
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `books`;
|
||
CREATE TABLE `books` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '小说ID',
|
||
`author_id` bigint NOT NULL COMMENT '作者ID',
|
||
`category_id` bigint NOT NULL COMMENT '分类ID',
|
||
`title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '小说标题',
|
||
`cover_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '封面图片URL',
|
||
`description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '小说简介',
|
||
`status` tinyint NOT NULL DEFAULT '1' COMMENT '状态：1=连载中，2=完结，3=下架',
|
||
`words_count` int DEFAULT '0' COMMENT '字数',
|
||
`chapters_count` int DEFAULT '0' COMMENT '章节数',
|
||
`rating` decimal(4,2) DEFAULT '0.00' COMMENT '评分（0.00~10.00）',
|
||
`read_count` bigint DEFAULT '0' COMMENT '阅读人数',
|
||
`current_readers` bigint DEFAULT '0' COMMENT '在读人数',
|
||
`tags` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '标签（逗号分隔）',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` timestamp NULL DEFAULT NULL COMMENT '软删除时间戳',
|
||
`is_recommended` tinyint NOT NULL DEFAULT '0' COMMENT '是否推荐：0=否，1=是',
|
||
`is_featured` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否精选：0=否，1=是',
|
||
`language` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'zh' COMMENT '语言，如 zh=中文，en=英文，jp=日文',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_author_id` (`author_id`),
|
||
KEY `idx_category_id` (`category_id`),
|
||
KEY `idx_status` (`status`),
|
||
CONSTRAINT `fk_books_author_id` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`),
|
||
CONSTRAINT `fk_books_category_id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='小说表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for bookshelves
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `bookshelves`;
|
||
CREATE TABLE `bookshelves` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`book_id` bigint NOT NULL COMMENT '小说ID',
|
||
`added_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '加入书架时间',
|
||
`last_read_chapter_id` bigint DEFAULT NULL COMMENT '最后阅读章节ID',
|
||
`last_read_percent` decimal(5,2) DEFAULT '0.00' COMMENT '阅读进度百分比（0.00~100.00）',
|
||
`last_read_at` timestamp NULL DEFAULT NULL COMMENT '最后阅读时间',
|
||
`read_status` tinyint NOT NULL DEFAULT '1' COMMENT '阅读状态：1=正在读，2=已读完，3=已收藏',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_book` (`user_id`,`book_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_book_id` (`book_id`),
|
||
CONSTRAINT `fk_bookshelves_book_id` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`),
|
||
CONSTRAINT `fk_bookshelves_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户书架表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for casbin_rule
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `casbin_rule`;
|
||
CREATE TABLE `casbin_rule` (
|
||
`id` int NOT NULL AUTO_INCREMENT,
|
||
`p_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
|
||
`v0` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
|
||
`v1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
|
||
`v2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
|
||
`v3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
|
||
`v4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
|
||
`v5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='路由权限表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for categories
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `categories`;
|
||
CREATE TABLE `categories` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '分类ID',
|
||
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '分类名称',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` timestamp NULL DEFAULT NULL COMMENT '软删除时间戳',
|
||
`channel` tinyint(1) NOT NULL DEFAULT '1' COMMENT '频道类型：1=男频，2=女频',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_name` (`name`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='小说分类表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for chapters
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `chapters`;
|
||
CREATE TABLE `chapters` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '章节ID',
|
||
`book_id` bigint NOT NULL COMMENT '小说ID',
|
||
`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '章节标题',
|
||
`content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '章节内容',
|
||
`word_count` int DEFAULT '0' COMMENT '章节字数',
|
||
`sort` int DEFAULT '0' COMMENT '排序序号',
|
||
`is_locked` tinyint NOT NULL DEFAULT '0' COMMENT '是否锁定：0=免费，1=需积分解锁',
|
||
`required_score` int NOT NULL DEFAULT '0' COMMENT '解锁该章节所需积分',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` timestamp NULL DEFAULT NULL COMMENT '软删除时间戳',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_book_id` (`book_id`),
|
||
CONSTRAINT `fk_chapters_book_id` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='章节表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for feedbacks
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `feedbacks`;
|
||
CREATE TABLE `feedbacks` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '反馈ID',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '反馈内容',
|
||
`status` tinyint NOT NULL DEFAULT '1' COMMENT '处理状态：1=未处理，2=处理中，3=已处理',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '反馈时间',
|
||
`updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
CONSTRAINT `fk_feedbacks_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户反馈表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for sign_in_reward_details
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `sign_in_reward_details`;
|
||
CREATE TABLE `sign_in_reward_details` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`rule_id` bigint NOT NULL COMMENT '规则ID，关联 sign_in_reward_rules 表',
|
||
`day_number` int NOT NULL COMMENT '签到天数（1到cycle_days）',
|
||
`reward_type` tinyint NOT NULL DEFAULT '1' COMMENT '奖励类型：1=积分',
|
||
`quantity` int NOT NULL COMMENT '奖励数量，如积分数量或礼包数量',
|
||
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '记录状态：1=启用，0=禁用',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` timestamp NULL DEFAULT NULL COMMENT '软删除时间戳',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_rule_id_day_number_deleted` (`rule_id`,`day_number`,`deleted_at`) COMMENT '确保规则下每天唯一奖励',
|
||
KEY `idx_rule_id_status_deleted` (`rule_id`,`status`,`deleted_at`) COMMENT '规则ID和状态查询索引',
|
||
CONSTRAINT `fk_sign_in_reward_details_rule_id` FOREIGN KEY (`rule_id`) REFERENCES `sign_in_reward_rules` (`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='签到奖励详情表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for sign_in_reward_rules
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `sign_in_reward_rules`;
|
||
CREATE TABLE `sign_in_reward_rules` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`rule_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '规则名称，如“7天签到活动”',
|
||
`cycle_days` int NOT NULL COMMENT '奖励周期天数，如7天',
|
||
`start_date` date NOT NULL COMMENT '活动开始日期',
|
||
`end_date` date NOT NULL COMMENT '活动结束日期',
|
||
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '规则状态：1=启用，0=禁用',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` timestamp NULL DEFAULT NULL COMMENT '软删除时间戳',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_rule_name_deleted` (`rule_name`,`deleted_at`) COMMENT '确保规则名称唯一',
|
||
KEY `idx_status_start_end_deleted` (`status`,`start_date`,`end_date`,`deleted_at`) COMMENT '规则状态和活动时间查询索引'
|
||
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='签到奖励规则表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for user_chapter_purchases
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `user_chapter_purchases`;
|
||
CREATE TABLE `user_chapter_purchases` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '购买记录ID',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`book_id` bigint NOT NULL COMMENT '小说ID',
|
||
`chapter_id` bigint NOT NULL COMMENT '章节ID',
|
||
`points_used` int NOT NULL COMMENT '消耗积分数',
|
||
`purchase_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '购买时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_book_id` (`book_id`),
|
||
KEY `idx_chapter_id` (`chapter_id`),
|
||
CONSTRAINT `fk_user_chapter_purchases_book_id` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`),
|
||
CONSTRAINT `fk_user_chapter_purchases_chapter_id` FOREIGN KEY (`chapter_id`) REFERENCES `chapters` (`id`),
|
||
CONSTRAINT `fk_user_chapter_purchases_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户章节购买记录表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for user_follow_authors
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `user_follow_authors`;
|
||
CREATE TABLE `user_follow_authors` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '关注ID',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`author_id` bigint NOT NULL COMMENT '作者ID',
|
||
`followed_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '关注时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_author` (`user_id`,`author_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_author_id` (`author_id`),
|
||
CONSTRAINT `fk_user_follow_authors_author_id` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`),
|
||
CONSTRAINT `fk_user_follow_authors_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户关注作者表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for user_points_logs
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `user_points_logs`;
|
||
CREATE TABLE `user_points_logs` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '积分流水ID',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`change_type` tinyint NOT NULL COMMENT '变动类型，1=消费(spend), 2=收入(earn)',
|
||
`points_change` int NOT NULL COMMENT '积分变化数，正数增加，负数减少',
|
||
`related_order_id` bigint DEFAULT NULL COMMENT '关联ID：当change_type=1时，为chapter_purchases.id；当change_type=2时，为advertisement_records.id',
|
||
`description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '变动说明',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '变动时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_related_order_id` (`related_order_id`),
|
||
CONSTRAINT `fk_user_points_logs_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户积分流水表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for user_read_history
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `user_read_history`;
|
||
CREATE TABLE `user_read_history` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '历史记录ID',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`book_id` bigint NOT NULL COMMENT '小说ID',
|
||
`chapter_id` bigint NOT NULL COMMENT '最后阅读章节ID',
|
||
`read_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后阅读时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_book` (`user_id`,`book_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_book_id` (`book_id`),
|
||
KEY `fk_user_read_history_chapter_id` (`chapter_id`),
|
||
CONSTRAINT `fk_user_read_history_book_id` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`),
|
||
CONSTRAINT `fk_user_read_history_chapter_id` FOREIGN KEY (`chapter_id`) REFERENCES `chapters` (`id`),
|
||
CONSTRAINT `fk_user_read_history_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户阅读历史记录表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for user_read_records
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `user_read_records`;
|
||
CREATE TABLE `user_read_records` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`book_id` bigint NOT NULL COMMENT '小说ID',
|
||
`chapter_id` bigint NOT NULL COMMENT '章节ID',
|
||
`progress` int NOT NULL DEFAULT '0' COMMENT '阅读进度百分比(0-100)',
|
||
`read_at` datetime NOT NULL COMMENT '阅读时间',
|
||
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_book_chapter` (`user_id`,`book_id`,`chapter_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_book_id` (`book_id`),
|
||
KEY `idx_chapter_id` (`chapter_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户阅读记录表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for user_sign_in_logs
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `user_sign_in_logs`;
|
||
CREATE TABLE `user_sign_in_logs` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID，关联 users 表',
|
||
`rule_id` bigint NOT NULL COMMENT '规则ID，关联 sign_in_reward_rules 表',
|
||
`reward_detail_id` bigint NOT NULL COMMENT '奖励详情ID，关联 sign_in_reward_details 表',
|
||
`sign_in_date` date NOT NULL COMMENT '签到日期',
|
||
`quantity` int NOT NULL COMMENT '奖励数量，如积分数量或礼包数量',
|
||
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '记录状态：1=有效，0=无效',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` timestamp NULL DEFAULT NULL COMMENT '软删除时间戳',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_rule_sign_in_date_deleted` (`user_id`,`rule_id`,`sign_in_date`,`deleted_at`) COMMENT '确保用户在规则下每日唯一签到',
|
||
KEY `idx_user_id_rule_id_deleted` (`user_id`,`rule_id`,`deleted_at`) COMMENT '用户ID和规则ID查询索引',
|
||
KEY `fk_user_sign_in_logs_rule_id` (`rule_id`),
|
||
KEY `fk_user_sign_in_logs_reward_detail_id` (`reward_detail_id`),
|
||
CONSTRAINT `fk_user_sign_in_logs_reward_detail_id` FOREIGN KEY (`reward_detail_id`) REFERENCES `sign_in_reward_details` (`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_user_sign_in_logs_rule_id` FOREIGN KEY (`rule_id`) REFERENCES `sign_in_reward_rules` (`id`) ON DELETE CASCADE,
|
||
CONSTRAINT `fk_user_sign_in_logs_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户签到记录表';
|
||
|
||
-- ----------------------------
|
||
-- Table structure for users
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `users`;
|
||
CREATE TABLE `users` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
|
||
`username` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名',
|
||
`password_hash` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '密码哈希',
|
||
`avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '头像URL',
|
||
`email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '邮箱',
|
||
`points` bigint unsigned NOT NULL DEFAULT '0' COMMENT '积分',
|
||
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
|
||
`updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
|
||
`deleted_at` timestamp NULL DEFAULT NULL COMMENT '软删除时间戳',
|
||
`background_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '作者背景图',
|
||
`attention_count` int DEFAULT '0' COMMENT '关注他人的数量 attention count',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_username` (`username`),
|
||
KEY `idx_email_deleted` (`email`,`deleted_at`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户表';
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|