仿LeetCode项目后端mysql表结构设计_oj系统mysql表设计

需求

表引擎选用默认的InnoDB

用户模块

首先是用户模块，主要分为登陆信息和用户信息两大类。

注释：为什么将用户登陆和其它信息分为两个表设计？
性能上：这样做列数更少，能够更为快速查询数据。
安全上：防止在恶意查询时，用户登陆信息和敏感信息同时被查询出来，增加安全性。

登陆信息表

CREATE TABLE `user_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `identity_type` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '登陆类型，可自由指定',
  `identifier` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT 'def' COMMENT '登陆类型的唯一标识',
  `certificate` varchar(20) NOT NULL DEFAULT 'def' COMMENT '密码凭证',
  `creat_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '绑定时间',
  `update_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新后的绑定时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ONLY` (`uid`,`identity_type`) USING BTREE,
  KEY `uid_index` (`uid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='登陆信息表';
1
2
3
4
5
6
7
8
9
10
11
12

注释：key 、primary key 、unique key 区别
key Key是索引约束，对表中字段进行约束索引
主键（PRIMARY KEY）： 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表都应该有一个主键，并且每个表只能有一个主键。
唯一键（unique key）： 定义了unique约束的字段中不能包含重复值,可以为一个或多个字段定义unique约束,unique约束中可以包含null空值

用户信息表

关于时间类型的存储格式，
可以参考这一篇文章:时间类型存储格式选择

CREATE TABLE `user_base` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) unsigned NOT NULL COMMENT '用户id',
  `nickname` varchar(20) NOT NULL COMMENT '昵称',
  `gender` tinyint(1) unsigned NOT NULL COMMENT '性别 0为女 1为男',
  `member` tinyint(1) NOT NULL,
  `present_address` varchar(40) NOT NULL COMMENT '地址',
  `
birthday` int(11) NOT NULL COMMENT '用户生日',
  `individua_resume` varchar(40) NOT NULL,
  `personal website` varchar(40) NOT NULL,
  `industry` varchar(40) NOT NULL COMMENT '所在行业',
  `work_experience_1` varchar(40) NOT NULL COMMENT '工作经历1',
  `work_experience_2` varchar(40) NOT NULL COMMENT '工作经历2',
  `work_experience_3` varchar(40) NOT NULL COMMENT '工作经历3',
  `education_experience_1` varchar(40) NOT NULL COMMENT '教育经历1',
  `education_experience_2` varchar(40) NOT NULL COMMENT '教育经历2',
  `education_experience_3` varchar(40) NOT NULL COMMENT '教育经历3',
  `professional_skill` varchar(40) NOT NULL COMMENT '专业技能',
  `face` varchar(40) NOT NULL COMMENT '头像',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

注释：主键能否和需求耦合？
有一些例子是使用uid（用户id）作为主键的，但是这样自做可能导致认定为业务唯一标识的字段不唯一；还会影响b-tree的排序效率。
1.若使用业务相关的唯一标识做主键，小概率可能会因为业务场景的变更，此时的被认定为业务唯一标识的字段不唯一。虽然有点牵强，但是很多都基于这种考虑。
2、对于InnoDB来说，主键是聚簇索引，主键索引位置同时存储索引值和该行记录。
在B+树的数据结构中叶子节点存储数据，如果没定义主键，则会使用非空且唯一的字段，默认为作为主键，如果前面两者都不存在则使用内置6个字节长度的行标ROWID作为主键。在定义主键非自增id，那么计算主键索引值可能每次结果都不会出现连续的，这样在插入B+树叶子节点的时候，就不会在最后一个叶子节点尾部插入。而是中间某个叶子节点插入节点，导致插入数据需要不断对页数据进行重排，效率会明显降低。

用户收藏表

没有在用户信息中增加收藏列的做法，一般都新建一个收藏表提高查询效率。
增加收藏列也可以，使用分割符分割题目编号即可。
同样，在用户信息中也不应该存储太多列，可以分成多个表存储不同的用户信息。

CREATE TABLE `user_col` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) unsigned NOT NULL COMMENT '用户编号',
  `cid` bigint(20) unsigned NOT NULL COMMENT '题目编号',
  `time` int(11) NOT NULL COMMENT '收藏时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='用户收藏表';
1
2
3
4
5
6
7

注释：MySQL字段尽量避免NULL，应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

题库模块

题目信息表

CREATE TABLE `topic_inf` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cid` bigint(20) unsigned NOT NULL,
  `topic` text NOT NULL COMMENT '题目描述',
  `difficulty` tinyint(1) unsigned NOT NULL COMMENT '难度',
  `passing` decimal(10,0) unsigned NOT NULL COMMENT '通过率',
  `tag` char(10) NOT NULL COMMENT '标签',
  PRIMARY KEY (`id`),
  UNIQUE KEY `cid` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='题目信息表';

1
2
3
4
5
6
7
8
9
10
11

题目状态表

CREATE TABLE `topic_inf` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cid` bigint(20) unsigned NOT NULL,
  `topic` text NOT NULL COMMENT '题目描述',
  `difficulty` tinyint(1) unsigned NOT NULL COMMENT '难度',
  `passing` decimal(10,0) unsigned NOT NULL COMMENT '通过率',
  `tag` varchar(255) NOT NULL COMMENT '标签',
  PRIMARY KEY (`id`),
  UNIQUE KEY `cid` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='题目信息表';
1
2
3
4
5
6
7
8
9
10

圈子模块

文章表

在这里插入图片描述

CREATE TABLE `cicle_article` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `article_id` bigint(20) unsigned NOT NULL COMMENT '文章id',
  `adress` varchar(10) NOT NULL COMMENT '地址',
  `time` int(11) unsigned NOT NULL COMMENT '发布时间',
  `author_id` bigint(20) unsigned NOT NULL COMMENT '作者id',
  `page_view` bigint(20) unsigned NOT NULL COMMENT '访问量',
  PRIMARY KEY (`id`),
  UNIQUE KEY `article_id` (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='文章表';
1
2
3
4
5
6
7
8
9
10

评论表

CREATE TABLE `cicle_comment` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) unsigned NOT NULL COMMENT '评论id',
  `article_id` bigint(20) unsigned NOT NULL COMMENT '文章id',
  `time` int(11) unsigned NOT NULL COMMENT '时间',
  `from_id` bigint(20) unsigned NOT NULL COMMENT '评论用户id',
  `to_id` bigint(20) unsigned NOT NULL COMMENT '作者id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `comment_id` (`comment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='评论表';

1
2
3
4
5
6
7
8
9
10
11

回复表

CREATE TABLE `circle_reply` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) unsigned NOT NULL COMMENT '评论id',
  `reply_id` bigint(20) unsigned NOT NULL COMMENT '回复id',
  `content` text NOT NULL COMMENT '回复内容',
  `from_id` bigint(20) unsigned NOT NULL COMMENT '回复用户id',
  `to_id` bigint(20) unsigned NOT NULL COMMENT '评论用户id',
  `time` int(10) unsigned NOT NULL COMMENT '回复时间',
  PRIMARY KEY (`id`)
  UNIQUE KEY `reply_id` (`reply_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='回复表';
1
2
3
4
5
6
7
8
9
10
11

职位模块

最后是我负责的职位模块…emmm我怎么觉得自己选了个最简单的
其实我是乱选的 wwww

看起来只需要两个表就够了

字节跳动职位表

CREATE TABLE `jobs_bytedance` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `job_id` bigint(20) unsigned NOT NULL COMMENT '职位id',
  `job_name` char(20) NOT NULL COMMENT '职位名称',
  `job_category` char(5) NOT NULL,
  `job_type` char(5) NOT NULL,
  `workplace` char(2) NOT NULL,
  `time` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
1
2
3
4
5
6
7
8
9
10

其它职位表

CREATE TABLE `jobs_other` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `job_id` bigint(20) unsigned NOT NULL,
  `enterprise` char(5) NOT NULL COMMENT '企业名',
  `job_name` char(20) NOT NULL COMMENT '工作名称',
  `explain` text NOT NULL COMMENT '说明',
  `
work_time` char(10) DEFAULT NULL COMMENT '工作经验',
  `education_background` char(5) NOT NULL COMMENT '教育背景',
  `workplace` char(10) NOT NULL COMMENT '地点',
  `salary` char(10) NOT NULL COMMENT '工资',
  PRIMARY KEY (`id`),
  UNIQUE KEY `job_id` (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='其它企业工作信息';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15