第8章用通配符进行过滤 · 波仔的 AI 编程实战课

📄 一句话概括

精确匹配不够用的时候，用 LIKE 加通配符：% 代表任意多个字符，_ 代表正好一个字符。 Demo 在 13 个产品上量出五种匹配：'Jet%' 命中 1 个、'%anvil%' 命中 3 个、'S%' 命中 2 个、 '_ ton anvil' 命中 2 个（.5 ton anvil 被挡在外面）、'__ ton%' 命中 1 个。 最后一句最有说服力：两个符号的差别就一条，一个管「多少都行」，一个管「正好一个」。

📝 本章和前面几章的关系

上一章的条件都是精确的：等于 1003、价格低于 10。 这一章处理「差不多」的需求——名字里带 anvil、以 S 开头、第二个字符是 t。 通配符好用，但它是性能杀手。 这一章的最后一节专门讲代价， 因为「随手加个 %」是新手最常见的慢性病。

一、LIKE 与百分号：% 代表任意多个字符

WHERE prod_name LIKE 'Jet%'：以 Jet 开头，后面随便。 通配符能匹配 0 个字符、1 个字符，也能匹配一整句话。所以 'Jet%' 连「Jet」这个名字本身也能匹配。 LIKE '%anvil%'：中间含 anvil，两头随便。 Demo 里命中 3 个铁砧——.5 ton anvil、1 ton anvil、2 ton anvil。 LIKE 'S%'：以 S 开头，命中 Safe 与 Sling 两个。 同一个符号放在不同位置，含义完全不同：'Jet%' 是前缀、'%Jet' 是后缀、'%Jet%' 是包含。 三种写法的性能也完全不同（最后一节讲）。

二、下划线：_ 正好代表一个字符

LIKE '_ ton anvil'：下划线顶一个字符。所以「1 ton anvil」「2 ton anvil」都中，而 .5 ton anvil 不中——它点号后面还有 5，是三个字符，位置对不上。 把下划线换成百分号就三个全中：LIKE '% ton anvil' 给 3 行。 这一对照就是 % 与 _ 的全部差别：一个不在乎多少个，一个只认一个。 两个下划线就是两个字符：LIKE '__ ton%' 命中 .5 ton anvil，因为点号和一个字符恰好两位，后面跟空格 ton。注意 1 ton anvil 反而不中——它开头只有一个字符。 下划线在中文里要小心：一个汉字算一个字符，所以 LIKE '_ 公司' 能匹配「某公司」但不能匹配「某某公司」。字符数 vs 字节数是两件事。

三、大小写：LIKE 默认不区分

Demo 里 LIKE 'jet%' 和 LIKE 'Jet%' 给出一模一样的结果。 MySQL 默认的校对规则（collation）不区分大小写，SQLite 的 LIKE 也默认不区分 ASCII 大小写。 这是「默认」，不是「必然」。 要区分就得显式声明： MySQL 里可以写 WHERE prod_name LIKE BINARY 'jet%' 或者用区分大小写的校对规则； SQLite 里可以 PRAGMA case_sensitive_like = ON。 第 27 章专门讲字符集与校对，这里先记住「默认不区分」。 这条差异会坑迁移：同一个查询在 MySQL 上返回 3 行，搬到 PostgreSQL 上可能只返回 0 行——因为后者默认区分大小写。

四、通配符的代价：为什么它慢

LIKE 用不上普通索引——这是它慢的根本原因。 索引是按「排好序的前缀」找的，而 %anvil% 这种「中间任意位置都要看」的条件，索引帮不上忙，只能逐行把字符串拆出来比对。 有一个例外：前缀固定的 'Jet%' 在 MySQL 里是能用索引的（最左前缀匹配）。所以 'Jet%' 快、'%Jet' 慢、'%Jet%' 最慢。 这一个字母的差别，在百万行的表上就是几百毫秒和几秒的差别。 三条使用建议，按重要排： 一是别无脑加 %——能定前缀就别两头都加； 二是别把它当正则用——复杂匹配交给第 9 章，正则虽慢但至少写得清； 三是记住位置决定快慢——两头都加 % 是最慢的一种。 还有一个隐藏的坑：如果列里存的是数字（比如价格），LIKE '5%' 会先做隐式类型转换， 索引直接失效。该用比较符的地方别用 LIKE。

五、小结

教材小结：两个通配符各管一段、LIKE 不区分大小写、通配符有代价。本课补三条：一是下划线数得清，一个汉字也算一个；二是前缀固定能用索引，中间模糊一定不能； 三是别把 LIKE 用在数值列上，隐式转换会让索引失效。

六、动手 Demo：五种匹配，把 % 和 _ 的差别摆出来

Demo 五段：前缀匹配、包含匹配、单字母前缀、下划线精确一位、下划线加百分号混用。 最有说服力的是第四段和第五段的对照——同一个位置，_ 只吃一个字符，% 吃多少都行。第六段顺便验证大小写：'jet%' 与 'Jet%' 结果完全相同。

import sqlite3
con = sqlite3.connect(":memory:")

con.execute("SELECT prod_name FROM products WHERE prod_name LIKE 'Jet%'")     # 前缀
con.execute("SELECT prod_name FROM products WHERE prod_name LIKE '%anvil%'")  # 含
con.execute("SELECT prod_name FROM products WHERE prod_name LIKE 'S%'")
con.execute("SELECT prod_name FROM products WHERE prod_name LIKE '_ ton anvil'")  # 一个字符
con.execute("SELECT prod_name FROM products WHERE prod_name LIKE '__ ton%'")       # 混用
con.execute("SELECT prod_name FROM products WHERE prod_name LIKE 'jet%'")      # 大小写

