以此数据库为准：-- 用户表
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
password VARCHAR(100) NOT NULL COMMENT '密码',
email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
nickname VARCHAR(50) COMMENT '昵称',
avatar VARCHAR(200) COMMENT '头像URL',
role ENUM('USER', 'MODERATOR', 'ADMIN') DEFAULT 'USER' COMMENT '用户角色',
reputation INT DEFAULT 0 COMMENT '声望值',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE COMMENT '是否激活'
);
-- 分类表
CREATE TABLE categories (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '分类ID',
name VARCHAR(50) NOT NULL UNIQUE COMMENT '分类名称',
description TEXT COMMENT '分类描述',
parent_id BIGINT COMMENT '父分类ID，用于层级结构',
sort_order INT DEFAULT 0 COMMENT '排序顺序',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES categories(id)
);
-- 标签表
CREATE TABLE tags (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '标签ID',
name VARCHAR(50) NOT NULL UNIQUE COMMENT '标签名称',
color VARCHAR(20) DEFAULT '#666666' COMMENT '标签颜色',
usage_count INT DEFAULT 0 COMMENT '使用次数',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 问题表
CREATE TABLE questions (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '问题ID',
title VARCHAR(200) NOT NULL COMMENT '问题标题',
content TEXT NOT NULL COMMENT '问题内容',
user_id BIGINT NOT NULL COMMENT '提问者ID',
category_id BIGINT NOT NULL COMMENT '分类ID',
view_count INT DEFAULT 0 COMMENT '浏览次数',
answer_count INT DEFAULT 0 COMMENT '回答数量',
vote_count INT DEFAULT 0 COMMENT '投票数量',
status ENUM('OPEN', 'CLOSED', 'RESOLVED') DEFAULT 'OPEN' COMMENT '问题状态',
is_sticky BOOLEAN DEFAULT FALSE COMMENT '是否置顶',
is_locked BOOLEAN DEFAULT FALSE COMMENT '是否锁定',
last_activity_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (category_id) REFERENCES categories(id)
);
-- 问题标签关联表
CREATE TABLE question_tags (
question_id BIGINT,
tag_id BIGINT,
PRIMARY KEY (question_id, tag_id),
FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
-- 回答表
CREATE TABLE answers (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '回答ID',
content TEXT NOT NULL COMMENT '回答内容',
user_id BIGINT NOT NULL COMMENT '回答者ID',
question_id BIGINT NOT NULL COMMENT '问题ID',
vote_count INT DEFAULT 0 COMMENT '投票数量',
is_accepted BOOLEAN DEFAULT FALSE COMMENT '是否被采纳',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
);
-- 投票表
CREATE TABLE votes (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '投票ID',
user_id BIGINT NOT NULL COMMENT '投票者ID',
target_type ENUM('QUESTION', 'ANSWER') NOT NULL COMMENT '目标类型',
target_id BIGINT NOT NULL COMMENT '目标ID',
vote_type ENUM('UP', 'DOWN') NOT NULL COMMENT '投票类型',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_vote (user_id, target_type, target_id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
-- 评论表
CREATE TABLE comments (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '评论ID',
content TEXT NOT NULL COMMENT '评论内容',
user_id BIGINT NOT NULL COMMENT '评论者ID',
target_type ENUM('QUESTION', 'ANSWER') NOT NULL COMMENT '目标类型',
target_id BIGINT NOT NULL COMMENT '目标ID',
parent_id BIGINT COMMENT '父评论ID，用于嵌套评论',
vote_count INT DEFAULT 0 COMMENT '投票数量',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (parent_id) REFERENCES comments(id)
);
-- 消息通知表
CREATE TABLE notifications (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '通知ID',
user_id BIGINT NOT NULL COMMENT '接收者ID',
type ENUM('REPLY', 'VOTE', 'ACCEPT', 'MENTION') NOT NULL COMMENT '通知类型',
content TEXT NOT NULL COMMENT '通知内容',
target_type ENUM('QUESTION', 'ANSWER', 'COMMENT') NOT NULL COMMENT '目标类型',
target_id BIGINT NOT NULL COMMENT '目标ID',
is_read BOOLEAN DEFAULT FALSE COMMENT '是否已读',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
最新发布
本文解决了一个在Struts2框架中使用<s:submit>标签时出现的ognl.OgnlException错误。通过修改标签属性，成功避免了目标为空时设置属性的问题。
扫一扫
6928
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
