数据库设计与建表

数据库设计是系统开发的基础。合理的数据库设计能够提高系统性能，降低维护成本。本节将根据业务需求设计数据库表结构。

本节将学习：MySQL 数据库设计、表结构设计、索引设计，以及外键关系。

数据库设计原则

设计原则

数据库设计原则：

1. 规范化设计：遵循第三范式，减少数据冗余
2. 性能优化：合理使用索引，优化查询性能
3. 扩展性：考虑未来扩展需求
4. 数据完整性：使用外键约束保证数据一致性

数据库选择

选择 MySQL 的原因：

• 成熟稳定，广泛使用
• 支持 ACID 事务
• 性能优秀
• 生态完善

表结构设计

用户表（user）

CREATE TABLE `user` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'User ID',
    `username` VARCHAR(50) NOT NULL COMMENT 'Username',
    `email` VARCHAR(100) NOT NULL COMMENT 'Email',
    `phone` VARCHAR(20) DEFAULT NULL COMMENT 'Phone Number',
    `password` VARCHAR(255) NOT NULL COMMENT 'Encrypted Password',
    `nickname` VARCHAR(50) DEFAULT NULL COMMENT 'Nickname',
    `avatar` VARCHAR(255) DEFAULT NULL COMMENT 'Avatar URL',
    `status` TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 0-disabled, 1-enabled',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create Time',
    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`),
    UNIQUE KEY `uk_email` (`email`),
    UNIQUE KEY `uk_phone` (`phone`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User Table';

商品表（product）

CREATE TABLE `product` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Product ID',
    `name` VARCHAR(200) NOT NULL COMMENT 'Product Name',
    `description` TEXT COMMENT 'Product Description',
    `price` DECIMAL(10, 2) NOT NULL COMMENT 'Product Price',
    `stock` INT NOT NULL DEFAULT 0 COMMENT 'Stock Quantity',
    `category_id` BIGINT NOT NULL COMMENT 'Category ID',
    `image_url` VARCHAR(255) DEFAULT NULL COMMENT 'Product Image URL',
    `status` TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 0-off-shelf, 1-on-shelf',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create Time',
    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
    PRIMARY KEY (`id`),
    KEY `idx_category_id` (`category_id`),
    KEY `idx_status` (`status`),
    KEY `idx_price` (`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Product Table';

商品分类表（category）

CREATE TABLE `category` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Category ID',
    `name` VARCHAR(100) NOT NULL COMMENT 'Category Name',
    `parent_id` BIGINT DEFAULT 0 COMMENT 'Parent Category ID',
    `level` TINYINT NOT NULL DEFAULT 1 COMMENT 'Category Level: 1-first, 2-second',
    `sort_order` INT DEFAULT 0 COMMENT 'Sort Order',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create Time',
    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
    PRIMARY KEY (`id`),
    KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Category Table';

订单表（order）

CREATE TABLE `order` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Order ID',
    `order_no` VARCHAR(50) NOT NULL COMMENT 'Order Number',
    `user_id` BIGINT NOT NULL COMMENT 'User ID',
    `total_amount` DECIMAL(10, 2) NOT NULL COMMENT 'Total Amount',
    `status` TINYINT NOT NULL DEFAULT 0 COMMENT 'Status: 0-pending, 1-paid, 2-shipped, 3-completed, 4-cancelled',
    `shipping_address` VARCHAR(500) DEFAULT NULL COMMENT 'Shipping Address',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create Time',
    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_order_no` (`order_no`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_status` (`status`),
    KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Order Table';

订单项表（order_item）

CREATE TABLE `order_item` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Order Item ID',
    `order_id` BIGINT NOT NULL COMMENT 'Order ID',
    `product_id` BIGINT NOT NULL COMMENT 'Product ID',
    `quantity` INT NOT NULL COMMENT 'Quantity',
    `price` DECIMAL(10, 2) NOT NULL COMMENT 'Unit Price',
    `subtotal` DECIMAL(10, 2) NOT NULL COMMENT 'Subtotal',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create Time',
    PRIMARY KEY (`id`),
    KEY `idx_order_id` (`order_id`),
    KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Order Item Table';

支付表（payment）

CREATE TABLE `payment` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Payment ID',
    `order_id` BIGINT NOT NULL COMMENT 'Order ID',
    `payment_no` VARCHAR(50) NOT NULL COMMENT 'Payment Number',
    `amount` DECIMAL(10, 2) NOT NULL COMMENT 'Payment Amount',
    `payment_method` VARCHAR(20) NOT NULL COMMENT 'Payment Method: ALIPAY, WECHAT, CREDIT_CARD',
    `status` TINYINT NOT NULL DEFAULT 0 COMMENT 'Status: 0-pending, 1-success, 2-failed',
    `pay_time` DATETIME DEFAULT NULL COMMENT 'Pay Time',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create Time',
    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_payment_no` (`payment_no`),
    KEY `idx_order_id` (`order_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Payment Table';

表关系图

索引设计

索引设计原则

索引设计原则：

1. 主键索引：每个表必须有主键
2. 唯一索引：保证唯一性的字段
3. 普通索引：经常用于查询条件的字段
4. 组合索引：多字段联合查询

索引类型

外键关系

外键设计

外键关系：

1. user → order：一个用户可以有多个订单
2. order → order_item：一个订单可以有多个订单项
3. product → order_item：一个商品可以出现在多个订单项中
4. category → product：一个分类可以包含多个商品
5. order → payment：一个订单对应一个支付记录

外键约束

