WHERE 的条件归成五类：比较、范围、集合、模糊、空。
LIKE 的前缀匹配在沙箱里也用不上索引，因为 LIKE 默认不区分大小写。
深翻页越翻越慢，OFFSET 是「数过去」而不是「跳过去」。
9.2 是全书最长的一节，十三个小节。拆开看只有五类条件加三个坑。
这一篇里的 LIKE 和索引，第 10 章讲索引时会再回来一次，那时从原理上讲为什么。
一、五类条件
十三个小节听着多，归纳起来 WHERE 只干五件事。
每一类跑一条，把结果行数记下来，后面调 SQL 时心里有数。
| 类别 | 写法 | 结果 |
|---|---|---|
| 比较 | price > 100 |
2453 行 |
| 范围 | BETWEEN 10 AND 20 |
260 行 |
| 集合 | cat_id IN (1,2,3) |
1460 行 |
| 模糊 | name LIKE '图书%' |
500 行 |
| 空 | cat_id IS NULL |
135 行 |
最容易被写错的是第五行。
如果写成 WHERE cat_id = NULL，这里给 0 行，不是 135 行。
因为 = 比较遇上空值，结果是「未知」而不是「真」，未知的行一律不返回。
同一批数据，换个写法从 135 行变成 0 行，这就是三值逻辑的实际后果。
判断空只能用 IS NULL 和 IS NOT NULL，这一条没有例外。
二、LIKE 的两个通配符和三个坑
LIKE 只有两个通配符：% 代表任意多个字符，_ 代表恰好一个字符。
* 和 ? 在这里没有特殊含义，那是正则的写法，别混。
第一个坑是位置。
'图书%' 是前缀匹配，'%图书%' 两边都有通配符，'%图书' 是后缀匹配。
只有前缀匹配有可能用上索引，因为索引是按「开头」排好序的。
另外两种开头不确定，没法在索引里定位到起点，只能一行行看。
第二个坑是锚点。
正则里的 ^ 和 $ 在 LIKE 里不存在，LIKE 天生就是「整串匹配」。
所以 LIKE 'G0001' 是「整串等于 G0001」，不是「以它开头」。
要「以它开头」得写 LIKE 'G0001%'，那个 % 就是「后面随便」。
少了这个 %，结果会从几百行掉成一行，而它不报错。
第三个坑是大小写，而且它和索引绑在一起。
沙箱里 code LIKE 'G0001%' 的执行计划是 SCAN，不是 SEARCH——有索引也没用上。
原因是 LIKE 默认不区分大小写，'g0001%' 也算命中。
大小写折叠之后，命中的行在索引里不再是连续的一段，区间查找就失效了。
打开 PRAGMA case_sensitive_like，同一个查询立刻变成 SEARCH，走索引区间。
所以「前缀匹配能用索引」这句话是有前提的：比较得按字节序来，不做大小写折叠。
MySQL 没有这个限制：LIKE '前缀%' 在大小写不敏感的排序规则下照样能用索引。
它直接按排序规则算区间，不先折叠字符。
反过来就是一条实用的提醒：同一条 SQL 在 MySQL 上很快，搬到 SQLite 上可能变全表扫。
判断「用没用上索引」只能看执行计划，不能凭常识。
三、DISTINCT、ORDER BY 与深翻页
DISTINCT 和 GROUP BY 在底层是同一件事——按列分组。
两者结果一样，耗时也接近，选哪个只看可读性。
ORDER BY 要记住两件事：默认是升序，可以写多列。
多列排序是「第一列相同才看第二列」，写反了结果完全不同。
第三个坑是深翻页。LIMIT 10 OFFSET 4000 取十行，但它得先「数过」前 4000 行。
所以翻得越深越慢，这是 OFFSET 的实现方式决定的，不是优化不够。
正确做法是记住上一页最后一个 id，下一页用 WHERE id > 上次的 id 去取。
这样不管翻到第几页，代价都一样——它不需要「数过去」。
代价是不能跳到任意页，只能一页页往后翻。
这在「列表页」里根本不是问题，因为用户就是这么翻的。
四、动手 Demo：单表查询的五类条件，与三个坑
数据是 5000 个商品，cat_id 每 37 行给一个空值（一共 135 行）。
后面所有「空」相关的结论都靠这 135 行撑起来。
import sqlite3
c = sqlite3.connect(u"d037.db")
# ① 五类条件各跑一条
for sql in [u"SELECT COUNT(*) FROM tb_goods WHERE price > 100",
u"SELECT COUNT(*) FROM tb_goods WHERE price BETWEEN 10 AND 20",
u"SELECT COUNT(*) FROM tb_goods WHERE cat_id IN (1, 2, 3)",
u"SELECT COUNT(*) FROM tb_goods WHERE name LIKE '图书%'",
u"SELECT COUNT(*) FROM tb_goods WHERE cat_id IS NULL"]:
print(c.execute(sql).fetchone()[0])
# ② 前缀 LIKE 到底走不走索引
print(c.execute(u"EXPLAIN QUERY PLAN SELECT id FROM tb_goods "
u"WHERE code LIKE 'G0001%'").fetchone()[-1])
c.execute(u"PRAGMA case_sensitive_like = ON")
print(c.execute(u"EXPLAIN QUERY PLAN SELECT id FROM tb_goods "
u"WHERE code LIKE 'G0001%'").fetchone()[-1])
# ③ 深翻页与 keyset 翻页
c.execute(u"SELECT id FROM tb_goods ORDER BY id LIMIT 10 OFFSET 4000").fetchall()
c.execute(u"SELECT id FROM tb_goods WHERE id > 4000 ORDER BY id LIMIT 10").fetchall()
实跑输出：
--------------------------------------------------------------------
第三十七课：单表查询的五类条件，与三个坑
--------------------------------------------------------------------
**教材 9.2 是全书最长的一节，十一个小节。**
**拆开看只有五类条件：比较、范围、集合、模糊、空。**
**这一课把五类各跑一遍，再专挑三个真会出事的坑：`LIKE` 的位置、`DISTINCT` 的代价、深翻页。**
一、查全部与查指定列
--------------------------------------------------------------------
id code name price
-------------------------
1 G00001 数码商品1 1.7
2 G00002 家电商品2 2.4
3 G00003 服饰商品3 3.1
3 rows in set
**指定列的时候，输出顺序就按你写的顺序来，和表里的列顺序无关。**
**所以「第几列是什么」不能靠位置记，要按列名取。**
二、五类条件各跑一条
--------------------------------------------------------------------
类别 写法 结果
----------------------------------------------
比较 `WHERE price > 100` 2453 行
范围 `WHERE price BETWEEN 10 AND 20` 260 行
集合 `WHERE cat_id IN (1, 2, 3)` 1460 行
模糊 `WHERE name LIKE '图书%'` 500 行
空 `WHERE cat_id IS NULL` 135 行
**五行合起来覆盖了 `WHERE` 的绝大多数用法。**
**第五行那个「空」要特别注意：它数出来是 135 行。**
**如果写成 `WHERE cat_id = NULL`，这里会给 0 行。**
**同一批数据，换个写法从「135 行」变成「0 行」，这就是三值逻辑坑的实际后果。**
三、LIKE 的三个坑
--------------------------------------------------------------------
**`LIKE` 只有两个通配符：`%` 代表任意多个字符，`_` 代表恰好一个字符。**
**别的都不是通配符，`*` 和 `?` 在这里没有特殊含义。**
写法 命中行数 类型
-----------------------------------------
`LIKE '图书%'` 500 前缀匹配
`LIKE '%图书%'` 500 两边都有通配符
`LIKE '%图书'` 0 后缀匹配
`LIKE 'G0001_'` 10 `_` 占一位
**四行里只有第一行有可能用上索引，因为只有它「开头是确定的」。**
**后三行开头是通配符，索引是按前缀排序的，没法从中间找。**
查询（默认设置） 执行计划
---------------------------------------------------------------
`code LIKE 'G0001%'` SCAN tb_goods USING COVERING INDEX i_code
`code LIKE '%0001'` SCAN tb_goods USING COVERING INDEX i_code
**两行都是 `SCAN`，连前缀匹配那一行也没走成 `SEARCH`。**
**这和「前缀能用索引」的常识相反，得说清为什么。**
**因为 SQLite 的 `LIKE` 默认不区分大小写，`'g0001%'` 也算命中。**
**大小写折叠之后，命中的行在索引里不再是连续的一段，区间查找就失效了。**
查询（打开区分大小写） 执行计划
---------------------------------------------------------------------------------------
`code LIKE 'G0001%'` SEARCH tb_goods USING COVERING INDEX i_code (code>? AND code<?)
**同一个查询，改一个开关就变成 `SEARCH`，走的是索引区间。**
**所以「前缀匹配能用索引」有前提：比较得按字节序来，不做大小写折叠。**
**换成区间写法 `code >= 'G0001' AND code < 'G0002'`，默认设置下就走索引：**
**`SEARCH tb_goods USING COVERING INDEX i_code (code>? AND code<?)`。**
**MySQL 在这里比 SQLite 宽松：`LIKE '前缀%'` 在大小写不敏感的排序规则下也能用索引。**
**它直接按排序规则算区间，不先折叠字符，所以没这个限制。**
**反过来就是一条实用的提醒：同一条 SQL 在 MySQL 上很快，搬到 SQLite 上可能变全表扫。**
**判断「用没用上索引」只能看执行计划，不能凭常识。**
四、DISTINCT 与 ORDER BY
--------------------------------------------------------------------
写法 结果行数 耗时
---------------------------------------------
`SELECT DISTINCT cat_id` 11 747 微秒
`GROUP BY cat_id` 11 2.06 毫秒
**两者结果一样，耗时这一格 `DISTINCT` 反而快了一倍多。**
**但别把这个方向当规律——它们在底层就是同一件事「按列分组」，差别只是优化器怎么摆。**
**所以「去重」和「分组」是一件事的两种写法，选哪个只看可读性。**
**`ORDER BY` 要记住两件事：默认是升序（`ASC`），可以写多列。**
**多列排序是「第一列相同才看第二列」，顺序反了结果就完全不同。**
id cat_id price
-----------------
1998 NULL 199.6
3996 NULL 198.2
851 NULL 196.7
3 rows in set
五、深翻页：LIMIT 的 offset 越大越慢
--------------------------------------------------------------------
**`LIMIT 10 OFFSET 0` 和 `LIMIT 10 OFFSET 4000` 取的都是十行，代价差很多。**
**因为「跳过前 4000 行」这件事，数据库是真的一行一行数过去的。**
写法 耗时
--------------------------------
`LIMIT 10 OFFSET 0` 56 微秒
`LIMIT 10 OFFSET 2000` 102 微秒
`LIMIT 10 OFFSET 4000` 145 微秒
**三行数据量一样，耗时随 offset 增长。**
**这张表只有 5000 行，差别还不算大；上了百万行，第 90 万页会慢到没法用。**
**正确做法是「记住上一页最后一个 id」，下一页用 `WHERE id > 上次的 id` 来取。**
**这样不管翻到第几页，代价都一样——因为它不需要「数过去」。**
写法 耗时 翻到第几页都一样吗
-----------------------------------------------
`OFFSET 4000` 145 微秒 不，越深越慢
`WHERE id > 4000` 55 微秒 是，代价恒定
**第二行的写法不需要「跳过」，它直接从 id 大于 4000 的地方开始取。**
**所以翻第 1 页和第 900 页一样快。**
**它的代价是不能「跳到任意页」，只能一页一页往后翻。**
**这个限制在「列表页」里完全不是问题，因为用户就是这么翻的。**
执行计划那两行是这一课的重点。
默认设置下两行都是 SCAN ... USING COVERING INDEX i_code——连前缀匹配都没走成 SEARCH。
「COVERING INDEX」的意思是「只读索引就够，不用回表」，它确实用上了索引，
但用的是「整条索引扫一遍」，不是「区间定位」。
打开区分大小写之后才变成 SEARCH ... (code>? AND code<?)，那才是真正的区间查找。
深翻页那三档是 58、105、171 微秒，随 offset 单调上升。
表只有 5000 行，所以差距只有三倍；上了百万行，这个差距会变成几百倍。
keyset 那一句是 57 微秒，和 OFFSET 0 差不多——它压根不需要数。
这就是「翻第 1 页和翻第 900 页一样快」的意思。
30 秒实验
- 把
LIKE '图书%'改成LIKE '%图书'，行数会变成 0，想想为什么是 0 而不是 500。 - 把深翻页的
OFFSET从 4000 改成 4900，看耗时继续涨到什么程度。 - 把
WHERE cat_id IS NULL换成WHERE cat_id = NULL，确认它给 0 行。
常见误区
- 把
LIKE 'G0001'当成「以 G0001 开头」，忘了那个%，结果从十行掉成一行还不报错。 - 以为「有索引就一定走索引」，不看执行计划，结果全表扫了还以为很快。
- 用
OFFSET做深翻页，前几页很快，数据一多就慢到没法用。
自测三问（点开对答案）
1. 判断空值为什么要用 `IS NULL`，不能用 `= NULL`？ —— 因为 `=` 遇上空值算出来是「未知」，不是「真」。 `WHERE` 只保留结果为真的行，所以 `= NULL` 一行都留不下。`IS NULL` 是专门判断空值的运算符。 2. `LIKE '前缀%'` 在沙箱里为什么没走成 `SEARCH`？ —— 因为 `LIKE` 默认不区分大小写，命中的行在索引里不是连续的一段，区间查找失效。 打开 `case_sensitive_like` 之后才变成 `SEARCH`。 3. 深翻页为什么越翻越慢，怎么改？ —— `OFFSET n` 的实现是「先数过 n 行再取」，n 越大数得越多。 改成 keyset 翻页——记住上一页最后一个 id，用 `WHERE id > ?` 取下一页。条件筛完了，下一篇 9.3 讲把行变成数的五个聚合函数，和它们的两个分母陷阱。
- ☐ （本篇完，下一篇 9.3 聚合函数查询）