MySQL Workbench 数据库设计系统实战：5 张表示例与完整设计报告

本文将介绍一个使用 MySQL 和 Workbench 实现的数据库设计系统。该系统包含 5 张表，并包含主键、外键、索引、函数、视图和触发器。下面将详细介绍系统设计和相关内容。

1. 总体设计

系统包含 5 个表，分别为：

用户表（'User'）
商品表（'Product'）
订单表（'Order'）
订单商品表（'OrderProduct'）
评论表（'Comment'）

如下图所示：

其中，订单商品表（'OrderProduct'）为中间表，用于存储订单和商品的关系。

2. 表设计

2.1 用户表（'User'）

用户表用于存储系统中的用户信息，包含以下字段：

ID：主键，自增长。
用户名（'username'）：唯一，不可为空。
密码（'password'）：不可为空。
邮箱（'email'）：唯一，不可为空。
手机号（'phone'）：唯一，不可为空。
注册时间（'register_time'）：日期时间类型，默认值为当前时间。

表结构如下：

CREATE TABLE `User` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `phone` VARCHAR(45) NOT NULL,
  `register_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) VISIBLE,
  UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE,
  UNIQUE INDEX `phone_UNIQUE` (`phone` ASC) VISIBLE
);

2.2 商品表（'Product'）

商品表用于存储系统中的商品信息，包含以下字段：

ID：主键，自增长。
商品名称（'name'）：不可为空。
商品描述（'description'）：可为空。
商品价格（'price'）：数值类型，不可为空。
商品库存（'stock'）：数值类型，不可为空。
发布人（'user_id'）：外键，关联用户表。
发布时间（'publish_time'）：日期时间类型，默认值为当前时间。
最后更新时间（'update_time'）：日期时间类型，默认值为当前时间。

表结构如下：

CREATE TABLE `Product` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(255) NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `stock` INT NOT NULL,
  `user_id` INT NOT NULL,
  `publish_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  INDEX `fk_Product_User_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `fk_Product_User`
    FOREIGN KEY (`user_id`)
    REFERENCES `User` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

2.3 订单表（'Order'）

订单表用于存储系统中的订单信息，包含以下字段：

ID：主键，自增长。
订单号（'order_number'）：唯一，不可为空。
用户ID（'user_id'）：外键，关联用户表。
订单总价（'total_price'）：数值类型，不可为空。
下单时间（'order_time'）：日期时间类型，默认值为当前时间。
订单状态（'status'）：字符串类型，可为空。

表结构如下：

CREATE TABLE `Order` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `order_number` VARCHAR(45) NOT NULL,
  `user_id` INT NOT NULL,
  `total_price` DECIMAL(10,2) NOT NULL,
  `order_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `status` VARCHAR(45) NULL,
  PRIMARY KEY (`ID`),
  UNIQUE INDEX `order_number_UNIQUE` (`order_number` ASC) VISIBLE,
  INDEX `fk_Order_User_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `fk_Order_User`
    FOREIGN KEY (`user_id`)
    REFERENCES `User` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

2.4 订单商品表（'OrderProduct'）

订单商品表用于存储订单和商品的关系，包含以下字段：

ID：主键，自增长。
订单ID（'order_id'）：外键，关联订单表。
商品ID（'product_id'）：外键，关联商品表。
商品数量（'quantity'）：数值类型，不可为空。

表结构如下：

CREATE TABLE `OrderProduct` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `order_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `quantity` INT NOT NULL,
  PRIMARY KEY (`ID`),
  INDEX `fk_OrderProduct_Order_idx` (`order_id` ASC) VISIBLE,
  INDEX `fk_OrderProduct_Product_idx` (`product_id` ASC) VISIBLE,
  CONSTRAINT `fk_OrderProduct_Order`
    FOREIGN KEY (`order_id`)
    REFERENCES `Order` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_OrderProduct_Product`
    FOREIGN KEY (`product_id`)
    REFERENCES `Product` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

2.5 评论表（'Comment'）

评论表用于存储系统中的评论信息，包含以下字段：

ID：主键，自增长。
用户ID（'user_id'）：外键，关联用户表。
商品ID（'product_id'）：外键，关联商品表。
评论内容（'content'）：可为空。
评论时间（'comment_time'）：日期时间类型，默认值为当前时间。

表结构如下：

CREATE TABLE `Comment` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `content` VARCHAR(255) NULL,
  `comment_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  INDEX `fk_Comment_User_idx` (`user_id` ASC) VISIBLE,
  INDEX `fk_Comment_Product_idx` (`product_id` ASC) VISIBLE,
  CONSTRAINT `fk_Comment_User`
    FOREIGN KEY (`user_id`)
    REFERENCES `User` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Comment_Product`
    FOREIGN KEY (`product_id`)
    REFERENCES `Product` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

3. 索引设计

为了提高系统的查询效率，我们在表上创建以下索引：

用户表（'User'）：'username'、'email'、'phone'
商品表（'Product'）：'user_id'
订单表（'Order'）：'order_number'、'user_id'
订单商品表（'OrderProduct'）：'order_id'、'product_id'
评论表（'Comment'）：'user_id'、'product_id'

4. 函数设计

系统中设计了一个函数，用于获取某个用户的订单总数。函数名为 'get_order_count'，参数为用户ID（'user_id'）。函数返回该用户的订单总数。

函数结构如下：

CREATE FUNCTION `get_order_count`(user_id INT) RETURNS int(11)
BEGIN
  DECLARE count INT;
  SELECT COUNT(*) INTO count FROM `Order` WHERE user_id = user_id;
  RETURN count;
END

5. 视图设计

系统中设计了一个视图，用于查看商品的评论总数。视图名为 'ProductCommentCount'，包含以下字段：

商品ID（'product_id'）
商品名称（'product_name'）
评论总数（'comment_count'）

视图结构如下：

CREATE VIEW `ProductCommentCount` AS
SELECT Comment.product_id, Product.name AS product_name, COUNT(*) AS comment_count
FROM Comment
JOIN Product ON Comment.product_id = Product.ID
GROUP BY Comment.product_id;

6. 触发器设计

系统中设计了一个触发器，用于在订单表（'Order'）插入数据时，自动生成订单号。触发器名为 'generate_order_number'。

触发器结构如下：

CREATE TRIGGER `generate_order_number` BEFORE INSERT ON `Order` FOR EACH ROW
BEGIN
  SET NEW.order_number = CONCAT('O', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), FLOOR(RAND() * 900 + 100));
END

7. 总结

通过本文的介绍，我们了解了一个使用 MySQL 和 Workbench 实现的数据库设计系统。该系统包含 5 张表，并包含主键、外键、索引、函数、视图和触发器。在实际应用中，我们可以根据需要进行修改和扩展，以满足不同的业务需求。