DROP TABLE IF EXISTS book;
|
CREATE TABLE book
|
(
|
id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
title varchar(50) NOT NULL,
|
author varchar(30) DEFAULT NULL,
|
summary varchar(1000) DEFAULT NULL,
|
image varchar(100) DEFAULT NULL,
|
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
delete_time datetime(3) DEFAULT NULL,
|
is_deleted tinyint(1) DEFAULT 0,
|
PRIMARY KEY (id)
|
) ENGINE = InnoDB
|
DEFAULT CHARSET = utf8mb4;
|
|
DROP TABLE IF EXISTS lin_file;
|
CREATE TABLE lin_file
|
(
|
id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
path varchar(500) NOT NULL,
|
type varchar(10) NOT NULL DEFAULT 'LOCAL' COMMENT 'LOCAL 本地，REMOTE 远程',
|
name varchar(100) NOT NULL,
|
extension varchar(50) DEFAULT NULL,
|
size int(11) DEFAULT NULL,
|
md5 varchar(40) DEFAULT NULL COMMENT 'md5值，防止上传重复文件',
|
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
delete_time datetime(3) DEFAULT NULL,
|
is_deleted tinyint(1) DEFAULT 0,
|
PRIMARY KEY (id),
|
UNIQUE KEY md5_del (md5, delete_time)
|
) ENGINE = InnoDB
|
DEFAULT CHARSET = utf8mb4;
|
|
DROP TABLE IF EXISTS lin_log;
|
CREATE TABLE lin_log
|
(
|
id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
message varchar(450) DEFAULT NULL,
|
user_id int(10) unsigned NOT NULL,
|
username varchar(24) DEFAULT NULL,
|
status_code int(11) DEFAULT NULL,
|
method varchar(20) DEFAULT NULL,
|
path varchar(50) DEFAULT NULL,
|
permission varchar(100) DEFAULT NULL,
|
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
delete_time datetime(3) DEFAULT NULL,
|
is_deleted tinyint(1) DEFAULT 0,
|
PRIMARY KEY (id)
|
) ENGINE = InnoDB
|
DEFAULT CHARSET = utf8mb4;
|
|
-- ----------------------------
|
-- 权限表
|
-- ----------------------------
|
DROP TABLE IF EXISTS lin_permission;
|
CREATE TABLE lin_permission
|
(
|
id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
name varchar(60) NOT NULL COMMENT '权限名称，例如：访问首页',
|
module varchar(50) NOT NULL COMMENT '权限所属模块，例如：人员管理',
|
mount tinyint(1) NOT NULL DEFAULT 1 COMMENT '0：关闭 1：开启',
|
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
delete_time datetime(3) DEFAULT NULL,
|
is_deleted tinyint(1) DEFAULT 0,
|
PRIMARY KEY (id)
|
) ENGINE = InnoDB
|
DEFAULT CHARSET = utf8mb4;
|
|
-- ----------------------------
|
-- 分组表
|
-- ----------------------------
|
DROP TABLE IF EXISTS lin_group;
|
CREATE TABLE lin_group
|
(
|
id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
name varchar(60) NOT NULL COMMENT '分组名称，例如：搬砖者',
|
info varchar(255) DEFAULT NULL COMMENT '分组信息：例如：搬砖的人',
|
level tinyint(2) NOT NULL DEFAULT 3 COMMENT '分组级别 1：root 2：guest 3：user root（root、guest分组只能存在一个)',
|
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
delete_time datetime(3) DEFAULT NULL,
|
is_deleted tinyint(1) DEFAULT 0,
|
PRIMARY KEY (id),
|
UNIQUE KEY name_del (name, delete_time)
|
) ENGINE = InnoDB
|
DEFAULT CHARSET = utf8mb4;
|
|
-- ----------------------------
|
-- 分组-权限表
|
-- ----------------------------
|
DROP TABLE IF EXISTS lin_group_permission;
|
CREATE TABLE lin_group_permission
|
(
|
id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
group_id int(10) unsigned NOT NULL COMMENT '分组id',
|
permission_id int(10) unsigned NOT NULL COMMENT '权限id',
|
PRIMARY KEY (id)
|
) ENGINE = InnoDB
|
DEFAULT CHARSET = utf8mb4;
|
|
-- ----------------------------
|
-- 用户基本信息表
|
-- ----------------------------
|
DROP TABLE IF EXISTS lin_user;
|
CREATE TABLE lin_user
|
(
|
id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
username varchar(24) NOT NULL COMMENT '用户名，唯一',
|
nickname varchar(24) DEFAULT NULL COMMENT '用户昵称',
|
avatar varchar(500) DEFAULT NULL COMMENT '头像url',
|
email varchar(100) DEFAULT NULL COMMENT '邮箱',
|
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
delete_time datetime(3) DEFAULT NULL,
|
is_deleted tinyint(1) DEFAULT 0,
|
PRIMARY KEY (id),
|
UNIQUE KEY username_del (username, delete_time),
|
UNIQUE KEY email_del (email, delete_time)
|
) ENGINE = InnoDB
|
DEFAULT CHARSET = utf8mb4;
|
|
DROP TABLE IF EXISTS lin_user_identity;
|
CREATE TABLE lin_user_identity
|
(
|
id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
user_id int(10) unsigned NOT NULL COMMENT '用户id',
|
identity_type varchar(100) NOT NULL,
|
identifier varchar(100),
|
credential varchar(100),
|
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
delete_time datetime(3) DEFAULT NULL,
|
is_deleted tinyint(1) DEFAULT 0,
|
PRIMARY KEY (id)
|
) ENGINE = InnoDB
|
DEFAULT CHARSET = utf8mb4;
|
|
-- ----------------------------
|
-- 用户-分组表
|
-- ----------------------------
|
DROP TABLE IF EXISTS lin_user_group;
|
CREATE TABLE lin_user_group
|
(
|
id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
user_id int(10) unsigned NOT NULL COMMENT '用户id',
|
group_id int(10) unsigned NOT NULL COMMENT '分组id',
|
PRIMARY KEY (id)
|
) ENGINE = InnoDB
|
DEFAULT CHARSET = utf8mb4;
|
