知识小屋

    多表设计实战

    前面讲了三种关系（一对一、一对多、多对多）的概念和写法。这一篇用一个完整的真实场景把它们全部串起来，从需求分析 → 建表 → 插数据 → 查询，完整走一遍。

    场景选在线书店，因为它天然包含了全部三种关系：

    一对一  → 用户 ↔ 用户详情
一对多  → 用户 → 订单、用户 → 收货地址、分类 → 书籍
多对多  → 书籍 ↔ 标签、订单 ↔ 书籍（订单商品）

    一、先想清楚有哪些"东西"

    动手建表之前，先把现实里有哪些实体列出来，再想它们之间的关系。这是设计数据库的第一步。

    用户      → 谁在买书
用户详情  → 用户的隐私资料（生日、身份证、签名）
收货地址  → 书寄到哪里
分类      → 书属于哪一类（文学、技术、历史）
标签      → 书的关键词（畅销、新书、限时折扣）
书籍      → 卖什么
订单      → 用户下的一次单
订单商品  → 这个订单里买了哪几本书、各几本

    关系梳理

    关系类型说明
    用户 ↔ 用户详情一对一一个用户只有一份详情
    用户 → 收货地址一对多一个用户可以有多个地址
    用户 → 订单一对多一个用户可以下多个订单
    分类 → 书籍一对多一个分类下有很多书
    书籍 ↔ 标签多对多一本书有多个标签，一个标签下有多本书
    订单 ↔ 书籍多对多一个订单有多本书，一本书也能出现在多个订单

    二、ER 关系总览

    把全部 9 张表的关系拆成三组来看，避免一张大图乱成一团。三组之间通过 users.id 和 books.id 衔接。

    ① 用户域（一对一 + 一对多）

    ┌───────────────┐              ┌──────────┐              ┌────────────────┐
│ user_profiles │◄─── 1:1 ────►│  users   │◄─── 1:N ────►│ user_addresses │
│  user_id (UQ) │              │    id    │              │    user_id     │
└───────────────┘              └──────────┘              └────────────────┘

    user_profiles.user_id 加了 UNIQUE 所以是一对一；user_addresses.user_id 没加，所以是一对多——区别就这一行约束。

    ② 订单商品域（一对多 + 多对多中间表）

    ┌──────────┐         ┌──────────────┐         ┌──────────┐         ┌────────────┐
│  orders  │── 1:N ─►│ order_items  │◄─ 1:N ──│  books   │◄─ 1:N ──│ categories │
│ user_id  │         │  order_id    │         │ cat_id   │         │     id     │
└──────────┘         │ book_id, qty │         └──────────┘         └────────────┘
                     └──────────────┘

    order_items 夹在 orders 和 books 中间——它既是 orders 的一对多（一个订单有多个明细），又是 books 的一对多（一本书能出现在多个明细里）。两边都是 1:N 指向它，所以 orders 和 books 之间就是多对多，order_items 就是那张中间表（只不过它额外存了数量、快照价）。

    ③ 标签域（多对多）

    ┌──────────┐          ┌────────────┐          ┌──────────┐
│  books   │◄─ N:N ──│ book_tags  │── N:N ─►│   tags   │
│    id    │          │ book_id    │          │    id    │
└──────────┘          └────────────┘          └──────────┘

    book_tags 是纯中间表，只存关系（book_id + tag_id），不存别的信息。注意 books 同时出现在 ② 和 ③ 里，是这两组关系的衔接点。

    下面逐个表讲建表细节。

    三、建表顺序很重要

    外键引用别的表，被引用的表必须先存在。所以建表顺序是：父表在前，子表在后。

    ① users              （最顶层，谁都引用它）
② user_profiles      （引用 users）
③ user_addresses     （引用 users）
④ categories         （独立）
⑤ tags               （独立）
⑥ books              （引用 categories）
⑦ book_tags          （引用 books + tags，中间表）
⑧ orders             （引用 users + user_addresses）
⑨ order_items        （引用 orders + books，中间表）

    记住一个口诀：先建不依赖别人的，再建依赖别人的；中间表最后建。

    四、开始建表

    ① users —— 用户表

    CREATE TABLE users (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username    VARCHAR(50)  NOT NULL,
    password    VARCHAR(100) NOT NULL,
    email       VARCHAR(100) NOT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (username),     -- ① 用户名不能重复
    UNIQUE (email)         -- ② 邮箱不能重复
);

    为什么这么设计：

    • username 和 email 都加了 UNIQUE，注册时数据库层面就保证不重名，不用先查再插。
    • created_at 用 DEFAULT CURRENT_TIMESTAMP，插入时不填也自动有值。

    ② user_profiles —— 用户详情表（一对一）

    CREATE TABLE user_profiles (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id      BIGINT UNSIGNED NOT NULL,
    real_name    VARCHAR(50),
    birthday     DATE,
    id_card      VARCHAR(18),
    bio          VARCHAR(500),
    PRIMARY KEY (id),
    UNIQUE (user_id),                          -- ① 关键：保证一对一
    FOREIGN KEY (user_id) REFERENCES users(id)  -- ② 引用 users
);

    用到了什么约束，为什么：

    ① UNIQUE (user_id) —— 区分一对一和一对多的关键

    没有它，同一个 user_id 可以插多条详情，就变成一对多了。加了它，一个用户只能对应一条详情，这才是一对一。

    ② 外键引用 users —— 详情必须属于一个真实存在的用户。

    为什么不直接放 users 表？

    id_card、birthday 这些是隐私字段，查询频率低。拆出去后，普通的用户列表查询不用碰这些字段，更安全也更轻。这是"字段多、不常用、涉及隐私 → 拆出去"的典型场景。

    ③ user_addresses —— 收货地址表（一对多）

    CREATE TABLE user_addresses (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     BIGINT UNSIGNED NOT NULL,
    receiver    VARCHAR(50)  NOT NULL,    -- 收件人
    phone       VARCHAR(20)  NOT NULL,    -- 收件电话
    detail      VARCHAR(255) NOT NULL,    -- 详细地址
    is_default  TINYINT      NOT NULL DEFAULT 0,   -- 1=默认地址
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    -- ① 删用户时，地址跟着删
);

    用到了什么约束，为什么：

    • 外键放"多"的一方（user_addresses），不在 users 表里存地址列表。原因和前面讲的师生关系一样：一个用户有多个地址，你在用户表里没法存得下。
    • ON DELETE CASCADE：删用户时，他的收货地址自动清掉，不用手动维护。
    • 注意这里没有 UNIQUE(user_id)，所以一个用户可以有多条地址——这是一对多。

    对比 ②：user_profiles 有 UNIQUE(user_id) 所以是一对一；user_addresses 没有，所以是一对多。唯一区别就是这一行约束。

    ④ categories —— 分类表

    CREATE TABLE categories (
    id     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name   VARCHAR(50) NOT NULL,
    sort   INT NOT NULL DEFAULT 0,    -- 排序权重
    PRIMARY KEY (id),
    UNIQUE (name)         -- ① 分类名不重复
);

    ① UNIQUE (name) —— 避免"文学"和"文学 "这种重复分类。

    ⑤ tags —— 标签表

    CREATE TABLE tags (
    id    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name  VARCHAR(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (name)         -- ① 标签名不重复
);

    ⑥ books —— 书籍表（一对多：分类 → 书籍）

    CREATE TABLE books (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id   BIGINT UNSIGNED NOT NULL,    -- ① 必须有分类
    title         VARCHAR(100) NOT NULL,
    author        VARCHAR(50)  NOT NULL,
    price         DECIMAL(10,2) NOT NULL,      -- ② 金额用 DECIMAL
    stock         INT NOT NULL DEFAULT 0,      -- 库存
    published_at  DATE,
    PRIMARY KEY (id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE RESTRICT     -- ③ 分类下还有书时，禁止删分类
);

    用到了什么约束，为什么：

    ① category_id NOT NULL —— 每本书必须有分类，不允许空。

    ② DECIMAL(10,2) 而不是 FLOAT —— 存钱用 DECIMAL。FLOAT 有精度丢失，0.1 + 0.2 可能变成 0.30000000000000004，算账时会出问题。DECIMAL(10,2) 表示总共 10 位、小数 2 位，最大存 99999999.99。

    ③ ON DELETE RESTRICT —— 分类下还有书时不让删，防止书变成"孤儿数据"。这是默认行为，写出来更清晰。

    ⑦ book_tags —— 书籍标签中间表（多对多）

    CREATE TABLE book_tags (
    book_id   BIGINT UNSIGNED NOT NULL,
    tag_id    BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (book_id, tag_id),                          -- ① 联合主键防重复
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id)  REFERENCES tags(id)  ON DELETE CASCADE
);

    用到了什么约束，为什么：

    ① PRIMARY KEY (book_id, tag_id) —— 联合主键，防止一本书重复绑定同一个标签：

    (1, 1) → ✅ 书1 绑 标签"畅销"
(1, 2) → ✅ 书1 绑 标签"新书"
(1, 1) → ❌ 重复，报错

    中间表只存关系，不存别的信息，所以不需要单独的自增 id，两个外键直接做主键就行。

    ON DELETE CASCADE：删书或删标签时，对应的绑定关系自动清掉。

    ⑧ orders —— 订单表（一对多：用户 → 订单）

    CREATE TABLE orders (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_no        VARCHAR(32) NOT NULL,         -- 订单号
    user_id         BIGINT UNSIGNED NOT NULL,
    address_id      BIGINT UNSIGNED NOT NULL,     -- 下单时锁定的收货地址
    total_amount    DECIMAL(10,2) NOT NULL,
    status          TINYINT NOT NULL DEFAULT 0,   -- 0待付款 1已付款 2已发货 3已完成
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (order_no),
    FOREIGN KEY (user_id)    REFERENCES users(id),
    FOREIGN KEY (address_id) REFERENCES user_addresses(id)
        ON DELETE RESTRICT     -- ① 地址被订单引用时不让删
);

    ① 为什么地址被引用时不让删？

    订单是历史记录，必须能查到"当时寄到哪"。如果删了地址，订单的 address_id 就指向了不存在的地址。所以用 RESTRICT 保护住。

    现实项目里更常见的做法是给地址加"软删除"字段（is_deleted），而不是真删。但这里用 RESTRICT 演示外键策略。

    ⑨ order_items —— 订单商品表（订单 ↔ 书籍 的多对多）

    CREATE TABLE order_items (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id    BIGINT UNSIGNED NOT NULL,
    book_id     BIGINT UNSIGNED NOT NULL,
    quantity    INT NOT NULL,                    -- 买了几本
    unit_price  DECIMAL(10,2) NOT NULL,          -- 下单时单价（快照）
    PRIMARY KEY (id),
    UNIQUE (order_id, book_id),                  -- ① 一个订单里同一本书只能有一行
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id)  REFERENCES books(id)  ON DELETE RESTRICT
);

    用到了什么约束，为什么：

    ① UNIQUE (order_id, book_id) —— 一个订单里，同一本书只能出现一行。防止"订单1 买了书A 2本"和"订单1 买了书A 3本"这种重复记录。

    为什么这是"多对多的中间表"，但又和 book_tags 不太一样？

    因为它除了关系还存了别的信息：买了多少本（quantity）、买的时候单价多少（unit_price）。这种"带业务字段"的中间表，一般会加自增 id 做主键，再用联合唯一约束防重复。

    unit_price 是"快照"——重点理解

    为什么不直接 JOIN books 拿价格？因为书的价格会变。今天下单 39 元，明天书涨到 49 元，你不能让历史订单的总价跟着变。所以下单时把当时的单价存一份到 order_items.unit_price，这就是"数据快照"思想。

    五、建表顺序回顾

    -- 严格按这个顺序执行，否则外键创建会失败

