楠风测开 · 测试工程师复习日记 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) ) ENGINEInnoDB; 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) ) ENGINEInnoDB; 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) ) ENGINEInnoDB; 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) ) ENGINEInnoDB; 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 statusSUCCESS) 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;订单状态订单金额明细合计成功支付1001PAID199.80199.80199.801002CREATED59.9059.900.001003CANCELLED99.9099.900.001004PAID159.80159.80159.80COALESCE在这个展示报表里把“没有成功支付”显示为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。今天先把正确性守住。下一篇Day16MySQL 索引 AI 索引建议用更大的实验表看联合索引、执行计划和写入代价。你在查数据库时更常遇到“查得慢”还是“查得快但结果不对”欢迎把脱敏后的场景写出来。关注楠风测开我们继续把每个结论变成能核对的结果。