-- 用户表
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
email VARCHAR(100) UNIQUE,
nickname VARCHAR(50),
avatar VARCHAR(255),
status TINYINT DEFAULT 1 COMMENT '1:正常 0:禁用',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 角色表
CREATE TABLE roles (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
role_name VARCHAR(50) UNIQUE NOT NULL,
role_code VARCHAR(50) UNIQUE NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 用户角色关联表
CREATE TABLE user_roles (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
role_id BIGINT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (role_id) REFERENCES roles(id),
UNIQUE KEY uk_user_role (user_id, role_id)
);
-- 知识分类/目录表
CREATE TABLE categories (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
parent_id BIGINT DEFAULT 0 COMMENT '父分类ID,0为顶级分类',
sort_order INT DEFAULT 0,
description TEXT,
icon VARCHAR(100),
status TINYINT DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 知识文章表
CREATE TABLE articles (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
content LONGTEXT,
summary TEXT,
category_id BIGINT,
author_id BIGINT NOT NULL,
view_count INT DEFAULT 0,
like_count INT DEFAULT 0,
status TINYINT DEFAULT 1 COMMENT '1:发布 0:草稿 -1:删除',
is_top TINYINT DEFAULT 0 COMMENT '是否置顶',
tags VARCHAR(500),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(id),
FOREIGN KEY (author_id) REFERENCES users(id)
);
-- 附件表
CREATE TABLE attachments (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
article_id BIGINT,
file_name VARCHAR(255) NOT NULL,
file_path VARCHAR(500) NOT NULL,
file_size BIGINT,
file_type VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (article_id) REFERENCES articles(id)
);