MySQL Workbench 数据库设计系统实战：5 张表示例

以下是一个使用 MySQL 和 Workbench 实现的数据库设计系统，包含 5 张表、主键、外键、索引、函数、视图和触发器：

用户表

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

文章表

CREATE TABLE articles (
    article_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    body TEXT NOT NULL,
    author_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(user_id) ON DELETE CASCADE
);

评论表

CREATE TABLE comments (
    comment_id INT PRIMARY KEY AUTO_INCREMENT,
    body TEXT NOT NULL,
    author_id INT NOT NULL,
    article_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles(article_id) ON DELETE CASCADE
);

标签表

CREATE TABLE tags (
    tag_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

文章标签表

CREATE TABLE article_tags (
    article_id INT NOT NULL,
    tag_id INT NOT NULL,
    FOREIGN KEY (article_id) REFERENCES articles(article_id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id) ON DELETE CASCADE,
    PRIMARY KEY (article_id, tag_id)
);

-- 创建索引

CREATE INDEX idx_author_id ON articles(author_id);
CREATE INDEX idx_article_id ON comments(article_id);

-- 创建函数

DELIMITER $$
CREATE FUNCTION fn_calculate_age(dob DATE)
RETURNS INT
BEGIN
    DECLARE age INT;
    SELECT FLOOR(DATEDIFF(CURRENT_DATE(), dob)/365.25) INTO age;
    RETURN age;
END$$
DELIMITER ;

-- 创建视图

CREATE VIEW article_comments AS
SELECT articles.title, COUNT(comments.comment_id) AS total_comments
FROM articles
LEFT JOIN comments ON articles.article_id = comments.article_id
GROUP BY articles.article_id;

-- 创建触发器

DELIMITER $$
CREATE TRIGGER tr_user_created_at
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = CURRENT_TIMESTAMP;
END$$
DELIMITER ;