CRUD、NULL 与确定性分页

← 本站索引 ｜ 练习答案

可运行 SQL

USE mysql_course;

INSERT INTO customer(email, display_name)
VALUES ('ada@example.com', 'Ada'), ('lin@example.com', 'Lin');

INSERT INTO product(sku, name, price, stock)
VALUES ('BOOK-001', 'Database Book', 88.00, 10),
       ('MUG-001', 'Query Mug', 29.90, 30);

INSERT INTO sales_order(customer_id, status, total_amount)
SELECT customer_id, 'pending', 117.90
FROM customer WHERE email = 'ada@example.com';

SELECT order_id, customer_id, status, total_amount, created_at
FROM sales_order
WHERE status = 'pending'
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

UPDATE sales_order
SET status = 'paid', version_no = version_no + 1
WHERE order_id = 1 AND status = 'pending';

机制解释

WHERE 只保留结果为 TRUE 的行；与 NULL 比较产生 UNKNOWN，应使用 IS NULL 或 IS NOT NULL。ORDER BY 中加入唯一键才能在相同时间值下保持确定性。

OFFSET 分页仍需定位并丢弃前面的记录；键集分页把上一页末尾的排序键作为下一页边界，更适合深翻页，但不支持随意跳页，并且过滤条件与排序必须稳定。

参数化查询保护的是值。表名、列名和 ASC/DESC 通常不能作为值参数绑定，应由服务端白名单映射。

真实边界

• 受影响行数为零可能表示记录不存在、状态已变化或值未变化，应用要定义契约。
• 批量 DML 会放大锁、redo、binlog 和副本重放压力，应限制批次并可恢复。
• 软删除会影响唯一性、所有查询和容量，不能只增加 deleted_at 就算完成。

练习

1. 写出按 created_at、order_id 向后翻页的查询。
2. 验证 NOT IN 子查询包含 NULL 时的结果，再改写为 NOT EXISTS。
3. 为 paid → cancelled 更新加入乐观版本条件。

验收清单

• CRUD 脚本能重复准备和查询实验数据。
• 分页排序包含唯一结尾键，下一页无重复或遗漏。
• 写入检查受影响行数，并能区分状态冲突与记录不存在。

完成后核对答案

深度加餐：CRUD 的安全线是确定性

UPDATE/DELETE 要有可验证条件，分页要有稳定排序；否则数据会被悄悄多改或漏读。

先做一个可观察实验

START TRANSACTION;
SELECT id, order_no
FROM orders
WHERE status = 'PENDING'
ORDER BY placed_at DESC, id DESC
LIMIT 20;

UPDATE orders
SET status = 'CANCELLED'
WHERE id = 42 AND status = 'PENDING';
SELECT ROW_COUNT();
COMMIT;

预期：只有仍为 PENDING 的订单会被取消；ROW_COUNT 为 0 时调用方知道状态已被别人改变。

原理下潜与工程边界

SQL 执行顺序与书写顺序不同；WHERE、ORDER BY、LIMIT 的组合要能解释结果。offset 分页会随深度扫描，keyset 分页使用最后一条的排序键更稳定。

没有 WHERE 的 UPDATE/DELETE 是事故按钮；只按 created_at 排序可能出现重复/漏项；NULL 排序和时区转换要显式定义。

反例排错路线

开发环境开启安全更新只能拦一部分误操作；先 SELECT 同条件预览，再执行写入并检查 ROW_COUNT、warnings 和事务状态。

练习加码（仍不提供答案）

加码：把订单列表改成 keyset 分页，处理相同 placed_at、删除中间行和非法 cursor 三种情况。

验收时请补充：一份可复现的环境/数据前提、正常结果、失败结果以及可回滚或清理动作。

继续查阅：MySQL 8.4 参考手册（外部资料已收录于证据账本） · InnoDB 文档（外部资料已收录于证据账本）