83 lines
3.1 KiB
SQL
83 lines
3.1 KiB
SQL
-- myblog 数据库初始化脚本
|
|
-- 7 张表：users, categories, tags, articles, article_tags, comments, visitor_stats
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
email VARCHAR(120) DEFAULT NULL,
|
|
role ENUM('admin','author') NOT NULL DEFAULT 'author',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS categories (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(60) NOT NULL,
|
|
slug VARCHAR(80) NOT NULL UNIQUE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(60) NOT NULL,
|
|
slug VARCHAR(80) NOT NULL UNIQUE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS articles (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT UNSIGNED NOT NULL,
|
|
category_id INT UNSIGNED DEFAULT NULL,
|
|
title VARCHAR(200) NOT NULL,
|
|
slug VARCHAR(220) NOT NULL UNIQUE,
|
|
content MEDIUMTEXT NOT NULL,
|
|
excerpt VARCHAR(500) DEFAULT NULL,
|
|
status ENUM('published','draft') NOT NULL DEFAULT 'draft',
|
|
views INT UNSIGNED NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_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 IF NOT EXISTS article_tags (
|
|
article_id INT UNSIGNED NOT NULL,
|
|
tag_id INT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (article_id, tag_id),
|
|
FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS comments (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
article_id INT UNSIGNED NOT NULL,
|
|
parent_id INT UNSIGNED DEFAULT NULL,
|
|
author VARCHAR(60) NOT NULL,
|
|
email VARCHAR(120) DEFAULT NULL,
|
|
content TEXT NOT NULL,
|
|
status ENUM('pending','approved') NOT NULL DEFAULT 'pending',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS visitor_stats (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
visit_date DATE NOT NULL,
|
|
ip VARCHAR(45) DEFAULT NULL,
|
|
page VARCHAR(200) DEFAULT NULL,
|
|
ua VARCHAR(255) DEFAULT NULL,
|
|
UNIQUE KEY uniq_visit (visit_date, ip, page)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- 默认管理员：admin / 123456 (password_hash 加密)
|
|
INSERT INTO users (username, password_hash, role) VALUES
|
|
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin')
|
|
ON DUPLICATE KEY UPDATE id=id;
|
|
|
|
-- 示例分类
|
|
INSERT INTO categories (name, slug) VALUES
|
|
('设计','design'),('前端','frontend'),('后端','backend'),('随笔','notes')
|
|
ON DUPLICATE KEY UPDATE id=id;
|
|
|
|
-- 示例标签
|
|
INSERT INTO tags (name, slug) VALUES
|
|
('CSS','css'),('PHP','php'),('设计系统','design-system'),('动效','motion')
|
|
ON DUPLICATE KEY UPDATE id=id; |