实跑输出：

--------------------------------------------------------------------
第八课：用通配符进行过滤
--------------------------------------------------------------------

  【一】LIKE 与百分号：% 代表任意个字符
  SQL> SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'Jet%'
  prod_id     prod_name
  -------------------------
  JP2000      JetPack 2000
  （1 行）
  SQL> SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%'
  prod_id     prod_name
  -------------------------
  ANV01       .5 ton anvil
  ANV02       1 ton anvil
  ANV03       2 ton anvil
  （3 行）
  **% 能匹配 0 个、1 个或任意多个字符。** 所以 'Jet%' 也匹配「Jet」本身。
  SQL> SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'S%'
  prod_id     prod_name
  ----------------------
  SAFE        Safe
  SLING       Sling
  （2 行）

  【二】下划线：_ 只代表一个字符
  SQL> SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil'
  prod_id     prod_name
  ------------------------
  ANV02       1 ton anvil
  ANV03       2 ton anvil
  （2 行）
  **一个下划线顶一个字符**，所以这句只匹配「1 ton anvil」和「2 ton anvil」，
  而 `.5 ton anvil` 因为点号后面还有 5，是三个字符，**匹配不上。**
  SQL> SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '% ton anvil'
  prod_id     prod_name
  -------------------------
  ANV01       .5 ton anvil
  ANV02       1 ton anvil
  ANV03       2 ton anvil
  （3 行）
  换成 % 就三个全中。**% 与 _ 的差别就这一条：一个管「多少个都行」，一个管「正好一个」。**

  【三】组合使用：% 和 _ 可以混着写
  SQL> SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '__ ton%'
  prod_id     prod_name
  -------------------------
  ANV01       .5 ton anvil
  （1 行）
  两个下划线加一个百分号：**开头正好两个字符，中间是空格 ton，后面随便。**

  【四】大小写：LIKE 默认不区分
  SQL> SELECT prod_name FROM products WHERE prod_name LIKE 'jet%'
  prod_name
  -------------
  JetPack 2000
  （1 行）
  SQL> SELECT prod_name FROM products WHERE prod_name LIKE 'Jet%'
  prod_name
  -------------
  JetPack 2000
  （1 行）
  **两句结果一样。** MySQL 默认的校对规则不区分大小写（第 27 章讲 collation），
  SQLite 的 LIKE 也默认不区分 ASCII 大小写。**要区分得显式声明，或者用正则。**

  【五】通配符的代价
  **LIKE 用不上普通索引**（前缀固定的 'Jet%' 在 MySQL 里能用索引，'%anvil%' 一定不能）。
  **因为它得逐行比对字符串。** 小表无所谓，百万行就是灾难。
  **三条使用建议，按重要排：**
  **一是别无脑加 %**——能定前缀就别两头都加；
  **二是别把它当正则用**——复杂匹配交给第 9 章；
  **三是记住位置决定快慢**——两头都加 % 是最慢的一种。

第一段前缀匹配。 'Jet%' 只命中 JetPack 2000 一个。 这种写法是 LIKE 里最快的，因为前缀固定，MySQL 能用索引。 第二段包含匹配。 '%anvil%' 命中 3 个铁砧。这种写法一定用不上索引，得全表扫。 第三段 'S%' 命中 Safe 与 Sling。 注意它不命中 'Safe' 之外的小写 'safe'——其实安全，因为 LIKE 不区分大小写，小写也能中。 第四段是这一章的关键。 '_ ton anvil' 只给 2 行， .5 ton anvil 被挡在外面——因为它的第一个位置是两个字符（点加 5）。 第五段换成 '% ton anvil'，立刻变 3 行。 一个符号的差别，一行结果的差别。再换成 '__ ton%' 又只剩 1 行—— 两个下划线卡死前两位，只有 .5 符合。 最后一段验证大小写。 'jet%' 和 'Jet%' 结果一样，说明 LIKE 默认不比较大小写。 这一条在换数据库时最容易出问题。

30 秒实验

用 LIKE 找出所有名字里带空格的产品，写出两种写法并对比行数。
试 LIKE '%'，返回几行？这说明 % 能匹配什么？
把 LIKE 'Jet%' 换成 LIKE '%Jet%'，行数一样吗？如果一样，为什么说后者更慢？

常见误区

以为 % 只匹配「至少一个字符」。它也能匹配 0 个字符，所以 'Jet%' 连「Jet」本身都能中。
在数值列上用 LIKE。隐式类型转换会让索引失效，而且结果容易反直觉。 数值比较用比较符，别用 LIKE。
以为 LIKE 一定区分大小写。MySQL 默认不区分。 要区分得显式声明，这一点在迁移到别的数据库时会翻车。

自测三问（点开对答案）

1. % 与 _ 的区别是什么？ —— % 匹配 0 个或多个字符；_ 只匹配正好一个字符。 2. 为什么 `LIKE 'Jet%'` 能用索引，而 `LIKE '%Jet%'` 不能？ —— 索引按前缀排序，能定位「以 Jet 开头」的范围；两头都加 % 就变成「任意位置含 Jet」，只能逐行比对。 3. LIKE 默认区分大小写吗？ —— MySQL 默认不区分（校对规则决定的）；PostgreSQL 默认区分。所以跨库迁移时这种查询的返回行数可能变。

通配符只能表达「含什么」，表达不了「三个数字加一个字母」这种模式。 下一章上正则：REGEXP、字符类、重复元字符、锚点——比 LIKE 强一个量级。

☐ （本章完，下一章第9章用正则表达式进行搜索）