/*
Navicat MySQL Data Transfer
Source Server : firstNavicatConnect
Source Server Version : 80032
Source Host : localhost:3306
Source Database : school_bbs
Target Server Type : MYSQL
Target Server Version : 80032
File Encoding : 65001
Date: 2023-09-16 09:57:46
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for forum
-- ----------------------------
DROP TABLE IF EXISTS `forum`;
CREATE TABLE `forum` (
`id` int NOT NULL AUTO_INCREMENT,
`forum_name` varchar(30) COLLATE utf8mb4_general_ci NOT NULL COMMENT '板块名称',
`description` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '板块描述',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '板块创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `fk_forum_name` (`forum_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of forum
-- ----------------------------
INSERT INTO `forum` VALUES ('1', '学习天地', '学习讨论的圣地', '2023-06-06 18:18:23');
-- ----------------------------
-- Table structure for message
-- ----------------------------
DROP TABLE IF EXISTS `message`;
CREATE TABLE `message` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`sender_id` int DEFAULT NULL COMMENT '发送人id',
`recipient_id` int DEFAULT NULL COMMENT '接收人id',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '私信内容',
`is_read` tinyint(1) DEFAULT '0' COMMENT '是否已读,0为未读,1为已读',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '私信创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of message
-- ----------------------------
-- ----------------------------
-- Table structure for notice
-- ----------------------------
DROP TABLE IF EXISTS `notice`;
CREATE TABLE `notice` (
`id` int NOT NULL AUTO_INCREMENT,
`content` text COLLATE utf8mb4_general_ci NOT NULL COMMENT '公告内容',
`title` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`release_at` datetime DEFAULT CURRENT_TIMESTAMP,
`failure_at` timestamp NULL DEFAULT ((now() + interval 1 day)),
`user_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_notice_user_id` (`user_id`),
CONSTRAINT `fk_notice_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of notice
-- ----------------------------
INSERT INTO `notice` VALUES ('1', '不允许发布不良信息、色情信息、反动言论、暴力信息等不健康的内容,同时也要注意尊重他人隐私', '注意事项', '2023-06-06 11:16:42', '2023-06-06 11:16:42', '2023-06-07 11:16:42', '1');
-- ----------------------------
-- Table structure for post
-- ----------------------------
DROP TABLE IF EXISTS `post`;
CREATE TABLE `post` (
`id` int NOT NULL AUTO_INCREMENT,
`title` varchar(30) COLLATE utf8mb4_general_ci NOT NULL COMMENT '标题',
`content` text COLLATE utf8mb4_general_ci NOT NULL COMMENT '内容',
`user_id` int DEFAULT NULL COMMENT '用户id',
`forum_id` int DEFAULT NULL COMMENT '帖子id',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`last_reply_at` datetime DEFAULT NULL COMMENT '最后回复时间',
`audit_status` tinyint(1) DEFAULT '0' COMMENT '审核情况,提交未审核为0,审核未通过为1,审核已通过为2。',
PRIMARY KEY (`id`),
KEY `fk_user_id` (`user_id`),
KEY `fk_forum_id` (`forum_id`),
CONSTRAINT `fk_forum_id` FOREIGN KEY (`forum_id`) REFERENCES `forum` (`id`),
CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of post
-- ----------------------------
INSERT INTO `post` VALUES ('1', 'MySQL事务', 'MySQL事务的四个特性', '2', '1', '2023-06-06 18:19:24', null, '0');
-- ----------------------------
-- Table structure for post_favorite
-- ----------------------------
DROP TABLE IF EXISTS `post_favorite`;
CREATE TABLE `post_favorite` (
`id` int NOT NULL,
`user_id` int DEFAULT NULL COMMENT '用户id',
`post_id` int DEFAULT NULL COMMENT '帖子id',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `fk_favorite_user_id` (`user_id`),
KEY `fk_favorite_post_id` (`post_id`),
CONSTRAINT `fk_favorite_post_id` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`),
CONSTRAINT `fk_favorite_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of post_favorite
-- ----------------------------
-- ----------------------------
-- Table structure for post_like
-- ----------------------------
DROP TABLE IF EXISTS `post_like`;
CREATE TABLE `post_like` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` int DEFAULT NULL COMMENT '用户id',
`post_id` int DEFAULT NULL COMMENT '帖子id',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `fk_like_user_id` (`user_id`),
KEY `fk_like_post_id` (`post_id`),
CONSTRAINT `fk_like_post_id` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`),
CONSTRAINT `fk_like_user_id` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of post_like
-- ----------------------------
INSERT INTO `post_like` VALUES ('1', '3', '1', '2023-06-06 18:25:04');
-- ----------------------------
-- Table structure for post_tag
-- ----------------------------
DROP TABLE IF EXISTS `post_tag`;
CREATE TABLE `post_tag` (
`id` int NOT NULL AUTO_INCREMENT,
`tag_name` varchar(30) COLLATE utf8mb4_general_ci NOT NULL COMMENT '标签名称',
PRIMARY KEY (`id`),
UNIQUE KEY `fk_tag_unique` (`tag_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of post_tag
-- ----------------------------
INSERT INTO `post_tag` VALUES ('1', 'MySQL学习');
-- ----------------------------
-- Table structure for post_tag_map
-- ----------------------------
DROP TABLE IF EXISTS `post_tag_map`;
CREATE TABLE `post_tag_map` (
`id` int NOT NULL AUTO_INCREMENT,
`post_id` int DEFAULT NULL,
`tag_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_map_post_id` (`post_id`),
KEY `fk_map_tag_id` (`tag_id`),
CONSTRAINT `fk_map_post_id` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`),
CONSTRAINT `fk_map_tag_id` FOREIGN KEY (`tag_id`) REFERENCES `post_tag` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of post_tag_map
-- ----------------------------
INSERT INTO `post_tag_map` VALUES ('1', '1', '1');
-- ----------------------------
-- Table structure for reply
-- ----------------------------
DROP TABLE IF EXISTS `reply`;
CREATE TABLE `reply` (
`id` int NOT NULL AUTO_INCREMENT,
`content` text COLLATE utf8mb4_general_ci NOT NULL COMMENT '回帖内容',
`user_id` int DEFAULT NULL COMMENT '用户id',
`post_id` int DEFAULT NULL COMMENT '帖子id',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `fk_reply_user_id` (`user_id`),
KEY `fk_reply_post_id` (`post_id`),
CONSTRAINT `fk_reply_post_id` FOREIGN KEY (`post_id