电商库表设计实战

第 7 章 · 电商库表设计实战

本章目标：在 shop-db 项目中完成 shop_db 电商核心库的完整 DDL walkthrough；逐表讲解 categories、products、users、orders、order_lines、carts、cart_items 的字段含义、外键约束与索引策略；理解 slug + is_published + price（分） 字段约定；输出与 gin-web ch05 GORM 模型字段对照表，为 ch08～ch12 的 Java 访问层打基础。

学时建议：5～6 小时（含 2 小时 DDL 跟练与 ER 图绘制）

前置：完成 go-database ch01～ch06（SQL 查询、JOIN、事务、DDL、ER 与范式）；了解基本电商业务名词。

7.1 场景说明：从 ER 图到可执行 DDL

ch06 已将 shop_db 抽象为 ER 模型。本章把模型落地为 MySQL 8 可执行 DDL，作为 shop-db 与后续 毕业项目 ch17 的 schema 基线。

说明：教学项目路径 ~/go-learn/shop-db；数据库名 shop_db；对外 API 域名 https://api.example.com 仅为占位，不涉及任何企业内部仓库。

字段演进约定（全模块统一）：

7.2 全局 ER 关系图

┌─────────────┐       ┌─────────────┐       ┌─────────────┐
│ categories  │ 1   N │  products   │ N   M │    tags     │  （ch13 扩展，本章预留）
│ id, slug    │◄──────│ category_id │       │  (可选)     │
└─────────────┘       └──────┬──────┘       └─────────────┘
                             │
         ┌───────────────────┼───────────────────┐
         │ N                 │ N                 │ N
         ▼                   ▼                   ▼
┌─────────────┐       ┌─────────────┐       ┌─────────────┐
│ cart_items  │       │ order_lines │       │  (reviews)  │  ch17 扩展
│ cart_id     │       │ order_id    │       └─────────────┘
│ product_id  │       │ product_id  │
└──────┬──────┘       └──────┬──────┘
       │ N                 │ N
       ▼ 1                 ▼ 1
┌─────────────┐       ┌─────────────┐
│   carts     │       │   orders    │
│ user_id     │       │ user_id     │
└──────┬──────┘       └──────┬──────┘
       │ N                 │ N
       └─────────┬─────────┘
                 ▼ 1
          ┌─────────────┐
          │    users    │
          │ id, email   │
          └─────────────┘

关系摘要：

7.3 建库与字符集

MySQL 8（shop-db 默认）：

CREATE DATABASE IF NOT EXISTS shop_db
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE shop_db;

Docker 快速启动（可选）：

docker run -d --name shop-mysql \
  -e MYSQL_ROOT_PASSWORD=root_pass \
  -e MYSQL_DATABASE=shop_db \
  -e MYSQL_USER=shop_user \
  -e MYSQL_PASSWORD=shop_pass \
  -p 3306:3306 \
  mysql:8.0

7.4 表 1：categories（商品分类）

CREATE TABLE IF NOT EXISTS categories (
    id          BIGINT       NOT NULL AUTO_INCREMENT,
    name        VARCHAR(64)  NOT NULL,
    slug        VARCHAR(64)  NOT NULL,
    sort_order  INT          NOT NULL DEFAULT 0,
    created_at  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at  DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
                    ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY uk_categories_slug (slug),
    KEY idx_categories_sort (sort_order, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

设计要点：

• slug 全局唯一，供 /catalog/{slug}/ 路由使用。
• 不在分类表存商品数量；用 COUNT(*) 或冗余字段（ch14 优化再议）。

7.5 表 2：products（商品）

CREATE TABLE IF NOT EXISTS products (
    id            BIGINT        NOT NULL AUTO_INCREMENT,
    category_id   BIGINT        NOT NULL,
    title         VARCHAR(128)  NOT NULL,
    slug          VARCHAR(128)  NOT NULL,
    description   TEXT          NULL,
    price         BIGINT        NOT NULL COMMENT '单价，单位：分',
    stock         INT           NOT NULL DEFAULT 0,
    is_published  TINYINT(1)    NOT NULL DEFAULT 0,
    created_at    DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at    DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
                      ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY uk_products_slug (slug),
    KEY idx_products_category_published (category_id, is_published, created_at DESC),
    KEY idx_products_published_price (is_published, price),
    CONSTRAINT fk_products_category
        FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT chk_products_price CHECK (price >= 0),
    CONSTRAINT chk_products_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

价格（分）示例：

索引策略 §7.5.1：

7.6 表 3：users（用户）

CREATE TABLE IF NOT EXISTS users (
    id            BIGINT        NOT NULL AUTO_INCREMENT,
    email         VARCHAR(255)  NOT NULL,
    password_hash VARCHAR(255)  NOT NULL,
    nickname      VARCHAR(64)   NOT NULL DEFAULT '',
    is_active     TINYINT(1)    NOT NULL DEFAULT 1,
    created_at    DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at    DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
                      ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY uk_users_email (email),
    KEY idx_users_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

7.7 表 4：orders 与 order_lines（订单）

7.7.1 orders

CREATE TABLE IF NOT EXISTS orders (
    id          BIGINT        NOT NULL AUTO_INCREMENT,
    user_id     BIGINT        NOT NULL,
    order_no    VARCHAR(32)   NOT NULL,
    status      VARCHAR(16)   NOT NULL DEFAULT 'pending',
    total_cents BIGINT        NOT NULL DEFAULT 0,
    created_at  DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at  DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
                    ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY uk_orders_order_no (order_no),
    KEY idx_orders_user_created (user_id, created_at DESC),
    KEY idx_orders_status (status, created_at DESC),
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT chk_orders_status
        CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    CONSTRAINT chk_orders_total CHECK (total_cents >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;