携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第4天，点击查看活动详情

一、设计想法

对于数据库设计，一部分参考了众多网站，一部分是根据自己的想法来的，主要是由用户和文章两方面来延伸出来的。 文章相关的有：文章表，文章详情表，类型表，标签表，评论表 用户相关的有：留言表，ip表和城市表（记录访问归属） 其他：归档表（记录网站每次更新），文件信息表，友链表

二、数据库具体的设计

这边数据库选用了MySQL5.7进行设计开发， MySQL相对于其他数据库的优势是开源，免费，而且运行速度还可以，主要是后续也相对MySQL进行一番学习，索引、锁、事务、日志等进行一番学习

/*
 Navicat Premium Data Transfer

 Source Server         : 175.178.11.114
 Source Server Type    : MySQL
 Source Server Version : 50737
 Source Host           : localhost:3306
 Source Schema         : blog

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

 Date: 05/08/2022 20:23:57
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for blogs_archive
-- ----------------------------
DROP TABLE IF EXISTS `blogs_archive`;
CREATE TABLE `blogs_archive`  (
  `blid` bigint(20) NOT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `archive_content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新原因',
  PRIMARY KEY (`blid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '归档表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for blogs_article
-- ----------------------------
DROP TABLE IF EXISTS `blogs_article`;
CREATE TABLE `blogs_article`  (
  `blid` bigint(20) NOT NULL COMMENT '主键id',
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '标题',
  `image_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '配图',
  `summary` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '简介',
  `category_id` bigint(20) NULL DEFAULT NULL COMMENT '类型id',
  `read_num` int(11) NULL DEFAULT NULL COMMENT '阅读量',
  `comment_num` int(11) NULL DEFAULT NULL COMMENT '评论数',
  `thumb_up_num` int(11) NULL DEFAULT NULL COMMENT '点赞数',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '状态',
  `is_top` int(255) NULL DEFAULT NULL COMMENT '是否置顶, 1: 置顶',
  `is_reprint` int(255) NULL DEFAULT NULL COMMENT '是否原创',
  `reprint_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '转载链接',
  `creator` bigint(20) NULL DEFAULT NULL,
  PRIMARY KEY (`blid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '文章信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for blogs_article_comment
-- ----------------------------
DROP TABLE IF EXISTS `blogs_article_comment`;
CREATE TABLE `blogs_article_comment`  (
  `blid` bigint(20) NOT NULL,
  `comment_ip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '评论ip',
  `parent_id` bigint(20) NULL DEFAULT NULL COMMENT '父类id',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '状态',
  `user_id` bigint(20) NULL DEFAULT NULL COMMENT '用户id',
  `article_id` bigint(20) NULL DEFAULT NULL COMMENT '文章id',
  `comment_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '评论内容',
  `thumb_up_num` int(5) NULL DEFAULT NULL COMMENT '点赞数',
  `to_user_id` bigint(20) NULL DEFAULT NULL COMMENT '目标用户的id',
  `creator` bigint(20) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`blid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '文章评论表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for blogs_article_info
-- ----------------------------
DROP TABLE IF EXISTS `blogs_article_info`;
CREATE TABLE `blogs_article_info`  (
  `blid` bigint(20) NOT NULL,
  `article_id` bigint(20) NULL DEFAULT NULL COMMENT '文章id',
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '文章内容',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
  `creator` bigint(20) NULL DEFAULT NULL,
  PRIMARY KEY (`blid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '文章内容表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for blogs_article_tag
-- ----------------------------
DROP TABLE IF EXISTS `blogs_article_tag`;
CREATE TABLE `blogs_article_tag`  (
  `blid` int(11) NOT NULL,
  `article_id` bigint(20) NULL DEFAULT NULL COMMENT '文章id',
  `label_id` bigint(20) NULL DEFAULT NULL COMMENT '标签id',
  `create_time` timestamp(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT NULL,
  `creator` bigint(20) NULL DEFAULT NULL,
  PRIMARY KEY (`blid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '文章标签中间表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for blogs_category
-- ----------------------------
DROP TABLE IF EXISTS `blogs_category`;
CREATE TABLE `blogs_category`  (
  `blid` bigint(20) NOT NULL,
  `type_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '类型名称',
  `num` int(11) NULL DEFAULT NULL COMMENT '该分类下文章数量',
  `status` int(255) NULL DEFAULT NULL COMMENT '状态',
  `parent_id` bigint(20) NULL DEFAULT NULL COMMENT '父类id',
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  `creator` bigint(20) NULL DEFAULT NULL,
  PRIMARY KEY (`blid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '文章分类表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for blogs_file
-- ----------------------------
DROP TABLE IF EXISTS `blogs_file`;
CREATE TABLE `blogs_file`  (
  `blid` bigint(20) NOT NULL,
  `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '文件地址',
  `explain` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '文件说明',
  `file_size` bigint(13) NULL DEFAULT NULL COMMENT '文件大小, 字节数',
  `uploaded_size` bigint(13) NULL DEFAULT NULL COMMENT '文件已上传的大小, 字节数',
  `user_id` bigint(20) NULL DEFAULT NULL COMMENT '上传用户id',
  `start_time` timestamp(0) NULL DEFAULT NULL COMMENT '上传的时间',
  `end_time` timestamp(0) NULL DEFAULT NULL COMMENT '上传完成的时间',
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '状态, 0: 正在上传, 1:已上传, 2: 已删除',
  `type_id` bigint(20) NULL DEFAULT NULL COMMENT '文件分类id',
  `thumbnail_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '缩略图',
  `creator` bigint(20) NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`blid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '文件信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for blogs_ip
-- ----------------------------
DROP TABLE IF EXISTS `blogs_ip`;
CREATE TABLE `blogs_ip`  (
  `blid` int(11) NOT NULL,
  `ip_addres` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'ip地址',
  `city` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '所属城市',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '来访时间',
  `update_time` datetime(0) NULL DEFAULT NULL,
  `creator` bigint(20) NULL DEFAULT NULL,
  PRIMARY KEY (`blid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'ip信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for blogs_label
-- ----------------------------
DROP TABLE IF EXISTS `blogs_label`;
CREATE TABLE `blogs_label`  (
  `blid` bigint(20) NOT NULL,
  `tag_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '标签名称',
  `create_time` timestamp(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT NULL,
  `creator` bigint(20) NULL DEFAULT NULL,
  PRIMARY KEY (`blid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '标签表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for blogs_message
-- ----------------------------
DROP TABLE IF EXISTS `blogs_message`;
CREATE TABLE `blogs_message`  (
  `blid` bigint(20) NOT NULL,
  `message_ip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '留言ip',
  `parent_id` bigint(20) NULL DEFAULT NULL COMMENT '父类id',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '状态',
  `user_id` bigint(20) NULL DEFAULT NULL COMMENT '用户id',
  `messgae_text` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '留言内容',
  `message_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '留言人邮箱',
  `update_time` datetime(0) NULL DEFAULT NULL,
  `creator` bigint(20) NULL DEFAULT NULL,
  PRIMARY KEY (`blid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '留言表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for city
-- ----------------------------
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city`  (
  `code` bigint(12) NOT NULL DEFAULT 0 COMMENT '代码',
  `parentCode` bigint(12) NOT NULL DEFAULT 0 COMMENT '父级代码',
  `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '名称',
  `level` tinyint(2) NOT NULL DEFAULT 1 COMMENT '1.国，2.省市，3.市，4.区县，5.街道，6.村镇',
  PRIMARY KEY (`code`) USING BTREE,
  INDEX `parentCode`(`parentCode`) USING BTREE,
  INDEX `type`(`level`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '城市' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for friend_link
-- ----------------------------
DROP TABLE IF EXISTS `friend_link`;
CREATE TABLE `friend_link`  (
  `blid` bigint(20) NOT NULL,
  `friend_link` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '友链地址',
  `friend_avater` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '站点图片地址',
  `friend_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '网站名称',
  PRIMARY KEY (`blid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '友链表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user`  (
  `blid` bigint(20) NOT NULL,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '账号',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密码',
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '账户状态',
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `nickname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '昵称',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `num` int(11) NULL DEFAULT NULL COMMENT '登录次数',
  `ipconfig` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '注册ip',
  `last_login_time` datetime(0) NULL DEFAULT NULL COMMENT '最后登录时间',
  `creator` bigint(20) NULL DEFAULT NULL,
  `avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '头像地址',
  PRIMARY KEY (`blid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

三、数据库模型图

四、补充

• SQL语句就是建表语句，欢迎大家在评论区提出改进意见
• ip的距记录是想在前端后台页面做一个可视化省份图，同时标注出来访客在各省份的来源数量
• SQL的阿里云下载链接