好的，以下是一个可能的MySQL数据库设计代码：
```sql
-- 用户表
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL UNIQUE,
password VARCHAR(128) NOT NULL,
email VARCHAR(254) NOT NULL UNIQUE,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 博客表
CREATE TABLE blogs (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
image_url VARCHAR(255) DEFAULT NULL,
category_id INT UNSIGNED DEFAULT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL ON UPDATE CASCADE
);
-- 分类表
CREATE TABLE categories (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 标签表
CREATE TABLE tags (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 博客-标签关联表
CREATE TABLE blog_tags (
blog_id INT UNSIGNED NOT NULL,
tag_id INT UNSIGNED NOT NULL,
PRIMARY KEY (blog_id, tag_id),
FOREIGN KEY (blog_id) REFERENCES blogs(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- 评论表
CREATE TABLE comments (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
blog_id INT UNSIGNED NOT NULL,
content TEXT NOT NULL,
parent_id INT UNSIGNED DEFAULT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (blog_id) REFERENCES blogs(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- 友情链接表
CREATE TABLE links (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
url VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 用户-友情链接关联表
CREATE TABLE user_links (
user_id INT UNSIGNED NOT NULL,
link_id INT UNSIGNED NOT NULL,
PRIMARY KEY (user_id, link_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (link_id) REFERENCES links(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- 订阅表
CREATE TABLE subscriptions (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
blog_id INT UNSIGNED NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (blog_id) REFERENCES blogs(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- 站内信表
CREATE TABLE messages (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
sender_id INT UNSIGNED NOT NULL,
receiver_id INT UNSIGNED NOT NULL,
content TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);
```
该代码定义了8个表：
1. `users`：存储用户信息，包括用户名、密码、邮箱等。
2. `blogs`：存储博客信息，包括博客标题、内容、分类、标签等，同时与用户表和分类表建立了外键关联。
3. `categories`：存储博客分类信息。
4. `tags`：存储博客标签信息。
5. `blog_tags`：存储博客和标签之间的关联关系。
6. `comments`：存储评论信息，包括评论内容、评论对象等，同时与用户表和博客表建立了外键关联。
7. `links`：存储友情链接信息。
8. `subscriptions`：存储用户订阅博客的信息。
同时，还定义了一个`messages`表用于存储站内信信息。
注意，该数据库设计代码仅供参考，具体的数据库设计需要根据具体的业务需求进行调整。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
