MySQL动手实战：电商订单系统数据库设计核心技能通关

设计一个支持。

一只特体独行的Pig

1678人浏览 · 2025-04-04 10:21:08

一只特体独行的Pig · 2025-04-04 10:21:08 发布

案例背景：在线商城订单系统

通过实战案例巩固知识，设计一个电商平台的订单管理系统案例，涵盖数据库设计、复杂查询、事务、索引优化、等核心知识点。

需求概述：设计一个支持用户注册、商品浏览、下单、支付、库存管理、订单统计的数据库系统，并实现高效查询与数据安全

1.详细关系说明

上述功能可以分为用户表、订单表+订单详情表、商品表。

1.1 用户与订单

关系：1：N (一个用户可以创建多个订单)
外键：orders.user_id --> users.user_id

1.2 订单与订单详情

关系：1：N(一个订单可以包含多个商品条目)
外键：order_items.order_id–>order.order_id
级联删除：当订单被删除时，其关联的订单详情自动删除

1.3 商品与订单详情

关系：1：N(一个商品可以被多个订单购买)
外键：order_item.product_id -->products.product_id

2.关键设计点

2.1 E-R的关键设计点

主键
- users.user_id、products.product_id 为自增主键
- orders.order_id 为业务主键
- order_items 使用联合主键 (order_id, product_id)
外键与约束
- 订单表通过user_id 关联用户表，确保订单归属有效用户
- 订单详情表通过product_id 关联商品表，确保商品存在
状态管理
- orders.status 使用枚举类型（pending, paid, shipped, 等），约束订单状态流转。
冗余字段设计
- order_items.price 冗余商品价格，避免商品价格变动影响历史订单。
- 在 order_items 中冗余 price 字段，可读性，以空间换时间

2.2 详情页中冗余字段`price`的分析

对于详情页中的订单金额，一般都会涉及到优惠的场景，对于跨境商品也可能涉及到税费的问题，所以详情页中的订单金额是需要多项动态计算得出的。

复杂计算逻辑：订单金额可能由以下子项动态计算得出
```
商品总价+运费-折扣-优惠卷-[税费]
```
若每次查询都实时计算，需要关联多张表（订单表，商品表等），并执行复杂的运算
高频访问需求：

订单列表也，报表统计等场景需要频繁展示总金额，实时计算会导致以下问题
- 数据库负载增加：多次表关联和计算消耗资源
- 查询延迟上升：复杂查询影响用户体验
使用冗余字段的优势
- 查询性能提升：直接读取预存的总金额字段，避免实时计算
- 兼容历史数据：即使计算规则变更，冗余字段可保留历史快照

3.数据库中表结构

3.1 用户表

核心知识点：主键（自增）、唯一约束、字符集(utf8mb4)

CREATE TABLE users(
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash CHAR(64) NOT NULL, -- SHA-256加密
    create_at DATETIME DEFAULT CURRENT_TIMESTAMP
)ENGINE=InnODB DEFAULT CHARSET=utf8mb4;

3.2 商品表

核心知识点：浮点数精度、无符号数

CREATE TABLE products(
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) UNSIGNED NOT NULL, -- 非负数
    stock INT UNSIGNED DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE  -- 可售/下架
);

3.3 订单表

核心知识点：外键、状态枚举、索引

CREATE TABLE orders(
	order_id CHAR(12) PRIMARY KEY, -- 如'ORD202307001'
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) UNSIGNED NOT NULL,
    status ENUM('pending','paid','shipped','completed','cancelled'),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    INDEX idx_created_at (created_at)
);

3.4 订单详情表

核心知识点：联合主键、级联删除

