楠风测开 · 测试工程师复习日记 Day15
用一套订单、支付和库存样例，把查询、关联、金额核对与事务验证串起来。
前言：SQL跑通之后，还差一次结果验收
朋友们好，我是楠风。
上一阶段聊完Linux监控，我们开始补上测试工程师经常接触的另一块能力：MySQL。
接口返回“支付成功”，页面也显示“已支付”，测试就结束了吗？还要看订单金额是否正确、失败支付有没有被算进收入、明细是否被重复汇总，以及失败时库存能不能回到原来的状态。
今天从SQL基础讲起，再让AI辅助检查和改写查询。验收顺序是先确认业务含义与结果，再比较执行代价。如果一条SQL把159.80元算成319.60元，即使执行得很快，也不能通过测试。
本文以MySQL 8.4、InnoDB为语法与事务参照，使用合成教学数据。下面的金额是根据给定数据推导的预期值；本文未提供真实MySQL实例的性能实测，执行计划与耗时需要在你的实验环境记录。
一、先认识四张表，各自回答什么问题
| 表 | 一行代表什么 | 主要用途 |
|---|---|---|
| orders | 一张订单 | 用户、状态、订单金额和时间 |
| order_items | 一条订单商品明细 | 商品、数量、下单时单价 |
| payments | 一次支付记录 | 成功或失败、支付金额和流水号 |
| products | 一个商品的当前信息 | 当前价格和库存 |
其中，一张订单可以有多条明细，也可能经历多次支付。我们特意保留一次支付失败、一个没有支付记录的订单，以及一个拆分支付的订单，用来检查边界。
订单明细保留unit_price，因为商品当前售价可能变化。核对历史订单时，应使用下单时的单价，而不是直接拿products.price重算。
本例金额用DECIMAL(10,2)：总共10位数字，其中2位小数。它属于精确数值类型，适合这里以分为精度的金额计算；真实项目还需要约定币种、折扣、税费及舍入规则。MySQL定点数类型说明
初始化实验数据
下面的SQL只在独立实验库首次执行。它不会删除已有表；如果同名表已经存在，应先核对环境，不要为了“跑通”就清空已有数据。
-- MySQL 8.4 / InnoDB 教学样例；在独立实验环境首次执行。 -- 不覆盖已有表。如果同名实验表已存在，请先核对，勿反复执行整份初始化。 CREATE DATABASE IF NOT EXISTS nanfeng_mysql_lab CHARACTER SET utf8mb4; USE nanfeng_mysql_lab; CREATE TABLE products ( sku VARCHAR(20) PRIMARY KEY, name VARCHAR(50) NOT NULL, price DECIMAL(10,2) NOT NULL, stock INT NOT NULL, CHECK (price >= 0), CHECK (stock >= 0) ) ENGINE=InnoDB; CREATE TABLE orders ( id BIGINT PRIMARY KEY, order_no VARCHAR(32) NOT NULL UNIQUE, user_id BIGINT NOT NULL, status VARCHAR(16) NOT NULL, total_amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, paid_at DATETIME NULL, CHECK (total_amount >= 0) ) ENGINE=InnoDB; CREATE TABLE order_items ( id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, sku VARCHAR(20) NOT NULL, quantity INT NOT NULL, unit_price DECIMAL(10,2) NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (sku) REFERENCES products(sku), CHECK (quantity > 0), CHECK (unit_price >= 0) ) ENGINE=InnoDB; CREATE TABLE payments ( id BIGINT PRIMARY KEY, payment_no VARCHAR(32) NOT NULL UNIQUE, order_id BIGINT NOT NULL, status VARCHAR(16) NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(id), CHECK (amount >= 0) ) ENGINE=InnoDB; START TRANSACTION; INSERT INTO products VALUES ('KEYBOARD','键盘',99.90,10), ('MOUSE','鼠标',59.90,20); INSERT INTO orders VALUES (1001,'NF1001',101,'PAID',199.80,'2026-10-01 09:00:00','2026-10-01 09:05:00'), (1002,'NF1002',101,'CREATED',59.90,'2026-10-01 11:00:00',NULL), (1003,'NF1003',102,'CANCELLED',99.90,'2026-10-02 10:00:00',NULL), (1004,'NF1004',103,'PAID',159.80,'2026-10-03 14:00:00','2026-10-03 14:02:00'); INSERT INTO order_items VALUES (1,1001,'KEYBOARD',2,99.90), (2,1002,'MOUSE',1,59.90), (3,1003,'KEYBOARD',1,99.90), (4,1004,'KEYBOARD',1,99.90), (5,1004,'MOUSE',1,59.90); INSERT INTO payments VALUES (5001,'P1001-FAIL',1001,'FAILED',199.80,'2026-10-01 09:04:00'), (5002,'P1001-OK',1001,'SUCCESS',199.80,'2026-10-01 09:05:00'), (5003,'P1003-FAIL',1003,'FAILED',99.90,'2026-10-02 10:01:00'), (5004,'P1004-A',1004,'SUCCESS',99.90,'2026-10-03 14:01:00'), (5005,'P1004-B',1004,'SUCCESS',59.90,'2026-10-03 14:02:00'); COMMIT; -- 样例应为4张订单、5条明细、5条支付记录、成功支付总额359.60。 SELECT (SELECT COUNT(*) FROM orders) AS orders_count, (SELECT COUNT(*) FROM order_items) AS items_count, (SELECT COUNT(*) FROM payments) AS payments_count, (SELECT SUM(amount) FROM payments WHERE status='SUCCESS') AS paid_total;初始化后的核对值应为：4张订单、5条明细、5条支付记录，成功支付总额359.60。products.stock只是当前库存快照，不代表这四张历史订单曾经怎样扣减库存。
二、基础查询：先把筛选条件写完整
1. 查询某个用户一天内创建的订单
SELECT id, order_no, status, total_amount, created_at FROM orders WHERE user_id = 101 AND created_at >= '2026-10-01 00:00:00' AND created_at < '2026-10-02 00:00:00' ORDER BY created_at, id;这组数据应返回1001和1002，金额分别是199.80、59.90。我们采用左闭右开的时间范围：包含当天零点，不包含次日零点。ORDER BY中加上唯一的id，让同一时间的记录也有明确顺序。
这里的created_at是DATETIME，样例约定保存业务当地时间。若实际项目使用UTC或TIMESTAMP，先确认存储与查询时区，不能只复制日期边界。
AI若把条件改成DATE(created_at) = '2026-10-01'，要分别检查两件事：业务范围是否一致，现有索引访问是否发生变化。今天先验结果，Day16再展开执行计划。
2. NULL不能用等号判断
SELECT id, status FROM orders WHERE paid_at IS NULL ORDER BY id;预期为1002和1003。它只回答“没有支付完成时间的订单有哪些”，不自动等于“用户仍然可以支付”：1003已经取消。若要找待支付订单，还需要status = 'CREATED'等业务条件。
NULL表示缺失或未知，用IS NULL判断；paid_at = NULL不会得到想要的匹配结果。MySQL的NULL处理
三、关联查询：最容易漏掉没有支付的订单
需求是“列出全部订单，并统计各自成功支付的次数”。可以这样写：
SELECT o.id, o.status, COUNT(p.id) AS success_count FROM orders AS o LEFT JOIN payments AS p ON p.order_id = o.id AND p.status = 'SUCCESS' GROUP BY o.id, o.status ORDER BY o.id;四张订单的success_count应依次为1、0、0、2。
这里有两个细节：
- 成功支付条件放在
ON里，使没有成功支付的订单仍然保留。如果改成WHERE p.status = 'SUCCESS'，这些订单就会被过滤掉。 - 用
COUNT(p.id)统计实际匹配到的支付记录；如果改为COUNT(*)，外连接保留的空匹配行也会被计数。
聚合函数对NULL的处理不同，尤其要分清COUNT(*)和COUNT(列)。这里的区别来自统计对象，不是写法偏好。MySQL聚合函数说明
四、实战：为什么159.80会变成319.60
订单1004有两条明细：键盘99.90、鼠标59.90；也有两条成功支付：99.90和59.90。订单金额、商品合计、支付合计都应为159.80。
下面是一个故意保留的错误示例：
-- 反例：两个一对多关系直接连接，金额会重复。 SELECT o.id, SUM(i.quantity * i.unit_price) AS item_total, SUM(p.amount) AS paid_total FROM orders AS o JOIN order_items AS i ON i.order_id = o.id JOIN payments AS p ON p.order_id = o.id AND p.status = 'SUCCESS' WHERE o.id = 1004 GROUP BY o.id;两条明细分别匹配两条支付，形成四行。每条明细和每条支付都出现两次，两个合计都会变成319.60。
不要急着加DISTINCT。SUM(DISTINCT p.amount)去重的是金额值：如果两笔合法支付恰好都是79.90，它会漏掉其中一笔。重复金额不等于重复交易。
正确做法是先把两个子表分别汇总成“每张订单一行”，再关联：
WITH item_sum AS ( SELECT order_id, SUM(quantity * unit_price) AS item_total FROM order_items GROUP BY order_id ), paid_sum AS ( SELECT order_id, SUM(amount) AS paid_total FROM payments WHERE status = 'SUCCESS' GROUP BY order_id ) SELECT o.id, o.status, o.total_amount, COALESCE(i.item_total, 0.00) AS item_total, COALESCE(p.paid_total, 0.00) AS paid_total FROM orders AS o LEFT JOIN item_sum AS i ON i.order_id = o.id LEFT JOIN paid_sum AS p ON p.order_id = o.id ORDER BY o.id;| 订单 | 状态 | 订单金额 | 明细合计 | 成功支付 |
|---|---|---|---|---|
| 1001 | PAID | 199.80 | 199.80 | 199.80 |
| 1002 | CREATED | 59.90 | 59.90 | 0.00 |
| 1003 | CANCELLED | 99.90 | 99.90 | 0.00 |
| 1004 | PAID | 159.80 | 159.80 | 159.80 |
COALESCE在这个展示报表里把“没有成功支付”显示为0。对缺失明细等异常，不能只看金额是否为零，还要单独检查关系是否存在。业务允许零元订单时，“没有明细”和“合法零元”尤其不能混为一谈。
同时，只有本例的已支付订单才要求“成功支付合计等于订单金额”。取消、退款、部分支付等状态应另定规则，不能对所有状态套同一个等式。
五、事务：让库存修改与订单创建一起回滚
先在同一连接确认键盘库存是10、订单9901不存在，再运行下面这段演练。它只使用本例固定的初始数据，结尾主动回滚。
START TRANSACTION; UPDATE products SET stock = stock - 2 WHERE sku = 'KEYBOARD' AND stock >= 2; SELECT ROW_COUNT() AS changed_rows; -- 初始样例应为1 INSERT INTO orders VALUES (9901,'LAB9901',101,'CREATED',199.80, '2026-10-04 09:00:00',NULL); INSERT INTO order_items VALUES (9901,9901,'KEYBOARD',2,99.90); SELECT stock FROM products WHERE sku = 'KEYBOARD'; -- 事务内应为8 SELECT COUNT(*) FROM orders WHERE id = 9901; -- 应为1 ROLLBACK; SELECT stock FROM products WHERE sku = 'KEYBOARD'; -- 应恢复为10 SELECT COUNT(*) FROM orders WHERE id = 9901; -- 应为0 SELECT COUNT(*) FROM order_items WHERE order_id = 9901; -- 应为0真实业务代码必须读取受影响行数：为0时停止创建订单，回滚并返回库存不足；任意后续语句异常，也要明确回滚，不要继续提交。上面的固定数据演练不包含应用程序的条件分支，不能直接当作生产下单实现。
再做一次成功路径时，可以将这一轮最后的ROLLBACK改成COMMIT，但必须使用新的实验订单号，并记录修改后的库存作为新基线。不要把已提交轮次与回滚轮次混着比较。
COMMIT提交当前事务，ROLLBACK撤销当前事务尚未提交的事务性修改。DDL等语句可能隐式提交，建表和建索引不应混入这段回滚演练。事务控制说明 · 隐式提交说明
这个实验只覆盖单库数据。已经发往第三方的支付请求、消息或邮件，不会因为数据库回滚自动撤回。跨服务一致性需要另外设计和验证。
六、AI SQL优化：把“验收条件”一并交给AI
下面是一份可直接改用的提问模板：
环境：MySQL 8.4 / InnoDB。 需求：每张订单输出一行，包含订单金额、明细合计、成功支付合计。 没有成功支付的订单也要保留；合法的同额支付不能被去重。 我将提供脱敏后的四张表DDL、样例数据和当前SQL。 请依次输出： 1. 用一句话说明当前SQL的结果粒度。 2. 指出NULL、连接重复、状态过滤和时间边界风险。 3. 给出保持业务语义的候选SQL，逐项解释变化。 4. 写出每个订单的预期结果与反例。 5. 列出需要实测的执行计划问题，不编造耗时或优化比例。 如果缺少信息，请明确指出，不能补造表结构或业务规则。AI可以提出“先聚合再关联”“明确筛选列”“把时间范围写完整”等建议，但每个建议都需要对应一项验证。
查询改写前后，先比较主键集合、每行金额、空值、重复次数和排序。对于本来就有缺陷的SQL，应对照业务预期修复；不能要求新SQL继续复现旧错误。
通过正确性验收后，再用EXPLAIN查看访问路径。EXPLAIN ANALYZE会实际执行查询，应在受控数据与负载下使用；优化器信息的含义和测量方法放到后两篇继续展开。EXPLAIN说明
七、验收清单：今天至少留下这六项结果
- **数据基线：**4张订单、5条明细、5条支付，成功金额359.60。
- **时间与状态：**指定用户、指定日期得到明确的订单ID；取消订单不混入待支付列表。
- **空值：**没有支付记录的订单仍在报表中，成功支付次数为0。
- **关联：**1004的正确合计为159.80，能够解释错误SQL为什么变成319.60。
- **事务：**回滚后库存恢复，临时订单与明细同时消失；库存不足不会继续创建订单。
- **改写：**AI建议有对应反例和逐行比较记录，性能结论只填实际测量结果。
常见报错也要按原因处理：表已存在先检查初始化是否执行过；外键错误先查父表与写入顺序；事务回滚没效果先查连接、存储引擎及是否已提交，不要反复重跑脚本掩盖问题。
写在最后
测试工程师学习MySQL，第一份有价值的成果可以很小：几张表、一组能揭露错误的数据，以及能解释每一行结果的SQL。
今天先把正确性守住。下一篇Day16：MySQL 索引 + AI 索引建议，用更大的实验表看联合索引、执行计划和写入代价。
你在查数据库时，更常遇到“查得慢”，还是“查得快但结果不对”？欢迎把脱敏后的场景写出来。关注楠风测开，我们继续把每个结论变成能核对的结果。
