商城数据库设计

一、ER 图（实体关系）

User ─┬─< CartItem ──> Product ──> Category
      │
      └─< Order ──< OrderItem ──> Product

关系说明：

• User 1─N CartItem（一个用户多条购物车条目）
• CartItem N─1 Product（一条购物车条目对应一个商品）
• Product N─1 Category（一个商品属于一个分类）
• User 1─N Order
• Order 1─N OrderItem（一个订单多条明细）
• OrderItem N─1 Product（明细锁定商品）

二、完整建表 SQL

USE mall;

-- 用户表
CREATE TABLE `user` (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username    VARCHAR(50)  NOT NULL,
    password    VARCHAR(100) NOT NULL COMMENT 'BCrypt 密文',
    nickname    VARCHAR(50)  NOT NULL DEFAULT '',
    phone       VARCHAR(20)  DEFAULT NULL,
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted     TINYINT      NOT NULL DEFAULT 0,
    UNIQUE KEY uk_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 分类表
CREATE TABLE `category` (
    id       BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(50) NOT NULL,
    sort     INT         NOT NULL DEFAULT 0 COMMENT '排序，越小越靠前',
    deleted  TINYINT     NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表';

-- 商品表
CREATE TABLE `product` (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id  BIGINT UNSIGNED NOT NULL COMMENT '所属分类',
    name         VARCHAR(200) NOT NULL COMMENT '商品名',
    description  TEXT,
    price        DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '价格，元',
    stock        INT NOT NULL DEFAULT 0 COMMENT '库存',
    image        VARCHAR(500) DEFAULT '' COMMENT '主图 URL',
    status       TINYINT NOT NULL DEFAULT 1 COMMENT '1上架 0下架',
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted      TINYINT NOT NULL DEFAULT 0,
    KEY idx_category (category_id),
    KEY idx_status  (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

-- 购物车表
CREATE TABLE `cart_item` (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NOT NULL,
    product_id  BIGINT UNSIGNED NOT NULL,
    count       INT NOT NULL DEFAULT 1,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购物车条目';

-- 订单主表
CREATE TABLE `order` (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no     VARCHAR(64) NOT NULL COMMENT '业务订单号',
    user_id      BIGINT UNSIGNED NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL COMMENT '订单总金额',
    status       VARCHAR(20)  NOT NULL DEFAULT 'PENDING'
                 COMMENT 'PENDING待支付 PAID已支付 SHIPPED已发货 CANCELLED已取消',
    address      VARCHAR(500) NOT NULL COMMENT '收货地址',
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user_created (user_id, created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 订单明细表
CREATE TABLE `order_item` (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id     BIGINT UNSIGNED NOT NULL,
    product_id   BIGINT UNSIGNED NOT NULL,
    product_name VARCHAR(200) NOT NULL COMMENT '商品名称快照',
    price        DECIMAL(10,2) NOT NULL COMMENT '下单时价格快照',
    count        INT NOT NULL COMMENT '购买数量',
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    KEY idx_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';

三、设计要点解读

① 为什么订单明细要存"快照"

product_name VARCHAR(200) NOT NULL,   -- 快照
price        DECIMAL(10,2) NOT NULL,  -- 快照

商品将来可能改名、涨价、下架、删除。有了快照，历史订单永远显示下单时的真实信息。这是电商数据库的经典设计，面试常问。

② 为什么都用逻辑删除

deleted TINYINT DEFAULT 0，删除 = UPDATE ... SET deleted=1。

• 数据可追溯（运营想看"被删的商品"还有记录）
• 关联数据不悬空（订单明细引用被物理删除的商品会炸）

MP 里配置了逻辑删除字段，selectList 自动带 WHERE deleted = 0。

③ 为什么要冗余 category_id 索引 + 业务唯一键

• KEY idx_category (category_id)：分类筛选常用，必须建索引。
• UNIQUE KEY uk_username、uk_order_no：用户名/订单号不能重复，数据库级兜底（即使代码漏查，数据库也不放行）。

④ 金额精度

DECIMAL(12,2) 订单总额、DECIMAL(10,2) 单价——全部精确小数。

四、seed 数据（方便联调）

INSERT INTO `category` (name, sort) VALUES ('手机', 1), ('电脑', 2), ('配件', 3);

INSERT INTO `product` (category_id, name, description, price, stock, image) VALUES
(1, '5G 智能手机', '旗舰机型，256GB',        2999.00, 100, '/images/product/phone1.png'),
(1, '折叠屏手机',   '内折屏，1TB',            8999.00, 20,  '/images/product/phone2.png'),
(3, '无线耳机',     '降噪版，续航 30h',       599.00,  200, '/images/product/earphone.png'),
(2, '轻薄本电脑',   '16G/512G，2.8K 屏',     4999.00, 50,  '/images/product/laptop.png');

五、本章验收

• [ ] 六个表全部建好，主键/自增/注释齐全
• [ ] seed 数据插入成功
• [ ] 用 MySQL 前几章学的 SQL 查一遍数据
• [ ] 后端 server 连上 mall 库能读到商品（此时可跑 /api/product/list）

建表顺序

先建被引用的表（user/category/product），再建引用它们的表（cart_item/order/order_item）。