MySQL
SELECT、表达式、NULL 与分页
写出投影、过滤、排序、NULL 处理和稳定分页语义明确的 SELECT，并避免隐式转换与不确定顺序，并在可重复的 StudyStore 实验中验证结果、失败边界与恢复方式。
发布于 2026年7月23日
SELECT、表达式、NULL 与分页
查询正确性的第一步是精确定义结果集合，而不是把 SELECT * 调到“看起来对”。列、过滤条件、NULL、排序和分页共同构成 API 契约。没有 ORDER BY 的结果没有稳定顺序，带 OFFSET 的大分页也不等于可扩展。
一、学习目标
- 明确投影、别名与表达式类型
- 正确使用 WHERE、三值逻辑与 NULL
- 避免隐式类型和字符集转换
- 建立确定性 ORDER BY
- 比较 OFFSET 与游标分页
二、投影与表达式
只选择调用方需要的列：
SELECT id, sku, name, price,
price * 1.13 AS price_with_tax
FROM products
WHERE active = TRUE;
SELECT * 会把 schema 演进变成结果契约变化，还可能阻止覆盖索引。表达式要考虑 DECIMAL 精度、NULL 传播和别名。业务金额舍入规则应显式使用 ROUND 并说明位数，而不是依赖客户端显示。
三、WHERE 与三值逻辑
比较 NULL 得到 UNKNOWN：
SELECT *
FROM orders
WHERE cancelled_at IS NULL;
NOT IN 子查询若包含 NULL，可能让所有比较变成 UNKNOWN。可使用 NOT EXISTS 并建立关联条件。COALESCE 适合输出默认值，但在过滤列上包函数可能阻止普通索引范围扫描，也可能混淆“缺失”和“默认值”。
四、隐式转换与范围
把字符串列与数字比较会触发转换并产生意外匹配：
-- 避免：sku 是 VARCHAR
WHERE sku = 123;
参数类型应与列一致。日期范围使用半开区间：
WHERE created_at >= '2026-07-01 00:00:00'
AND created_at < '2026-08-01 00:00:00'
不要对列调用 DATE(created_at) 后再过滤整月；半开区间既处理微秒边界，也更利于索引。
五、确定性排序
排序字段不唯一时追加唯一键：
ORDER BY created_at DESC, id DESC
否则相同时间行之间顺序未定义，翻页时可能重复或遗漏。排序规则影响文本顺序，NULL 在升降序中的位置也要纳入契约。用户可选排序列必须从白名单映射，不能把原始输入拼进 SQL。
六、分页策略
OFFSET 简单但越往后需要扫描并丢弃越多行，且并发插入会移动页边界。游标分页使用最后一行的排序键：
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;
游标需要与 ORDER BY 完全一致的唯一排序组合，并把方向、过滤条件与游标编码为稳定契约。后台导出更适合按主键批次扫描，不应模拟用户页码。
七、从知识点到工程契约
本篇 SQL 最终要运行在长期存在的 schema、连接和事务中，而不是只在一个临时查询窗口里得到一次正确结果。先写清输入表与行数、会话设置、事务边界、允许的锁、期望结果和失败后的状态，再决定使用约束、查询、索引、存储对象还是运维命令。数据库行为同时受到版本、隔离级别、字符集、统计信息和并发会话影响，示例必须把这些前提显式化。
可以用以下顺序把知识点落到 StudyStore：
- 在全新数据库按迁移顺序建立最小 schema，保存
SHOW CREATE TABLE与关键会话变量。 - 加入一个与“没有 ORDER BY 却依赖当前返回顺序”相关的反例，记录错误码、SQLSTATE、事务是否仍可用以及数据是否改变。
- 为正常、空集、边界值、重复值和并发冲突准备确定性种子，不依赖手工残留数据。
- 对读查询检查结果集和
EXPLAIN ANALYZE；对写操作同时检查受影响行数、约束、提交与回滚。 - 最后才讨论性能优化。索引和参数调整必须有执行计划、等待事件或容量数据支持，并保留变更前基线。
审查数据库设计时至少回答四个问题：谁能写，哪条约束保护不变量，事务在哪一层结束，失败后如何恢复。MySQL 能保证声明范围内的事务和持久性，却不会替应用补上缺失约束、幂等键、备份演练或最小权限。只要这些问题没有明确答案，就不要把一次成功执行当成生产方案。
本篇最重要的能力是“明确投影、别名与表达式类型”。能由主键、唯一键、外键、CHECK 或数据类型表达的规则，应优先落到数据库；需要跨聚合或外部系统判断的规则，再由应用和事务协调。不要用注释或约定替代可执行约束。
八、验证策略与复盘
验证分为 schema、数据、并发和恢复四层。schema 层检查定义与会话基线；数据层断言结果和约束；并发层至少用两个独立连接观察锁与隔离；恢复层在新实例重放迁移、备份与恢复。只看客户端显示“Query OK”不能证明结果正确，更不能证明在另一组数据和并发时仍正确。
建议保存下面的实验记录：
| 项目 | 需要记录的证据 |
|---|---|
| 版本 | SELECT VERSION() 与镜像标签 |
| 会话 | sql_mode、time_zone、事务隔离与字符集 |
| 输入 | schema 版本、种子行数和参数值 |
| 输出 | 结果集、受影响行数、警告、错误码与 SQLSTATE |
| 性能 | 执行计划、实际行数、耗时与等待 |
| 恢复 | COMMIT/ROLLBACK 结果、备份校验和与恢复行数 |
每组脚本都应能在空数据库从头执行，并通过显式断言退出非零。涉及权限时分别用管理员和最小权限账户连接；涉及锁时设置有限等待，避免测试永久挂起；涉及复制时等待 GTID 状态而不是固定 sleep。清理只删除本次创建的临时容器、网络和数据库。
本篇可以用以下目标做验收：正确使用 WHERE、三值逻辑与 NULL；避免隐式类型和字符集转换；建立确定性 ORDER BY。把每个目标转换为一条可重复 SQL、一个预期错误或一项恢复检查。若优化后结果正确但计划退化，应把执行计划也纳入回归证据。
发布前再从三个方向反向审查：把数据量放大两个数量级，判断扫描、锁和日志是否仍可控；让两个会话以最不利顺序并发，判断约束和事务是否仍保护不变量；让执行在任意一步失败，判断备份、回滚或幂等键能否恢复。只在十几行数据和单连接中成功的 SQL 仍只是功能草稿。
最后把脚本交给一个只知道镜像版本和入口命令的干净环境执行。脚本不得依赖图形客户端自动设置、个人默认数据库或先前会话变量；所有对象名、字符集、事务和预期错误都应明确。对仅用于说明、不可直接执行的片段要标注上下文，避免把省略条件的示意 SQL 当作完整迁移。
还要保存一份“结果为什么可信”的说明：约束证明哪些坏数据无法进入，事务证明哪些变化共同提交，执行计划证明访问了哪些行，权限测试证明哪些身份无法越界，恢复演练证明故障后能重新得到可用状态。这五类证据缺一时，都应在结论旁写明限制。数据库实验的价值不只是得到答案，而是让另一位操作者在不同机器、不同时间仍能重现同一判断。
若结论依赖当前数据分布或配置，应把适用范围写在 SQL 旁，并安排数据增长或版本升级后的复测条件；不要把一次测量永久固化为规则。
九、StudyStore 实验
为商品、订单列表分别实现确定性排序的 OFFSET 和游标分页。插入具有相同时间戳的多行并在翻页间新增订单，记录两种方案的重复、遗漏和执行计划；所有参数按列类型绑定。
完成本节后，不要只保存代码或 SQL。请同时保存执行命令、关键输出和失败案例；学习笔记真正有价值的部分，是能够说明输入、状态变化、输出以及失败后的恢复方式。
十、常见错误
- 没有 ORDER BY 却依赖当前返回顺序
- 用
= NULL或含 NULL 的 NOT IN - 对索引列套函数后再过滤整个范围
- 把字符串列与数字参数隐式比较
- 仅按非唯一时间排序并做游标分页
十一、练习与自测
- 把一个 SELECT * 改成稳定投影并说明兼容收益。
- 构造 NOT IN 子查询含 NULL 的反例，再用 NOT EXISTS 修复。
- 为倒序订单列表设计并编码复合游标。
- 比较第 10000 页 OFFSET 与主键游标的实际扫描行数。
自测时应在干净的临时目录或临时数据库中重新执行，而不是依赖上一节遗留的状态。如果结果与预期不同，先记录实际输出，再缩小问题范围。
十二、官方资料
版本行为与二手文章不一致时，以本系列固定版本的官方文档、命令输出和可重复测试结果为准。