第9章用正则表达式进行搜索 · 波仔的 AI 编程实战课

📄 一句话概括

REGEXP 是 LIKE 的加强版：它认字符类、认重复次数、认锚点。 Demo 在 13 个产品上跑出一串结果： '2000' 命中 1 个、'Jet|Safe' 命中 2 个、'[JT]et' 命中 1 个；换区间立刻见分晓，'[1-3] ton' 命中 2 个而 '[1-5] ton' 变成 3 个；锚点与重复次数也管用，'anvil$' 命中 3 个、'[0-9]{4}' 命中 1 个。最后一节交代本沙箱怎么真跑正则： SQLite 没有 REGEXP，我们在连接时注册了一个 Python 函数，把匹配交给 re； 字符类改用等价区间写，命中结果与 MySQL 一致。

📝 本章和前面几章的关系

上一章的通配符只有两个符号（% 和 _）， 表达不了「四个连续数字」「以 anvil 结尾」这种模式。 正则就是为这类需求准备的。 这一章是全书语法最密的一章，但真正要记的不多：几个字符类、四个重复符号、两个锚点。 剩下靠查表。

一、REGEXP：包含匹配，不是整串匹配

WHERE prod_name REGEXP '2000' 和 LIKE '%2000%' 结果一样——都是「含 2000 的」。这说明一件重要的事：REGEXP 默认是包含匹配，相当于自带两头 %。 要整串匹配得自己加锚点：'^2000$' 才是「整串就等于 2000」。 这是从别的语言转过来最容易搞错的一点——很多语言的正则默认就是整串匹配。 REGEXP 后面跟的是正则表达式，不是普通字符串。 所以里面的 .、*、+、?、[、 ( 都有特殊含义，要匹配它们本尊必须转义。

二、OR 匹配与字符集

竖线 | 是「或者」：'Jet|Safe' 命中 JetPack 2000 与 Safe 两个。等价于 LIKE 'Jet%' OR LIKE 'Safe%'，但一条就写完。 方括号 [ ] 是「这里可以是其中之一」：'[JT]et' 表示 J 或 T 后面跟 et。 Demo 里命中 JetPack 2000——因为样例库里没有 Tet 开头的产品。 方括号里可以写范围：'[1-3] ton' 等于 '[123] ton'，命中 1 ton anvil 与 2 ton anvil； 放宽到 '[1-5] ton'，.5 ton anvil 也进来了——正则只找「有一个 1 到 5 的数字紧跟空格 ton」，前面有没有点它不管。 这个例子说明正则的一个特点：它找的是「匹配的片段」，不是「整行符合」。 想让整行都符合，两头加锚点。

三、特殊字符与锚点

点号 . 在正则里是「任意一个字符」，所以 'anvil.' 不是「anvil 加一个点」，而是「anvil 加任意一个字符」。要匹配真的点就写 '\.'。 反斜杠在两层里都要转义：SQL 字符串一层、正则语法一层。 MySQL 里写 REGEXP '\\.'，本课这套写法同理——这是最容易写错的地方，多一个少一个反斜杠，结果就从 1 行变 0 行。 ^ 是开头锚点、$ 是结尾锚点。 '^J' 只匹配以 J 打头的，'anvil$' 只匹配以 anvil 结尾的。 两个一起用才是整串匹配。 锚点还有实际用途：'^[0-9]' 查「以数字开头」， '[0-9]$' 查「以数字结尾」——数据清洗时这两句最常用。

四、字符类与重复元字符

字符类是「预设好的方括号」：[:digit:] 任意数字、[:alpha:] 任意字母、 [:alnum:] 字母或数字、[:space:] 空白。 这几个类名本身已经带一对方括号，用的时候外面还要再套一层，只写半截就不是字符类。 重复元字符管「来几次」：* 零次或多次、+ 一次或多次、? 零次或一次、{n} 正好 n 次、 {n,} 至少 n 次、{n,m} n 到 m 次。 Demo 里 '[0-9]{4}' 的意思是「四个连续数字」，命中 JetPack 2000——只有它名字里有四位数字。 * 和 + 的区别值得单独记：'x*' 匹配空串（因为零次也算），'x+' 至少要有一个 x。 写错一个符号，可能整表都被匹配上。

五、本沙箱怎么跑 REGEXP

SQLite 没有 REGEXP 运算符（它只留了这个关键字的位置）。所以 Demo 在连接时注册了自己的实现：一个两参数的 Python 函数，内部调 re.search。 这时 POSIX 字符类得绕一下：Python 的 re 不认类名，双层方括号会被它当成嵌套集合，报 warning 而且匹配不到。所以 Demo 的 SQL 直接写等价区间 [0-9]、[A-Za-z]，命中结果与 MySQL 用 POSIX 类时完全一致。 这样得到的语义和 MySQL 的 REGEXP 一致：都是包含匹配、都支持上面这些元字符。 差别在 MySQL 用的是自己的正则引擎，而且除了 REGEXP，还有 REGEXP_LIKE()、REGEXP_INSTR()、 REGEXP_REPLACE() 一组函数。 这一节要记住的不是 SQLite 的绕法，而是一条判断标准： 看到「同一个工作两种实现」，先问「语义一样吗」，再问「哪里不一样」。这次答案是：语法一样、引擎不同、函数集不同。

六、小结

教材小结：REGEXP 比 LIKE 强，支持字符类、重复、锚点；它默认是包含匹配。 本课补三条：一是包含匹配要靠 ^ 和 $ 才能变整串匹配； 二是反斜杠要写两层（字符串一层、正则一层）； 三是 POSIX 类名自带一对方括号，实际用时外面再套一层，别只写半截。

七、动手 Demo：从包含匹配到锚点，一步步收紧

Demo 六段，从最松的「含 2000」一直到最紧的「四位连续数字」。 每一段都打出真实的 SQL 与命中行数，你可以照着改条件自己试。 注意第六段与本课开头那句的区别：'[0-9]{4}' 是「某处有四位数字」，不是「整串就是四位数字」。 正则默认只找片段，这是全章最容易错的地方。

import re, sqlite3

con = sqlite3.connect(":memory:")
con.create_function("REGEXP", 2, lambda p, s: 1 if re.search(p, s or "") else 0)

con.execute("SELECT prod_name FROM products WHERE prod_name REGEXP '2000'")
con.execute("SELECT prod_name FROM products WHERE prod_name REGEXP 'Jet|Safe'")
con.execute("SELECT prod_name FROM products WHERE prod_name REGEXP '[JT]et'")
con.execute("SELECT prod_name FROM products WHERE prod_name REGEXP '[1-3] ton'")
con.execute("SELECT prod_name FROM products WHERE prod_name REGEXP 'anvil$'")
con.execute("SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9]{4}'")

实跑输出：

--------------------------------------------------------------------
第九课：用正则表达式进行搜索
--------------------------------------------------------------------

  【一】REGEXP：比 LIKE 强一个量级
  SQL> SELECT prod_name FROM products WHERE prod_name REGEXP '2000'
  prod_name
  -------------
  JetPack 2000
  （1 行）
  SQL> SELECT prod_name FROM products WHERE prod_name LIKE '%2000%'
  prod_name
  -------------
  JetPack 2000
  （1 行）
  **上面两句等价**（都找「含 2000 的」），但 REGEXP 的能力远不止于此。
  **注意：REGEXP 是「包含匹配」，不是「整串匹配」**——它相当于自带两头 %。

  【二】OR 匹配：竖线
  SQL> SELECT prod_name FROM products WHERE prod_name REGEXP 'Jet|Safe'
  prod_name
  -------------
  JetPack 2000
  Safe
  （2 行）
  竖线是「或者」。等价于 LIKE 'Jet%' OR LIKE 'Safe%'，但一条就写完了。

  【三】匹配几个字符之一：方括号
  SQL> SELECT prod_name FROM products WHERE prod_name REGEXP '[JT]et'
  prod_name
  -------------
  JetPack 2000
  （1 行）
  **[JT] 表示「J 或 T 一个字符」**，所以 Jet 和 Tet 都能中。
  SQL> SELECT prod_name FROM products WHERE prod_name REGEXP '[1-3] ton'
  prod_name
  ------------
  1 ton anvil
  2 ton anvil
  （2 行）
  **[1-3] 是范围**，等于 [123]。所以只匹配「1 ton anvil」「2 ton anvil」，
  **而 `.5 ton anvil` 不中**——那句话里的 5 不在 1 到 3 之间。
  SQL> SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] ton'
  prod_name
  -------------
  .5 ton anvil
  1 ton anvil
  2 ton anvil
  （3 行）
  把范围放宽到 [1-5]，`.5 ton anvil` 也进来了——**它在 5 前面有个点，
   但正则只认「有一个 1 到 5 的数字紧跟空格 ton」，点号它不管。**

  【四】匹配特殊字符：要转义
  SQL> SELECT prod_name FROM products WHERE prod_name REGEXP '\.'
  prod_name
  -------------
  .5 ton anvil
  （1 行）
  **点号在正则里是「任意一个字符」的通配符**，要匹配真的点必须转义。
  MySQL 里写两个反斜杠（字符串层一个、正则层一个），本课这套写法同理。
  SQL> SELECT prod_name FROM products WHERE prod_name REGEXP 'anvil$'
  prod_name
  -------------
  .5 ton anvil
  1 ton anvil
  2 ton anvil
  （3 行）
  **$ 是「结尾锚点」**，所以只有以 anvil 结尾的三个铁砧命中，
  而 'JetPack 2000' 之类不中。

  【五】字符类与重复：真正省事的地方
  **POSIX 字符类的类名长成 [:digit:] 这样，用的时候外面还要再套一层方括号。**
  为了能在这套沙箱里直接跑，下表第三列给的是**等价区间写法**，两者含义相同。

  类名            含义                      等价区间
  ----------------------------------------------------------------------------
  [:digit:]       任意数字                  [0-9]
  [:alpha:]       任意字母                  [A-Za-z]
  [:alnum:]       字母或数字                [0-9A-Za-z]
  [:space:]       空白字符                  [ \t\r\n]
  *               0 个或多个                REGEXP 'x*'
  +               1 个或多个                REGEXP 'x+'
  ?               0 个或 1 个               REGEXP 'x?'
  {n}             正好 n 个                 REGEXP 'x{3}'
  {n,}            至少 n 个                 REGEXP 'x{2,}'
  ^               开头锚点                  REGEXP '^Jet'
  $               结尾锚点                  REGEXP 'anvil$'
  SQL> SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9]{4}'
  prod_name
  -------------
  JetPack 2000
  （1 行）
  **四个连续数字**，所以只有编号里带 2000 的那个中。
  SQL> SELECT prod_name FROM products WHERE prod_name REGEXP '^J'
  prod_name
  -------------
  JetPack 2000
  （1 行）
  **^ 是开头锚点**，只匹配以 J 打头的产品名。

  【六】本沙箱怎么跑 REGEXP
  **SQLite 本身没有 REGEXP 运算符**，所以 Demo 在连接时注册了一个 Python 函数
  （下面这一行是化简写法，真实版多几行）：
  `con.create_function('REGEXP', 2, lambda p, s: 1 if re.search(p, s) else 0)`
  **语义和 MySQL 的 REGEXP 一致**（都是「包含匹配」、都支持上面这些元字符），
  因为底层用的就是同一套正则语法。**唯一要补一刀的是 POSIX 类名**——
  Python 的 re 不认，注册的函数会先把几个类名换成等价区间再匹配，
  **所以本章 SQL 一律直接写区间，你写类名也不会报错。**
  **差别在 MySQL 用的是自己的正则引擎**，而且不止 REGEXP，
  还有 REGEXP_LIKE()、REGEXP_INSTR() 等等。