CREATE TABLE order_items(
	order_id CHAR(12) NOT NULL,
    product_id INT NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    price DECIMAL(10,2) UNSIGNED NOT NULL,
    PRIMARY KEY(order_id,product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

联合主键的作用
- 唯一性约束：
  - 确保同一订单中，同一产品只能出现一次
  - 防止重复添加相同产品到同一订单（列如：误操作导致同一商品被多次下单。）
- 业务逻辑映射
  - 在电商场景中，一个订单可以包含多个产品，但每个产品项（订单+产品组合）是唯一的。

4.数据操作与复杂查询

4.1插入数据

用户表 (users)

INSERT INTO users (username, email, password_hash) VALUES
('张三', 'zhangsan@example.com', SHA2('zhangsan123', 256)),
('李四', 'lisi@example.com', SHA2('lisi456', 256)),
('王五', 'wangwu@tech.com', SHA2('wangwu789', 256)),
('赵六', 'zhaoliu@mail.com', SHA2('zhaoliu000', 256)),
('陈七', 'chenqi@music.com', SHA2('chenqi999', 256));

商品表 (products)

INSERT INTO products (name, price, stock, is_active) VALUES
('苹果 iPhone 15 Pro', 9999.00, 50, TRUE),
('无线机械键盘', 299.50, 100, TRUE),
('27英寸4K显示器', 2499.00, 30, TRUE),
('便携蓝牙音箱', 199.00, 0, FALSE), -- 库存为0，已下架
('Type-C数据线', 49.90, 200, TRUE),
('笔记本电脑双肩包', 399.00, 80, TRUE),
('主动降噪耳机', 1299.00, 20, TRUE),
('智能运动手表', 899.00, 15, TRUE),
('平板电脑支架', 89.00, 150, TRUE),
('无线静音鼠标', 159.00, 60, TRUE);

订单表 (orders)

（订单数据无需修改，直接复用原有逻辑）

INSERT INTO orders (order_id, user_id, total_amount, status) VALUES
('ORD202307001', 1, 9999.00, 'completed'),  -- 张三购买iPhone
('ORD202307002', 2, 3098.00, 'paid'),       -- 李四购买键盘+显示器（已修正总金额）
('ORD202307003', 3, 548.70, 'pending'),     -- 王五购买背包+数据线（已修正总金额）
('ORD202307004', 1, 399.00, 'shipped'),    -- 张三再次购买背包
('ORD202307005', 4, 2198.00, 'cancelled');  -- 赵六取消的订单
('ORD202307006', 1, 9999.00, 'completed'),  -- 张三购买iPhone
('ORD202307007', 2, 3098.00, 'completed');    -- 李四购买键盘+显示器

订单详情表 (order_items)

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
-- ORD202307001 (iPhone)
('ORD202307001', 1, 1, 9999.00),

-- ORD202307002 (键盘*2 + 显示器*1)
('ORD202307002', 2, 2, 299.50),  -- 无线机械键盘
('ORD202307002', 3, 1, 2499.00), -- 27英寸4K显示器

-- ORD202307003 (背包*1 + 数据线*3)
('ORD202307003', 6, 1, 399.00),  -- 笔记本电脑双肩包
('ORD202307003', 5, 3, 49.90),    -- Type-C数据线

-- ORD202307004 (背包*1)
('ORD202307004', 6, 1, 399.00),

-- ORD202307005 (降噪耳机*1 + 手表*1)
('ORD202307005', 7, 1, 1299.00),  -- 主动降噪耳机
('ORD202307005', 8, 1, 899.00);    -- 智能运动手表

-- ORD202307006 (iPhone)
('ORD202307006', 1, 1, 9999.00),

-- ORD202307007 (键盘*2 + 显示器*1)
('ORD202307007', 2, 2, 299.50),  -- 无线机械键盘
('ORD202307007', 3, 1, 2499.00) -- 27英寸4K显示器

4.2 多表连接与聚合查询

查询每个用户的最近一笔订单详情（包括商品信息）

 -- 采用分组的形式进行实现的！ 
SELECT 
	u.username AS '用户名',
	ot.order_id AS '订单ID',
	GROUP_CONCAT(p.name) AS '商品里列表',
	SUM(ot.quantity) AS '商品的总数量',
	SUM(ot.quantity * p.price) AS '订单详情的总价'
FROM users u
JOIN orders o ON o.user_id = u.user_id
JOIN order_items ot ON ot.order_id = o.order_id
JOIN products p ON p.product_id = ot.product_id
WHERE o.created_at = ( 		-- 最近的时间
	SELECT MAX(created_at)
    FROM orders
    WHERE user_id = u.user_id
)
GROUP BY ot.order_id,u.username;

-- 查询每个用户的最近一笔订单详情（包括商品信息）
SELECT 
	u.username AS '用户名',
	ot.order_id AS '订单ID',
	p.name AS '商品名称',
	p.price AS '商品的单价',
	ot.quantity AS '商品的数量',
	(ot.quantity * p.price) AS '小计'
FROM users u
JOIN orders o ON o.user_id = u.user_id
JOIN order_items ot ON ot.order_id = o.order_id
JOIN products p ON p.product_id = ot.product_id
WHERE o.created_at = ( 		-- 最近的时间
	SELECT MAX(created_at)
    FROM orders
    WHERE user_id = u.user_id
)

注意：上述插入的数据存在两个张三的订单，因为是同时进行插入的数据，所以created_at 时间是一致，可以将某个订单进行更改，错开时间。或添加一个新的商品订单
```
-- 修改时间 
UPDATE orders
SET created_at = '2025-04-03 12:00:00'
WHERE order_id = 'ORD202307004';
```
说明：
- 子查询获取每个用户的最近订单时间
- 四表连接关联用户、订单、订单详情、和商品信息
- 计算字段展示小计金额

统计商品各类的月销售额

（假设商品名称包含类别关键词）

--  获取completed 状态的，其他的状 没有获取。
SELECT
	YEAR(o.created_at) AS '年份',
	MONTH(o.created_at) AS '月份',
	SUM(CASE WHEN p.name LIKE '%手机%' THEN oi.quantity * oi.price ELSE 0 END) AS '手机类销售额',
	SUM(CASE WHEN p.name LIKE '%耳机%' THEN oi.quantity * oi.price ELSE 0 END) AS '耳机类销售额',
	SUM(oi.quantity * oi.price) AS '总销售额'
FROM orders o 
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed';
GROUP BY YEAR(o.created_at), MONTH(o.created_at);

条件聚合（CASE WHEN）按商品名称关键词分类统计
日期函数 提取年月日信息
过滤已完成订单 确保统计有效

4.3 子查询与CTE(公共表达式)

查找从未下单的用户
```
SELECT u.username, u.email
FROM users u
WHERE NOT EXISTS (
  SELECT 1 
  FROM orders o 
  WHERE o.user_id = u.user_id
);
```
- NOT EXISTS的作用
  - 子查询SELECT 1 FROM orders o WHERE o.user_id = u.user_id 会尝试为外层查询的每一行用户（users表中的每一行）查找对应的订单
  - 如果某个用户没有订单，子查询返回空，则NOT EXISTS的条件成立，该用户会被选中
- SELECT 1的含义
  - SELECT 1是子查询的管用写法，目的是检查是否存在复合条件的记录，而不需要实际返回数据
  - 可以替换为 SELECT * 或 SELECT o.order_id，但 SELECT 1 通常更高效，因为数据库只需判断是否存在记录，无需处理具体数据。
- 等效CTE写法
```
WITH active_order AS(
	SELECT	DISTINCT user_id FROM orders
)
SELECT u.username,u.email
FROM users u 
LEFT JOIN active_order ao ON u.user_id = ao.user_id
WHERE ao.user_id IS NULL;
```

计算商品销售额占比（窗口函数）

-- 订单表，商品表，订单详情表
-- 逻辑：商品的销售总额/ 总销售额     所有订单的总额

SELECT
	p.product_id,
	p.name AS '商品名',
	SUM(oi.price * oi.quantity) AS '商品的销售额',
	(SELECT SUM(total_amount) FROM orders WHERE status = 'completed') AS '总销售额',
	SUM(oi.price * oi.quantity) / (SELECT SUM(total_amount) FROM orders WHERE status = 'completed') AS '销售额占比'
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'completed'
GROUP BY p.name,p.product_id;

使用CTE 和窗口函数

WITH product_amount AS (
	SELECT
		p.product_name,
		SUM(oi.price * oi.quantity) AS amount,
	FROM order_items oi
	JOIN products p ON oi.product_id = p.product_id
 	GROUP BY p.product_id		
)

SELECT 
	product_name AS '产品',
	amount AS '销售额'
	ROUND(amount/SUM(amount) OVER()*100,2) AS 'x'
FROM product_amount
ORDER BY amount DESC;

4.4 窗口函数（LAG函数）

查找连续三个月消费的用户

 -- 汉字也可以作为变量去适用
-- LAG函数 获取前两个月的消费记录
-- 赛选连续三个月有消费用户
WITH user_month_order AS (
  SELECT 
    u.user_id,
    DATE_FORMAT(o.created_at, '%Y-%m') AS 年月,
    SUM(o.total_amount) AS 月消费
  FROM users u
  JOIN orders o ON u.user_id = o.user_id
  WHERE o.status = 'completed'
  GROUP BY u.user_id, DATE_FORMAT(o.created_at, '%Y-%m')
)
SELECT 
  user_id,
  年月,
  月消费,
  LAG(年月, 1) OVER (PARTITION BY user_id ORDER BY 年月) AS 上月,
  LAG(年月, 2) OVER (PARTITION BY user_id ORDER BY 年月) AS 前两月
FROM 用户月消费
HAVING 
  前两月 = DATE_FORMAT(DATE_SUB(CONCAT(年月, '-01'), INTERVAL 2 MONTH), '%Y-%m');

4.5 事务与锁机制

高并发下单场景的库存扣减（悲观锁）

-- 使用事务和SELECT ... FOR UPDATE 锁定商品
-- 库存：商品表中stock

START TRANSACTION; -- 开启事务

-- 1. 检查并锁定商品库存
SELECT stock FROM products WHERE product_id = 1 FOR UPDATE;

-- 2. 假定库存足够，插入订单和订单详情

INSERT INTO orders(order_id,user_id,total_amount,status)
VALUES ('ORD202307008', 1, 9999.00, 'pending');

INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES ('ORD202307008', 1, 1, 9999.00);

-- 3.扣减库存
UPDATE products SET stock = stock - 1 WHERE product_id = 1;

COMMIT;

关键点：

FOR UPDATE 锁定商品行，防止其他事务同时修改。
事务确保订单创建和库存扣减的原子性。

订单支付状态的更新（乐观锁）

-- 使用版本号控制并发
UPDATE orders 
SET 
  status = 'paid',
  version = version + 1  -- 假设表中有version字段
WHERE 
  order_id = 'ORD202307001' 
  AND version = 1;  -- 更新前查询得到的版本号

5.性能优化时间

5.1 分析慢查询（EXPLAIN）

-- 查询某个用户的所有订单详情
EXPLAIN 
SELECT 
  o.order_id, 
  p.name, 
  oi.quantity, 
  oi.price
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.user_id = 1;

优化建议：

确保 orders.user_id 和 order_items.order_id 有索引。
添加覆盖索引：ALTER TABLE order_items ADD INDEX idx_order_product (order_id, product_id).

5.2 适用覆盖索引优化COUNT 查询

-- 统计用户订单数（低效写法）
SELECT COUNT(*) FROM orders WHERE user_id = 1;

-- 优化方案：创建索引
ALTER TABLE orders ADD INDEX idx_user (user_id);

-- 验证是否使用索引
EXPLAIN SELECT COUNT(*) FROM orders WHERE user_id = 1;

6.错误处理与数据清洗

6.1 修复订单总金额与详情不一致的问题

 -- 检查总金额是否正确
 -- 检查总金额是否正确
SELECT 
  o.order_id,
  o.total_amount AS 订单总金额,
  SUM(oi.数量 * oi.price) AS 计算总金额,
  CASE 
    WHEN o.total_amount <> SUM(oi.数量 * oi.price) THEN '错误' 
    ELSE '正确' 
  END AS 状态
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id
HAVING 状态 = '错误';

-- 更新错误数据
UPDATE orders o
JOIN (
  SELECT order_id, SUM(数量 * price) AS correct_total
  FROM order_items
  GROUP BY order_id
) t ON o.order_id = t.order_id
SET o.total_amount = t.correct_total
WHERE o.total_amount <> t.correct_total;

UPDATE中使用JOIN ON 的好处
- 跨表更新
- 精准定位数据
- 简化复杂逻辑

6.2删除无效的数据

-- 删除已取消且超过30天的订单
DELETE o, oi
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'cancelled'
AND o.created_at < NOW() - INTERVAL 30 DAY;

快递鸟一站式物流API解决方案

电商企业物流数字化转型必备！快递鸟 API 接口，72 小时快速完成物流系统集成。全流程实战1V1指导，营造开放的API技术生态圈。

更多推荐

苹方字体跨平台解决方案：告别Windows与Mac的字体显示鸿沟

在Web开发中，我们经常面临一个令人头疼的问题：精心设计的页面在Mac上优雅精致，到了Windows设备上却因字体差异而显得平庸。今天，我们为您介绍一个专业的解决方案——PingFangSC字体包，它让苹方字体的优雅设计能够在所有平台上完美呈现。这个开源项目提供了完整的6种字重，支持ttf和woff2双格式，真正实现了跨平台字体统一。## 为什么跨平台字体一致性如此重要？🔍现代Web应用

快递鸟社区

Ascend-SACT/Mineru-Optimization后端引擎对比：Pipeline、Hybrid与VLM模式如何选择？

Ascend-SACT/Mineru-Optimization提供三种强大的后端引擎模式——Pipeline、Hybrid和VLM，帮助用户高效处理各类文档。本文将深入对比这三种模式的核心特性、性能表现和适用场景，助你快速找到最适合的解决方案。## 三大引擎模式核心特性解析 🚀### Pipeline模式：传统OCR流程的极致优化**核心架构**：采用模块化设计，包含版面分析、OCR、

快递鸟社区

如何永久保存微信聊天记录？WeChatMsg免费开源工具终极指南

你是否曾担心更换手机后，那些珍贵的微信对话会永远消失？与家人的温馨聊天、重要的工作沟通、朋友间的难忘回忆，这些数字记忆都值得被永久珍藏。**WeChatMsg**是一款完全免费的开源工具，专门用于**微信聊天记录永久保存和深度分析**，让你的每一段对话都能成为永恒的数字资产。## 🔍 你的聊天记录正在面临什么风险？微信已经成为我们日常生活中不可或缺的沟通工具，但官方并未提供完整的聊天记录