前言

订单是电商核心模块，表设计缺陷会导致联表慢、统计复杂、数据冗余。本文适配小型商城 / 毕设项目，设计 6 张核心业务表，梳理一对多、多对多关系，完整建表语句 + 业务查询，可直接用于开发。

一、业务实体关系

1. 用户 (user) 一对多 订单 (order)：一个用户多个订单
2. 订单 (order) 一对多 订单详情 (order_item)：一张订单多条商品记录
3. 商品分类 (category) 一对多 商品 (goods)
4. 商品 (goods) 一对多 订单详情：商品可被多次下单
5. 用户 (user) 一对多 购物车 (cart)

二、完整建表 SQL

CREATE DATABASE IF NOT EXISTS shop_db DEFAULT CHARSET utf8mb4;
USE shop_db;

-- 1. 用户表
CREATE TABLE user (
    user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '登录账号',
    password VARCHAR(64) NOT NULL COMMENT '加密密码',
    phone CHAR(11) UNIQUE COMMENT '手机号',
    real_name VARCHAR(10) COMMENT '真实姓名',
    create_time DATETIME DEFAULT NOW() COMMENT '注册时间'
) COMMENT '用户表';

-- 2. 商品分类表
CREATE TABLE category (
    cat_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '分类ID',
    cat_name VARCHAR(30) NOT NULL COMMENT '分类名称',
    parent_id INT DEFAULT 0 COMMENT '父分类ID，0为一级分类'
) COMMENT '商品分类';

-- 3. 商品表
CREATE TABLE goods (
    goods_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID',
    goods_name VARCHAR(100) NOT NULL COMMENT '商品名称',
    cat_id INT NOT NULL COMMENT '所属分类',
    price DECIMAL(10,2) NOT NULL COMMENT '售价',
    stock INT NOT NULL DEFAULT 0 COMMENT '库存',
    cover VARCHAR(255) COMMENT '商品封面图',
    create_time DATETIME DEFAULT NOW()
) COMMENT '商品表';

-- 4. 订单主表（存储订单公共信息）
CREATE TABLE `order` (
    order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
    order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单编号',
    user_id INT NOT NULL COMMENT '下单用户',
    total_amount DECIMAL(10,2) NOT NULL COMMENT '订单总金额',
    order_status TINYINT NOT NULL COMMENT '0待支付 1已支付 2已发货 3已完成 4已取消',
    receive_name VARCHAR(10) COMMENT '收货人',
    receive_phone CHAR(11) COMMENT '收货手机号',
    receive_addr VARCHAR(200) COMMENT '收货地址',
    create_time DATETIME DEFAULT NOW() COMMENT '下单时间',
    pay_time DATETIME COMMENT '支付时间'
) COMMENT '订单主表';

-- 5. 订单详情表（订单与商品多对多中间表）
CREATE TABLE order_item (
    item_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '详情ID',
    order_id INT NOT NULL COMMENT '关联订单',
    goods_id INT NOT NULL COMMENT '关联商品',
    goods_name VARCHAR(100) NOT NULL COMMENT '下单时商品名称（冗余防商品改名）',
    goods_price DECIMAL(10,2) NOT NULL COMMENT '下单单价',
    buy_num INT NOT NULL COMMENT '购买数量',
    subtotal DECIMAL(10,2) NOT NULL COMMENT '小计金额'
) COMMENT '订单详情表';

-- 6. 购物车表
CREATE TABLE cart (
    cart_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    goods_id INT NOT NULL,
    buy_num INT NOT NULL DEFAULT 1,
    UNIQUE KEY uk_user_goods(user_id, goods_id)
) COMMENT '购物车表';

三、核心业务查询示例

-- 1. 查询某用户所有订单及订单内商品
SELECT o.order_no, o.total_amount, o.order_status, i.goods_name, i.buy_num, i.subtotal
FROM `order` o
JOIN order_item i ON o.order_id = i.order_id
WHERE o.user_id = 1;

-- 2. 统计每个商品总销量
SELECT g.goods_name, SUM(i.buy_num) total_sales
FROM goods g
LEFT JOIN order_item i ON g.goods_id = i.goods_id
GROUP BY g.goods_id, g.goods_name
ORDER BY total_sales DESC;

-- 3. 统计用户下单总金额
SELECT u.username, SUM(o.total_amount) user_consume
FROM user u
LEFT JOIN `order` o ON u.user_id = o.user_id
WHERE o.order_status IN (1,2,3)
GROUP BY u.user_id, u.username;

四、设计亮点说明

1. 订单与商品拆分主表 + 详情表，避免一条订单多条商品重复存储地址、用户信息；
2. 订单详情冗余商品名称、单价，商品修改名称 / 价格不影响历史订单；
3. 购物车设置联合唯一索引，同一用户同一商品仅一条购物车记录；
4. 订单状态用 tinyint 存储，节省空间；
5. 订单编号独立唯一，业务对外展示，主键 id 仅内部关联使用。