起步级-BiliBili数据库设计-分享

起步级-BiliBili数据库设计-分享

图表说明

红线 1:1 一对一

黄线 1:M / M:1 一对多或者多对一(分叉线处于哪边的为 ‘多’)

蓝线 M:M 多对多

​ 该图表为方便理解, 没有使用多对多线条表示, 使用 一对多/多对一 代替, 类似于 用户收藏列表 表示为 一对多,

​ 既一个用户收藏多个作品,

​ 但单个作品被多个用户收藏

​ 所以本质上还是多对多

tips: 看不清图片, 右键图片 > 放大图像

                                                          建表的sql 语句

/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 80039
 Source Host           : localhost:3306
 Source Schema         : bilibili_db

 Target Server Type    : MySQL
 Target Server Version : 80039
 File Encoding         : 65001

 Date: 25/12/2024 22:13:56
*/
drop database if exists bilibili_db;
create database bilibili_db;
use bilibili_db;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for collect_folder
-- ----------------------------
DROP TABLE IF EXISTS `collect_folder`;
CREATE TABLE `collect_folder`  (
                                   `cf_id` int NOT NULL AUTO_INCREMENT,
                                   `cover` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                   `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
                                   `cre_time` datetime NULL DEFAULT NULL,
                                   `remark` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                   `u_id` int NULL DEFAULT NULL,
                                   PRIMARY KEY (`cf_id`) USING BTREE,
                                   INDEX `u_id`(`u_id` ASC) USING BTREE,
                                   CONSTRAINT `collect_folder_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `user` (`u_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of collect_folder
-- ----------------------------

-- ----------------------------
-- Table structure for comment
-- ----------------------------
DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment`  (
                            `cm_id` int NOT NULL AUTO_INCREMENT,
                            `content` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                            `cre_time` datetime NULL DEFAULT NULL,
                            `status` tinyint NULL DEFAULT NULL,
                            `like_num` bigint UNSIGNED NULL DEFAULT NULL,
                            `reply_num` int NULL DEFAULT NULL,
                            `score` double NULL DEFAULT NULL,
                            `is_subcomment` bit(1) NULL DEFAULT NULL,
                            `is_ontop` bit(1) NULL DEFAULT NULL,
                            `u_id` int NULL DEFAULT NULL,
                            `iss_id` int NULL DEFAULT NULL,
                            PRIMARY KEY (`cm_id`) USING BTREE,
                            INDEX `u_id`(`u_id` ASC) USING BTREE,
                            INDEX `iss_id`(`iss_id` ASC) USING BTREE,
                            CONSTRAINT `comment_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `user` (`u_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
                            CONSTRAINT `comment_ibfk_2` FOREIGN KEY (`iss_id`) REFERENCES `issue` (`iss_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of comment
-- ----------------------------

-- ----------------------------
-- Table structure for comment_like
-- ----------------------------
DROP TABLE IF EXISTS `comment_like`;
CREATE TABLE `comment_like`  (
                                 `id` int NOT NULL AUTO_INCREMENT,
                                 `cre_time` datetime NULL DEFAULT NULL,
                                 `u_id` int NULL DEFAULT NULL,
                                 `cm_id` int NULL DEFAULT NULL,
                                 PRIMARY KEY (`id`) USING BTREE,
                                 INDEX `u_id`(`u_id` ASC) USING BTREE,
                                 INDEX `cm_id`(`cm_id` ASC) USING BTREE,
                                 CONSTRAINT `comment_like_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `user` (`u_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
                                 CONSTRAINT `comment_like_ibfk_2` FOREIGN KEY (`cm_id`) REFERENCES `comment` (`cm_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of comment_like
-- ----------------------------

-- ----------------------------
-- Table structure for comment_reply
-- ----------------------------
DROP TABLE IF EXISTS `comment_reply`;
CREATE TABLE `comment_reply`  (
                                  `id` int NOT NULL AUTO_INCREMENT,
                                  `cm_id` int NULL DEFAULT NULL,
                                  `reply_id` int NULL DEFAULT NULL,
                                  PRIMARY KEY (`id`) USING BTREE,
                                  INDEX `cm_id`(`cm_id` ASC) USING BTREE,
                                  INDEX `reply_id`(`reply_id` ASC) USING BTREE,
                                  CONSTRAINT `comment_reply_ibfk_1` FOREIGN KEY (`cm_id`) REFERENCES `comment` (`cm_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
                                  CONSTRAINT `comment_reply_ibfk_2` FOREIGN KEY (`reply_id`) REFERENCES `comment` (`cm_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of comment_reply
-- ----------------------------

-- ----------------------------
-- Table structure for danmu
-- ----------------------------
DROP TABLE IF EXISTS `danmu`;
CREATE TABLE `danmu`  (
                          `dm_id` int NOT NULL AUTO_INCREMENT,
                          `time` int NULL DEFAULT NULL,
                          `cre_time` datetime NULL DEFAULT NULL,
                          `content` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                          `color` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                          `dt_id` int NULL DEFAULT NULL,
                          `status` tinyint NULL DEFAULT NULL,
                          `like_num` bigint UNSIGNED NULL DEFAULT NULL,
                          `u_id` int NULL DEFAULT NULL,
                          PRIMARY KEY (`dm_id`) USING BTREE,
                          INDEX `u_id`(`u_id` ASC) USING BTREE,
                          INDEX `idx_danmu_user`(`dm_id` ASC, `u_id` ASC) USING BTREE,
                          CONSTRAINT `danmu_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `user` (`u_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of danmu
-- ----------------------------

-- ----------------------------
-- Table structure for danmu_like
-- ----------------------------
DROP TABLE