-- 订单表外键
ALTER TABLE `order` 
ADD CONSTRAINT `fk_order_user` 
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`);

-- 订单项表外键
ALTER TABLE `order_item` 
ADD CONSTRAINT `fk_order_item_order` 
FOREIGN KEY (`order_id`) REFERENCES `order` (`id`);

ALTER TABLE `order_item` 
ADD CONSTRAINT `fk_order_item_product` 
FOREIGN KEY (`product_id`) REFERENCES `product` (`id`);

-- 商品表外键
ALTER TABLE `product` 
ADD CONSTRAINT `fk_product_category` 
FOREIGN KEY (`category_id`) REFERENCES `category` (`id`);

-- 支付表外键
ALTER TABLE `payment` 
ADD CONSTRAINT `fk_payment_order` 
FOREIGN KEY (`order_id`) REFERENCES `order` (`id`);

数据库初始化脚本

完整建表脚本

-- 创建数据库
CREATE DATABASE IF NOT EXISTS `ecommerce` 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE `ecommerce`;

-- 用户表
CREATE TABLE `user` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'User ID',
    `username` VARCHAR(50) NOT NULL COMMENT 'Username',
    `email` VARCHAR(100) NOT NULL COMMENT 'Email',
    `phone` VARCHAR(20) DEFAULT NULL COMMENT 'Phone Number',
    `password` VARCHAR(255) NOT NULL COMMENT 'Encrypted Password',
    `nickname` VARCHAR(50) DEFAULT NULL COMMENT 'Nickname',
    `avatar` VARCHAR(255) DEFAULT NULL COMMENT 'Avatar URL',
    `status` TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 0-disabled, 1-enabled',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create Time',
    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`),
    UNIQUE KEY `uk_email` (`email`),
    UNIQUE KEY `uk_phone` (`phone`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User Table';

-- 商品分类表
CREATE TABLE `category` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Category ID',
    `name` VARCHAR(100) NOT NULL COMMENT 'Category Name',
    `parent_id` BIGINT DEFAULT 0 COMMENT 'Parent Category ID',
    `level` TINYINT NOT NULL DEFAULT 1 COMMENT 'Category Level: 1-first, 2-second',
    `sort_order` INT DEFAULT 0 COMMENT 'Sort Order',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create Time',
    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
    PRIMARY KEY (`id`),
    KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Category Table';

-- 商品表
CREATE TABLE `product` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Product ID',
    `name` VARCHAR(200) NOT NULL COMMENT 'Product Name',
    `description` TEXT COMMENT 'Product Description',
    `price` DECIMAL(10, 2) NOT NULL COMMENT 'Product Price',
    `stock` INT NOT NULL DEFAULT 0 COMMENT 'Stock Quantity',
    `category_id` BIGINT NOT NULL COMMENT 'Category ID',
    `image_url` VARCHAR(255) DEFAULT NULL COMMENT 'Product Image URL',
    `status` TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 0-off-shelf, 1-on-shelf',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create Time',
    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
    PRIMARY KEY (`id`),
    KEY `idx_category_id` (`category_id`),
    KEY `idx_status` (`status`),
    KEY `idx_price` (`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Product Table';

-- 订单表
CREATE TABLE `order` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Order ID',
    `order_no` VARCHAR(50) NOT NULL COMMENT 'Order Number',
    `user_id` BIGINT NOT NULL COMMENT 'User ID',
    `total_amount` DECIMAL(10, 2) NOT NULL COMMENT 'Total Amount',
    `status` TINYINT NOT NULL DEFAULT 0 COMMENT 'Status: 0-pending, 1-paid, 2-shipped, 3-completed, 4-cancelled',
    `shipping_address` VARCHAR(500) DEFAULT NULL COMMENT 'Shipping Address',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create Time',
    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_order_no` (`order_no`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_status` (`status`),
    KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Order Table';

-- 订单项表
CREATE TABLE `order_item` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Order Item ID',
    `order_id` BIGINT NOT NULL COMMENT 'Order ID',
    `product_id` BIGINT NOT NULL COMMENT 'Product ID',
    `quantity` INT NOT NULL COMMENT 'Quantity',
    `price` DECIMAL(10, 2) NOT NULL COMMENT 'Unit Price',
    `subtotal` DECIMAL(10, 2) NOT NULL COMMENT 'Subtotal',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create Time',
    PRIMARY KEY (`id`),
    KEY `idx_order_id` (`order_id`),
    KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Order Item Table';

-- 支付表
CREATE TABLE `payment` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Payment ID',
    `order_id` BIGINT NOT NULL COMMENT 'Order ID',
    `payment_no` VARCHAR(50) NOT NULL COMMENT 'Payment Number',
    `amount` DECIMAL(10, 2) NOT NULL COMMENT 'Payment Amount',
    `payment_method` VARCHAR(20) NOT NULL COMMENT 'Payment Method: ALIPAY, WECHAT, CREDIT_CARD',
    `status` TINYINT NOT NULL DEFAULT 0 COMMENT 'Status: 0-pending, 1-success, 2-failed',
    `pay_time` DATETIME DEFAULT NULL COMMENT 'Pay Time',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create Time',
    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_payment_no` (`payment_no`),
    KEY `idx_order_id` (`order_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Payment Table';

官方资源

• MySQL 官方文档：https://dev.mysql.com/doc/
• MySQL 数据类型：https://dev.mysql.com/doc/refman/8.0/en/data-types.html
• MySQL 索引优化：https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html

本节小结

在本节中，我们设计了：

第一个是数据库设计原则。 规范化设计、性能优化、扩展性、数据完整性。

第二个是表结构设计。 用户表、商品表、分类表、订单表、订单项表、支付表。

第三个是索引设计。 主键索引、唯一索引、普通索引、组合索引。

第四个是外键关系。 表之间的关联关系和外键约束。

这就是数据库设计与建表。完成数据库设计后，我们就可以开始编写代码了。

在下一节，我们将学习如何集成 MyBatis-Plus，简化数据库操作。