MySQL / 系统课时
设计表结构与数据关系
设计表结构与数据关系
已经明确用户、商品、订单和订单明细四个核心实体后,接下来要把它们落成可执行的 MySQL 表结构。本课会完成主键、字段类型、非空约束、外键和唯一约束设计,并通过插入一笔订单验证表之间的关系是否正确。
学习目标
- 能为业务实体选择合理的主键、字段类型和约束
- 能用外键表达一对多关系并拆解多对多关系
- 能通过测试数据检查表结构是否符合订单业务规则
确定主键和核心字段
每张表都应拥有稳定且唯一的主键。示例统一使用 BIGINT AUTO_INCREMENT 作为代理主键,使业务字段变化时不会影响其他表的引用关系。
用户表保存昵称和手机号,手机号需要唯一;商品表保存名称、售价和库存;订单表引用用户并保存状态和创建时间;订单明细同时引用订单和商品,并记录数量与成交单价。
金额使用 DECIMAL(10,2),避免使用浮点类型保存需要精确计算的货币值。数量和库存使用整数,并通过约束阻止明显无效的数据。
建立表之间的关系
订单表中的 user_id 指向用户表主键,形成用户到订单的一对多关系。订单明细中的 order_id 和 product_id 分别指向订单与商品,因此订单明细成为订单和商品之间的关联表。

这里还需要一个业务约束:同一订单中的同一个商品只保留一条明细,因此可以为 order_id 与 product_id 建立联合唯一约束。这样重复插入相同商品时,数据库会直接拒绝错误数据。
创建完整表结构
下面的 SQL 可以直接在 MySQL 8.x 中执行。为了便于重复练习,先删除并重新创建示例数据库。
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;预期结果:四张业务表全部创建成功。
+----------------------+
| Tables_in_order_demo |
+----------------------+
| order_items |
| orders |
| products |
| users |
+----------------------+插入数据验证约束
表结构是否合理不能只看建表语句,还要用一条完整业务链验证。下面先创建用户和商品,再创建订单及订单明细。
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;预期结果:查询能够沿外键关系还原一笔订单的用户、商品、数量和成交单价。
+----------+----------+--------------+----------+------------+
| order_id | nickname | product_name | quantity | unit_price |
+----------+----------+--------------+----------+------------+
| 1 | Alice | 机械键盘 | 2 | 399.00 |
+----------+----------+--------------+----------+------------+检查结构是否合理
设计完成后应至少检查三类问题:是否存在没有主键的表,是否把多值信息塞进单个字段,以及是否遗漏关键业务约束。比如把多个商品编号保存为 1,3,8 这样的字符串,会导致查询、索引和约束都变得困难,应改用订单明细表表达。
外键能够保护引用完整性,但不意味着所有业务规则都应该交给外键处理。订单总金额、库存扣减和订单状态变化通常还需要事务或应用逻辑保证一致性。当前结构的目标,是先让数据拥有稳定主键、清晰关系和最基本的数据库约束,为后续增删改查和事务处理建立可靠基础。