实战项目：博客系统

嗨！我是长安。让我们用PostgreSQL做一个完整的博客系统！

🎯 系统需求

我们要实现一个功能完善的博客系统：

  • 👤 用户注册、登录
  • ✍️ 发布、编辑、删除文章
  • 💬 评论功能
  • 👍 点赞功能
  • 🏷️ 标签系统
  • 📊 统计功能

📝 数据库设计

第一步：创建数据库

CREATE DATABASE blog_system;
\c blog_system

第二步：创建用户表

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(255),
    bio TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    is_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMPTZ
);

-- 索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_created ON users(created_at);

第三步：创建文章表

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE,  -- URL友好的标识
    content TEXT NOT NULL,
    excerpt VARCHAR(500),  -- 摘要
    cover_image VARCHAR(255),
    status VARCHAR(20) DEFAULT 'draft',  -- draft, published, archived
    views BIGINT DEFAULT 0,
    likes BIGINT DEFAULT 0,
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 索引
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_published ON posts(published_at DESC);
CREATE INDEX idx_posts_slug ON posts(slug);

第四步：创建评论表

CREATE TABLE comments (
    id BIGSERIAL PRIMARY KEY,
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    parent_id BIGINT REFERENCES comments(id) ON DELETE CASCADE,  -- 支持回复
    content TEXT NOT NULL,
    is_deleted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 索引
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_user_id ON comments(user_id);
CREATE INDEX idx_comments_parent_id ON comments(parent_id);

第五步：创建标签系统

-- 标签表
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 文章标签关联表
CREATE TABLE post_tags (
    post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INT REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

-- 索引
CREATE INDEX idx_post_tags_post_id ON post_tags(post_id);
CREATE INDEX idx_post_tags_tag_id ON post_tags(tag_id);

第六步：创建点赞表

CREATE TABLE likes (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, post_id)  -- 一个用户只能点赞一次
);

-- 索引
CREATE INDEX idx_likes_user_id ON likes(user_id);
CREATE INDEX idx_likes_post_id ON likes(post_id);

🔧 创建辅助功能

自动更新updated_at

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 为各表添加触发器
CREATE TRIGGER trigger_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trigger_posts_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trigger_comments_updated_at
BEFORE UPDATE ON comments
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

自动生成slug

CREATE OR REPLACE FUNCTION generate_slug()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.slug IS NULL OR NEW.slug = '' THEN
        NEW.slug = LOWER(REGEXP_REPLACE(NEW.title, '[^a-zA-Z0-9]+', '-', 'g'));
        NEW.slug = TRIM(BOTH '-' FROM NEW.slug);
        -- 确保唯一性
        IF EXISTS (SELECT 1 FROM posts WHERE slug = NEW.slug AND id != NEW.id) THEN
            NEW.slug = NEW.slug || '-' || NEW.id;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_generate_slug
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION generate_slug();

更新点赞数

CREATE OR REPLACE FUNCTION update_post_likes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE posts SET likes = likes + 1 WHERE id = NEW.post_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE posts SET likes = likes - 1 WHERE id = OLD.post_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_likes
AFTER INSERT OR DELETE ON likes
FOR EACH ROW EXECUTE FUNCTION update_post_likes();

📊 常用查询

1. 获取文章列表（带作者和标签）

SELECT 
    p.id,
    p.title,
    p.slug,
    p.excerpt,
    p.cover_image,
    p.views,
    p.likes,
    p.published_at,
    u.username AS author,
    u.avatar_url AS author_avatar,
    ARRAY_AGG(t.name) AS tags
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id
WHERE p.status = 'published'
GROUP BY p.id, u.username, u.avatar_url
ORDER BY p.published_at DESC
LIMIT 10;

2. 获取文章详情（带评论）

-- 文章信息
SELECT 
    p.*,
    u.username AS author,
    u.avatar_url AS author_avatar,
    u.bio AS author_bio,
    ARRAY_AGG(DISTINCT t.name) AS tags,
    COUNT(DISTINCT c.id) AS comment_count
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id
LEFT JOIN comments c ON p.id = c.post_id AND c.is_deleted = FALSE
WHERE p.slug = 'my-first-post'
GROUP BY p.id, u.username, u.avatar_url, u.bio;

-- 评论列表
SELECT 
    c.id,
    c.content,
    c.created_at,
    u.username,
    u.avatar_url,
    c.parent_id
FROM comments c
JOIN users u ON c.user_id = u.id
WHERE c.post_id = 1 
  AND c.is_deleted = FALSE
ORDER BY c.created_at ASC;

3. 热门文章

SELECT 
    p.title,
    p.slug,
    p.views,
    p.likes,
    u.username AS author,
    COUNT(c.id) AS comment_count
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.status = 'published'
  AND p.published_at > NOW() - INTERVAL '30 days'
GROUP BY p.id, u.username
ORDER BY p.views DESC, p.likes DESC
LIMIT 10;

4. 标签统计

SELECT 
    t.name,
    t.slug,
    COUNT(pt.post_id) AS post_count
FROM tags t
LEFT JOIN post_tags pt ON t.id = pt.tag_id
LEFT JOIN posts p ON pt.post_id = p.id AND p.status = 'published'
GROUP BY t.id, t.name, t.slug
HAVING COUNT(pt.post_id) > 0
ORDER BY post_count DESC;

5. 用户统计

SELECT 
    u.id,
    u.username,
    u.avatar_url,
    COUNT(DISTINCT p.id) AS post_count,
    COUNT(DISTINCT c.id) AS comment_count,
    SUM(p.views) AS total_views,
    SUM(p.likes) AS total_likes
FROM users u
LEFT JOIN posts p ON u.id = p.user_id AND p.status = 'published'
LEFT JOIN comments c ON u.id = c.user_id
GROUP BY u.id, u.username, u.avatar_url
ORDER BY post_count DESC
LIMIT 10;

🎮 实战操作

插入测试数据

-- 插入用户
INSERT INTO users (username, email, password_hash, bio) VALUES 
    ('长安', 'changan@email.com', '$2b$10$...', '编程指南创始人'),
    ('小明', 'xiaoming@email.com', '$2b$10$...', '热爱编程'),
    ('小红', 'xiaohong@email.com', '$2b$10$...', '全栈开发');

-- 插入标签
INSERT INTO tags (name, slug) VALUES 
    ('PostgreSQL', 'postgresql'),
    ('数据库', 'database'),
    ('教程', 'tutorial'),
    ('进阶', 'advanced');

-- 插入文章
INSERT INTO posts (user_id, title, content, excerpt, status, published_at) VALUES 
    (1, 'PostgreSQL入门教程', '完整内容...', '这是一篇PostgreSQL入门教程', 'published', NOW()),
    (1, 'SQL高级技巧', '完整内容...', '分享一些高级SQL技巧', 'published', NOW() - INTERVAL '1 day');

-- 关联标签
INSERT INTO post_tags (post_id, tag_id) VALUES 
    (1, 1), (1, 2), (1, 3),
    (2, 1), (2, 4);

-- 插入评论
INSERT INTO comments (post_id, user_id, content) VALUES 
    (1, 2, '写得很好！'),
    (1, 3, '学到了很多！');

-- 点赞
INSERT INTO likes (user_id, post_id) VALUES 
    (2, 1),
    (3, 1),
    (2, 2);

更新浏览量

-- 增加浏览量
UPDATE posts SET views = views + 1 WHERE id = 1;

发布文章

UPDATE posts 
SET status = 'published', 
    published_at = NOW() 
WHERE id = 3;

软删除评论

UPDATE comments 
SET is_deleted = TRUE 
WHERE id = 5;

📊 性能优化

创建视图简化查询

-- 文章列表视图
CREATE VIEW v_post_list AS
SELECT 
    p.id,
    p.title,
    p.slug,
    p.excerpt,
    p.views,
    p.likes,
    p.published_at,
    u.username AS author,
    u.avatar_url AS author_avatar,
    (SELECT COUNT(*) FROM comments WHERE post_id = p.id AND is_deleted = FALSE) AS comment_count
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.status = 'published';

-- 使用视图
SELECT * FROM v_post_list ORDER BY published_at DESC LIMIT 10;

使用物化视图缓存统计

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_user_stats AS
SELECT 
    u.id,
    u.username,
    COUNT(DISTINCT p.id) AS post_count,
    COALESCE(SUM(p.views), 0) AS total_views,
    COALESCE(SUM(p.likes), 0) AS total_likes
FROM users u
LEFT JOIN posts p ON u.id = p.user_id AND p.status = 'published'
GROUP BY u.id, u.username;

-- 刷新物化视图（定期执行）
REFRESH MATERIALIZED VIEW mv_user_stats;

📚 下一步

博客系统设计完成！接下来学习用户管理系统！

长安的总结：

这个博客系统涵盖了：

  • ✅ 完整的表设计
  • ✅ 索引优化
  • ✅ 触发器自动化
  • ✅ 复杂查询
  • ✅ 性能优化

把这个项目搞懂，你就能独立设计数据库了！💪

有问题来编程指南！

最近更新: