MySQL / 系统课时

设计表结构与数据关系

设计表结构与数据关系

已经明确用户、商品、订单和订单明细四个核心实体后,接下来要把它们落成可执行的 MySQL 表结构。本课会完成主键、字段类型、非空约束、外键和唯一约束设计,并通过插入一笔订单验证表之间的关系是否正确。

学习目标

  • 能为业务实体选择合理的主键、字段类型和约束
  • 能用外键表达一对多关系并拆解多对多关系
  • 能通过测试数据检查表结构是否符合订单业务规则

确定主键和核心字段

每张表都应拥有稳定且唯一的主键。示例统一使用 BIGINT AUTO_INCREMENT 作为代理主键,使业务字段变化时不会影响其他表的引用关系。

用户表保存昵称和手机号,手机号需要唯一;商品表保存名称、售价和库存;订单表引用用户并保存状态和创建时间;订单明细同时引用订单和商品,并记录数量与成交单价。

金额使用 DECIMAL(10,2),避免使用浮点类型保存需要精确计算的货币值。数量和库存使用整数,并通过约束阻止明显无效的数据。

建立表之间的关系

订单表中的 user_id 指向用户表主键,形成用户到订单的一对多关系。订单明细中的 order_id 和 product_id 分别指向订单与商品,因此订单明细成为订单和商品之间的关联表。

订单系统表结构与外键关系

这里还需要一个业务约束:同一订单中的同一个商品只保留一条明细,因此可以为 order_id 与 product_id 建立联合唯一约束。这样重复插入相同商品时,数据库会直接拒绝错误数据。

创建完整表结构

下面的 SQL 可以直接在 MySQL 8.x 中执行。为了便于重复练习,先删除并重新创建示例数据库。

sql
DROP DATABASE IF EXISTS order_demo;
CREATE DATABASE order_demo CHARACTER SET utf8mb4;
USE order_demo;

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    nickname VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    CHECK (price >= 0),
    CHECK (stock >= 0)
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'CREATED',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    CONSTRAINT uk_order_product UNIQUE (order_id, product_id),
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_items_product
        FOREIGN KEY (product_id) REFERENCES products(id),
    CHECK (quantity > 0),
    CHECK (unit_price >= 0)
);

SHOW TABLES;

预期结果:四张业务表全部创建成功。

text
+----------------------+
| Tables_in_order_demo |
+----------------------+
| order_items          |
| orders               |
| products             |
| users                |
+----------------------+

插入数据验证约束

表结构是否合理不能只看建表语句,还要用一条完整业务链验证。下面先创建用户和商品,再创建订单及订单明细。

sql
INSERT INTO users (nickname, phone)
VALUES ('Alice', '13800000001');

INSERT INTO products (name, price, stock)
VALUES ('机械键盘', 399.00, 20);

INSERT INTO orders (user_id)
VALUES (1);

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 1, 2, 399.00);

SELECT
    o.id AS order_id,
    u.nickname,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price
FROM orders AS o
JOIN users AS u ON u.id = o.user_id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id;

预期结果:查询能够沿外键关系还原一笔订单的用户、商品、数量和成交单价。

text
+----------+----------+--------------+----------+------------+
| order_id | nickname | product_name | quantity | unit_price |
+----------+----------+--------------+----------+------------+
|        1 | Alice    | 机械键盘     |        2 |     399.00 |
+----------+----------+--------------+----------+------------+

检查结构是否合理

设计完成后应至少检查三类问题:是否存在没有主键的表,是否把多值信息塞进单个字段,以及是否遗漏关键业务约束。比如把多个商品编号保存为 1,3,8 这样的字符串,会导致查询、索引和约束都变得困难,应改用订单明细表表达。

外键能够保护引用完整性,但不意味着所有业务规则都应该交给外键处理。订单总金额、库存扣减和订单状态变化通常还需要事务或应用逻辑保证一致性。当前结构的目标,是先让数据拥有稳定主键、清晰关系和最基本的数据库约束,为后续增删改查和事务处理建立可靠基础。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

设计表结构与数据关系

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通