博客系统MySQL,云主机win11,东南亚服务器 vps,vps美国和欧洲,vps日本服务器哪个好

🗣 Wd提问: 博客系统MySQL

🤖 Ai回答:
我来为您详细介绍博客系统的MySQL数据库设计。

1、数据库设计

核心表结构

sql
创建数据库
CREATE DATABASE blog_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE blog_system;

用户表
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE NOT NULL COMMENT '用户名',
email VARCHAR(100) UNIQUE NOT NULL COMMENT '邮箱',
password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
nickname VARCHAR(100) COMMENT '昵称',
avatar VARCHAR(255) COMMENT '头像URL',
bio TEXT COMMENT '个人简介',
role ENUM('admin', 'editor', 'author', 'subscriber') DEFAULT 'author' COMMENT '用户角色',
status ENUM('active', 'inactive', 'banned') DEFAULT 'active' COMMENT '用户状态',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

分类表
CREATE TABLE categories (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) UNIQUE NOT NULL COMMENT '分类名称',
slug VARCHAR(100) UNIQUE NOT NULL COMMENT 'URL友好标识',
description TEXT COMMENT '分类描述',
parent_id INT DEFAULT NULL COMMENT '父分类ID',
sort_order INT DEFAULT 0 COMMENT '排序',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

标签表
CREATE TABLE tags (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) UNIQUE NOT NULL COMMENT '标签名称',
slug VARCHAR(50) UNIQUE NOT NULL COMMENT 'URL友好标识',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

文章表
CREATE TABLE posts (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL COMMENT '文章标题',
slug VARCHAR(255) UNIQUE NOT NULL COMMENT 'URL友好标识',
content LONGTEXT NOT NULL COMMENT '文章内容',
excerpt TEXT COMMENT '文章摘要',
featured_image VARCHAR(255) COMMENT '特色图片',
author_id INT NOT NULL COMMENT '作者ID',
category_id INT COMMENT '分类ID',
status ENUM('draft', 'published', 'private', 'trash') DEFAULT 'draft' COMMENT '文章状态',
view_count INT DEFAULT 0 COMMENT '浏览次数',
comment_status ENUM('open', 'closed') DEFAULT 'open' COMMENT '评论状态',
password VARCHAR(255) COMMENT '文章密码',
published_at TIMESTAMP NULL COMMENT '发布时间',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

文章标签关联表
CREATE TABLE post_tags (
id INT PRIMARY KEY AUTO_INCREMENT,
post_id INT NOT NULL,
tag_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_post_tag (post_id, tag_id),
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

评论表
CREATE TABLE comments (
id INT PRIMARY KEY AUTO_INCREMENT,
post_id INT NOT NULL COMMENT '文章ID',
user_id INT COMMENT '评论者ID（可选，游客评论）',
parent_id INT DEFAULT NULL COMMENT '父评论ID',
author_name VARCHAR(100) COMMENT '评论者姓名',
author_email VARCHAR(100) COMMENT '评论者邮箱',
author_url VARCHAR(255) COMMENT '评论者网站',
content TEXT NOT NULL COMMENT '评论内容',
status ENUM('pending', 'approved', 'spam', 'trash') DEFAULT 'pending' COMMENT '评论状态',
ip_address VARCHAR(45) COMMENT 'IP地址',
user_agent TEXT COMMENT '用户代理',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

媒体文件表
CREATE TABLE media (
id INT PRIMARY KEY AUTO_INCREMENT,
filename VARCHAR(255) NOT NULL COMMENT '文件名',
original_name VARCHAR(255) NOT NULL COMMENT '原始文件名',
file_path VARCHAR(500) NOT NULL COMMENT '文件路径',
file_type VARCHAR(50) NOT NULL COMMENT '文件类型',
file_size INT NOT NULL COMMENT '文件大小（字节）',
mime_type VARCHAR(100) COMMENT 'MIME类型',
alt_text VARCHAR(255) COMMENT '替代文本',
caption TEXT COMMENT '说明文字',
user_id INT NOT NULL COMMENT '上传者ID',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

页面表
CREATE TABLE pages (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL COMMENT '页面标题',
slug VARCHAR(255) UNIQUE NOT NULL COMMENT 'URL友好标识',
content LONGTEXT NOT NULL COMMENT '页面内容',
parent_id INT DEFAULT NULL COMMENT '父页面ID',
template VARCHAR(100) COMMENT '页面模板',
status ENUM('draft', 'published', 'private') DEFAULT 'draft' COMMENT '页面状态',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES pages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2、索引优化

sql
为常用查询字段添加索引
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_published_at ON posts(published_at);
CREATE INDEX idx_posts_author_id ON posts(author_id);
CREATE INDEX idx_posts_category_id ON posts(category_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_status ON comments(status);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_categories_parent_id ON categories(parent_id);

3、常用查询语句

文章查询
sql
获取已发布的文章列表（分页）
SELECT p.id, p.title, p.slug, p.excerpt, p.featured_image,
p.view_count, p.published_at, u.username, u.nickname,
c.name as category_name
FROM posts p
LEFT JOIN users u ON p.author_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 'published'
ORDER BY p.published_at DESC
LIMIT 10 OFFSET 0;

获取热门文章（按浏览量）
SELECT p.id, p.title, p.slug, p.view_count, p.published_at
FROM posts p
WHERE p.status = 'published'
ORDER BY p.view_count DESC
LIMIT 10;

根据分类获取文章
SELECT p.id, p.title, p.slug, p.excerpt, p.published_at
FROM posts p
WHERE p.category_id = 1 AND p.status = 'published'
ORDER BY p.published_at DESC;

根据标签获取文章
SELECT DISTINCT p.id, p.title, p.slug, p.excerpt, p.published_at
FROM posts p
JOIN post_tags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE t.name = '技术' AND p.status = 'published'
ORDER BY p.published_at DESC;

评论查询
sql
获取文章的评论（分页）
SELECT c.id, c.content, c.author_name, c.author_email, c.author_url,
c.created_at, c.parent_id
FROM comments c
WHERE c.post_id = 1 AND c.status = 'approved'
ORDER BY c.created_at ASC
LIMIT 20 OFFSET 0;

获取评论回复
SELECT c.id, c.content, c.author_name, c.created_at
FROM comments c
WHERE c.parent_id = 1 AND c.status = 'approved'
ORDER BY c.created_at ASC;

用户相关查询
sql
获取用户信息及文章数量
SELECT u.id, u.username, u.nickname, u.avatar, u.bio,
COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.author_id AND p.status = 'published'
WHERE u.status = 'active'
GROUP BY u.id;

获取作者的文章列表
SELECT p.id, p.title, p.slug, p.status, p.view_count, p.published_at
FROM posts p
WHERE p.author_id = 1
ORDER BY p.created_at DESC;

4、数据操作示例

插入数据
sql
插入用户
INSERT INTO users (username, email, password_hash, nickname, role)
VALUES ('admin', 'admin@65.hk', '$2y$10$

Ai作答

📣 商家广告：