第一段 REGEXP 与 LIKE 等价。 '2000' 与 '%2000%' 都只命中 JetPack 2000。 这证明 REGEXP 是包含匹配。 第二段竖线。 'Jet|Safe' 命中两个，一条顶两条 LIKE。 第三段方括号。 '[JT]et' 只命中 JetPack 2000——样例库里没有 Tet 开头的产品，所以「或者」的另一半没用上。 第四段是范围，也是最容易看错的一段。 '[1-3] ton' 给 2 行（1 ton 与 2 ton）；放宽到 '[1-5] ton' 变 3 行，.5 ton anvil 混进来了——因为里面的 5 落在 1 到 5 之间，而正则不管 5 前面还有个点。 第五段锚点。 'anvil$' 只给 3 个铁砧，以 anvil 结尾的才算。 'JetPack 2000' 虽然含 Jet，但结尾不对，不中。 第六段字符类加重复。 '[0-9]{4}' 只命中 JetPack 2000。 注意这是「包含四个连续数字」，不是「整串是四个数字」——要后者得写 '^[0-9]{4}$'。

30 秒实验

把 REGEXP '^J' 换成 REGEXP 'J'，行数变吗？为什么？
写一句「以数字开头」的正则，看能命中几个产品。
用 '^[A-Za-z]+ [0-9]{4}$' 试一次，能匹配 JetPack 2000 吗？想想为什么不行。

常见误区

以为 REGEXP 是整串匹配。它默认只找片段，要整串得加 ^ 和 $。
反斜杠只写一层。字符串层和正则层各要一层，'\.' 才是「真的点」。
字符类少写一层方括号。[:digit:] 只是类名，外面还得再套一层才是完整写法。

自测三问（点开对答案）

1. REGEXP 和 LIKE 最根本的区别是什么？ —— REGEXP 用正则语法，能表达字符类、重复次数、锚点；LIKE 只有 % 和 _ 两个通配符。两者默认都是包含匹配。 2. `[0-9]{4}` 与 `^[0-9]{4}$` 有什么不同？ —— 前者「某处有四个连续数字」，后者「整串正好四个数字」。 3. 为什么匹配一个点要写 `\.` 而不是 `.`？ —— 因为 `.` 在正则里是「任意一个字符」的元字符，必须用反斜杠转义才表示字面的点；而在 SQL 字符串里反斜杠本身又要转义，所以看起来是两个反斜杠。

取数、筛行、模糊匹配都齐了，但取出来的是「原始列」。 下一章开始造新列：拼接字段、算术计算、给列起别名——数据库第一次不只是「拿数据」，而是「算数据」。

☐ （本章完，下一章第10章创建计算字段）