CREATE TABLE users            (...);  -- ①
CREATE TABLE user_profiles    (...);  -- ②
CREATE TABLE user_addresses   (...);  -- ③
CREATE TABLE categories       (...);  -- ④
CREATE TABLE tags             (...);  -- ⑤
CREATE TABLE books            (...);  -- ⑥
CREATE TABLE book_tags        (...);  -- ⑦
CREATE TABLE orders           (...);  -- ⑧
CREATE TABLE order_items      (...);  -- ⑨

    提示：删表顺序正好相反，DROP TABLE order_items 必须在 DROP TABLE orders 之前，否则会因为外键约束删不掉。或者先 SET FOREIGN_KEY_CHECKS = 0; 再删。

    六、插入测试数据

    光建表没法练习查询，来点数据。

    -- 用户
INSERT INTO users (username, password, email) VALUES
('tom',   'pwd123', 'tom@example.com'),
('jerry', 'pwd456', 'jerry@example.com');

-- 用户详情（一对一）
INSERT INTO user_profiles (user_id, real_name, birthday, bio) VALUES
(1, '汤姆',   '1995-03-12', '爱看小说'),
(2, '杰瑞',   '2000-07-08', '技术宅');

-- 收货地址（一对多，tom 有两个地址）
INSERT INTO user_addresses (user_id, receiver, phone, detail, is_default) VALUES
(1, '汤姆', '13800000001', '北京市海淀区xx路1号', 1),
(1, '汤姆', '13800000001', '北京市朝阳区yy路2号', 0),
(2, '杰瑞', '13800000002', '上海市浦东新区zz路3号', 1);

-- 分类
INSERT INTO categories (name, sort) VALUES
('文学', 1),
('技术', 2),
('历史', 3);

-- 标签
INSERT INTO tags (name) VALUES
('畅销'),
('新书'),
('限时折扣');

-- 书籍（一对多：分类 → 书籍）
INSERT INTO books (category_id, title, author, price, stock) VALUES
(2, 'MySQL 从入门到精通', '老王', 59.00, 100),
(2, 'Python 编程',         '老李', 69.00, 50),
(1, '活着',                '余华', 35.00, 200),
(3, '明朝那些事儿',        '当年明月', 49.00, 80);

-- 书籍标签（多对多）
INSERT INTO book_tags (book_id, tag_id) VALUES
(1, 1),   -- MySQL 书 是 畅销
(1, 3),   -- MySQL 书 是 限时折扣
(2, 2),   -- Python 书 是 新书
(3, 1),   -- 活着 是 畅销
(4, 1);   -- 明朝那些事儿 是 畅销

-- 订单（一对多：用户 → 订单）
INSERT INTO orders (order_no, user_id, address_id, total_amount, status) VALUES
('NO20240101001', 1, 1, 128.00, 1),
('NO20240102001', 2, 3, 69.00,  1);

-- 订单商品（订单 ↔ 书籍 多对多）
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
(1, 1, 1, 59.00),    -- 订单1：MySQL 书 1本
(1, 3, 2, 35.00),    -- 订单1：活着 2本，合计 70 → 但 total_amount 写的 128，凑合看
(2, 2, 1, 69.00);    -- 订单2：Python 书 1本

    七、几种典型查询

    数据有了，跑几个能体现关系设计的查询。（JOIN 语法详见下一篇）

    1. 查每个用户的详情（一对一）

    SELECT u.username, p.real_name, p.birthday, p.bio
FROM users u
INNER JOIN user_profiles p ON u.id = p.user_id;

    2. 查 tom 的所有收货地址（一对多）

    SELECT a.receiver, a.phone, a.detail, a.is_default
FROM user_addresses a
INNER JOIN users u ON a.user_id = u.id
WHERE u.username = 'tom';

    3. 查"技术"分类下的所有书（一对多）

    SELECT b.title, b.author, b.price
FROM books b
INNER JOIN categories c ON b.category_id = c.id
WHERE c.name = '技术';

    4. 查一本书的所有标签（多对多）

    -- 查 MySQL 书的标签
SELECT t.name
FROM tags t
INNER JOIN book_tags bt ON t.id = bt.tag_id
INNER JOIN books b      ON bt.book_id = b.id
WHERE b.title = 'MySQL 从入门到精通';
-- 结果：畅销、限时折扣

    多对多查询的关键：中间表要 JOIN 两次——一次连到 tags，一次连到 books。

    5. 查某个标签下有哪些书（多对多的反向）

    SELECT b.title, b.author
FROM books b
INNER JOIN book_tags bt ON b.id = bt.book_id
INNER JOIN tags t       ON bt.tag_id = t.id
WHERE t.name = '畅销';
-- 结果：MySQL 从入门到精通、活着、明朝那些事儿

    6. 查一个订单里买了哪些书、各几本（带快照价格）

    SELECT b.title, oi.quantity, oi.unit_price,
       (oi.quantity * oi.unit_price) AS subtotal
FROM order_items oi
INNER JOIN books  b ON oi.book_id = b.id
INNER JOIN orders o ON oi.order_id = o.id
WHERE o.order_no = 'NO20240101001';

    注意这里用的是 oi.unit_price（订单里的快照价），不是 b.price（书当前的价）——这就是前面强调快照的原因。

    八、外键策略在这个项目里的完整分布

    把整个项目的外键策略汇总一下，对照着看：

    表外键引用删除策略为什么
    user_profilesuser_idusers(默认 RESTRICT)详情不能孤立存在
    user_addressesuser_idusersCASCADE删用户连带删地址
    bookscategory_idcategoriesRESTRICT分类下有书不让删
    book_tagsbook_id / tag_idbooks / tagsCASCADE中间表跟着主表清
    ordersuser_idusers(RESTRICT)用户有订单不让删
    ordersaddress_iduser_addressesRESTRICT历史订单要能查地址
    order_itemsorder_idordersCASCADE删订单连带删明细
    order_itemsbook_idbooksRESTRICT被订单引用的书不让删

    规律：

    中间表（关系表） → 一般用 CASCADE，主表删了关系自动清
业务主表（用户、书、分类） → 一般用 RESTRICT，有引用就不让删
快照/历史 → 必须 RESTRICT，保护历史数据

    九、几个常见坑

    坑 1：建表顺序反了

    -- ❌ 先建 posts，引用还不存在的 categories
CREATE TABLE posts (...,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);
-- 报错：Can't create table ... foreign key constraint fails

    解决： 先建被引用的表。

    坑 2：外键字段类型不一致

    -- ❌ 父表 UNSIGNED，子表不是
categories.id      BIGINT UNSIGNED
books.category_id  BIGINT
-- 外键建不起来

    解决： 两边完全一致，包括 UNSIGNED。

    坑 3：忘了 UNIQUE，一对一变成一对多

    -- ❌ 漏了 UNIQUE(user_id)，一个用户能插多条详情
CREATE TABLE user_profiles (
    user_id BIGINT UNSIGNED NOT NULL,
    ...
    FOREIGN KEY (user_id) REFERENCES users(id)
);

    解决： 想要一对一，外键加 UNIQUE。

    坑 4：存金额用 FLOAT

    price FLOAT  -- ❌ 精度丢失
price DECIMAL(10,2)  -- ✅ 精确

    坑 5：中间表忘了防重复

    -- ❌ 没有联合主键/联合唯一，能重复绑定
CREATE TABLE book_tags (
    book_id BIGINT UNSIGNED,
    tag_id  BIGINT UNSIGNED
);

-- ✅ 联合主键
PRIMARY KEY (book_id, tag_id)

    下一篇开始讲怎么把这些表用 JOIN 连起来查询。