一、REGEXP 在沙箱里是个空缺

照抄教材的 列 REGEXP '模式'，沙箱直接报 no such function: REGEXP。 原因不是「缺个插件」，而是 SQLite 把 REGEXP 设计成了一个可注册的钩子。 库只认这个名字和它的调用约定：REGEXP 运算符等价于调用 regexp(模式, 值)。 具体怎么比，由使用者挂一个函数上去。没挂，就报找不到函数。 所以注册只要三行：定义一个两参数函数，用 create_function 挂到 regexp 这个名字上。 挂上之后，教材里的句子几乎能原样敲。

def regexp(pat, s):
    return 1 if s is not None and re.search(pat, s) else 0

c.create_function(u"regexp", 2, regexp)

注意参数顺序是「模式在前、值在后」，和人的直觉相反。 写反了不会报错，只会一个都匹配不上——这种错最难查，因为语句看起来完全正常。

二、六个常用模式

正则的语法一天学不完，但 SQL 里常用的就那几个。

^ 是开头、$ 是结尾、[0-9] 是一位数字、{5} 是「前面那个重复五次」。 | 是「或者」，括号用来把「或者」的范围圈住。 第一行和第二行只差一个 $，命中数从 9 变成 10。 因为第二行没写结尾锚点，G00010 到 G00019 也都被算进去了。 所以写正则要养一个习惯：能加 ^ 和 $ 的地方都加上。 少一个锚点不会报错，只会悄悄多算几十行，这种错在统计数字里最难看。

三、星号和加号：匹配多个字符

教材这一节的小标题写的是「全角星号和加号」，其实应该是半角 * 和 +。 正则里的「全角」只是字符，没有通配含义，按半角记就对了。

* 和 + 的差别就在「零次行不行」，这是最容易写错的一处。 * 允许前面那个字符一次都不出现，所以它几乎总能匹配到「空」，用起来要小心。 LIKE 里没有这一层：它的 % 就是「零到多个」，_ 就是「恰好一个」。 所以能说清的需求，用 LIKE 更稳；说不清的（比如「数字至少三位」），才请正则上场。

四、正则能表达、LIKE 表达不了的

两件事 LIKE 根本做不到：一是「某一位必须是数字」，二是「多个前缀任选」。 LIKE 的 _ 只代表「任意一个字符」，分不出数字和字母。

两格的结果都对得上，说明「能不能查出来」这一层，两者打平。 差别在写起来的长度：第二格 LIKE 要两个 OR，正则一对括号就包住了。 每多一个前缀，LIKE 就多一条 OR，正则只多一个竖线。 前缀多到五六个的时候，正则就好读多了。 还有几件事 LIKE 完全做不到： 「最后一位是 1 或 3」，正则写 [13]$；「倒数第三位必须是数字」，正则写 [0-9]..$。

五、代价：正则用不上索引

同一个前缀条件，LIKE 是 54 微秒，REGEXP 是 9.92 毫秒，差了 183 倍。 两个原因：一是它必须每行回调一次函数，二是它用不上索引，只能全表扫。 前缀匹配是「范围查找」，正则是「逐行判定」。 这不是优化技巧的差别，是算法级的差别——一个只要定位一段，一个必须看完全表。 所以能写成 LIKE '前缀%' 的，就别用正则。 正则留给「LIKE 说不清」的那部分需求，比如「第几位必须是数字」这种。 MySQL 那边也一样的道理：REGEXP 不走索引，用之前先想清楚表有多大。 十万行的表能忍，千万行的表就该换个思路，比如把特征先抽成一列存起来。

六、教材上的写法与两处差异

教材的句子是 SELECT * FROM tb_goods WHERE code REGEXP '^G0000[1-9]$';， 在 MySQL 上直接能跑。 沙箱里得先挂函数才能跑，挂完语法一模一样。 这就是本课一直的做法：能等价执行的照原样敲，不能的就补一个替身。 差异在细节：MySQL 8.0 的正则走 ICU 库，支持 \d 这种写法； SQLite 这边走 Python 的 re，也支持 \d，但两边对多字节字符的处理不完全一致。 所以正则写在 MySQL 上跑通，搬到别处还得再验一遍。 这一条没有捷径，只能现测。

七、动手 Demo：正则查询，与它在沙箱里的替身

这一段先照抄教材撞一次墙，再挂上替身函数，然后才讲用法和代价。 顺序故意这么排：先看见「跑不了」，才知道为什么要多那三行。

import re, sqlite3

c = sqlite3.connect(u"d043.db")

# ① 照抄会报错
try:
    c.execute(u"SELECT COUNT(*) FROM tb_goods WHERE code REGEXP '^G0001'")
except sqlite3.OperationalError as e:
    print(e)                      # no such function: REGEXP

# ② 挂一个替身：模式在前、值在后
def regexp(pat, s):
    return 1 if s is not None and re.search(pat, s) else 0

c.create_function(u"regexp", 2, regexp)
print(c.execute(u"SELECT code FROM tb_goods "
                u"WHERE code REGEXP '^G0000[1-9]$'").fetchall())

# ③ 代价：正则 vs 前缀 LIKE
c.execute(u"PRAGMA case_sensitive_like = ON")
c.execute(u"SELECT COUNT(*) FROM tb_goods WHERE code LIKE 'G0001%'").fetchall()
c.execute(u"SELECT COUNT(*) FROM tb_goods WHERE code REGEXP '^G0001'").fetchall()

实跑输出：

--------------------------------------------------------------------
第四十三课：正则查询，与它在沙箱里的替身
--------------------------------------------------------------------

  **教材 9.8 讲用正则表达式查询。MySQL 的写法是 `列 REGEXP '模式'`。**
  **沙箱的 SQLite 没有内建这个函数，得先给它装一个。**

  一、照抄教材会直接报错
  --------------------------------------------------------------------
  **报错原文：`no such function: REGEXP`。**
  **`REGEXP` 在 SQLite 里不是一个内建函数，它是一个「钩子」——**
  **库只认这个名字，具体怎么比由使用者注册进去。没注册，就报找不到函数。**
  **把函数挂上去，同一句就能跑了：**
  code
  ------
  G00001
  G00002
  G00003
  G00004
  G00005
  G00006
  G00007
  G00008
  G00009
  9 rows in set
  **注册只要三行：定义一个 `regexp(模式, 值)` 函数，再用 `create_function` 挂上去。**
  **挂上之后，`列 REGEXP '模式'` 就等价于 `regexp('模式', 列)`。**
  **注意参数的顺序是「模式在前、值在后」，写反了匹配就全错。**

  二、六个常用模式
  --------------------------------------------------------------------

  模式                       命中行数  意思
  ------------------------------------------------------------
  `^G0000[1-9]$`             9         G0000 后面是一位 1 到 9
  `^G0001`                   10        以 G0001 开头
  `1$`                       500       以 1 结尾
  `^G[0-9]{5}$`              5000      G 加五位数字，整串对齐
  `^(数码|家电)商品1[0-9]$`  2         两个前缀任选其一
  `商品(99|100)$`            2         编号是 99 或 100
  **`^` 是开头、`$` 是结尾、`[0-9]` 是一位数字、`{5}` 是「前面那个重复五次」。**
  **`|` 是「或者」，括号用来把「或者」的范围圈住。**
  **第一行和第二行只差一个 `$`，命中数从 9 变成 10。**
  **因为第二行没写结尾锚点，`G00010` 到 `G00019` 也都被算进去了。**
  **所以写正则要养一个习惯：能加 `^` 和 `$` 的地方都加上。**
  **少一个锚点不会报错，只会悄悄多算几十行，这种错在统计数字里最难看。**

  三、REGEXP 能表达、LIKE 表达不了的
  --------------------------------------------------------------------

  要查什么                LIKE 的写法  REGEXP 的写法
  --------------------------------------------------
  G00001~G00009           9 行         9 行
  两个前缀、两位数的编号  2 行         2 行
  **两格的结果都对得上，说明「能不能查出来」这一层，两者打平。**
  **差别在写起来的长度：第二格 `LIKE` 要两个 `OR`，正则一对括号就包住了。**
  **每多一个前缀，`LIKE` 就多一条 `OR`，正则只多一个竖线。**
  **前缀多到五六个的时候，正则就好读多了。**
  **还有几件事 `LIKE` 根本做不到：**
  **比如「最后一位是 1 或 3」，正则写 `[13]$`；**
  **「倒数第三位必须是数字」，正则写 `[0-9]..$`。**
  **`LIKE` 的 `_` 只代表「任意一个字符」，分不出数字和字母，所以这类需求它写不出来。**

  四、代价：正则用不上索引
  --------------------------------------------------------------------

  写法                    耗时
  ---------------------------------
  `code LIKE 'G0001%'`    54 微秒
  `code REGEXP '^G0001'`  9.21 毫秒
  **同一个前缀条件，正则慢了 170 倍。**
  **两个原因：一是它必须每行回调一次 Python 函数，二是它用不上索引，只能全表扫。**
  **所以能写成 `LIKE '前缀%'` 的，就别用正则。**
  **前缀匹配是「范围查找」，正则是「逐行判定」，这不是优化技巧的差别，是算法级的差别。**

  五、教材上的 MySQL 写法
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | SELECT * FROM tb_goods WHERE code REGEXP '^G0000[1-9]$';
  教材原码，本沙箱需先注册 regexp 函数才能跑

  **MySQL 的 `REGEXP` 是内建的，不用注册，语法和这里一模一样。**
  **差异在细节：MySQL 8.0 的正则用的是 ICU 库，支持 `\d` 这种写法；**
  **SQLite 这边走的是 Python 的 `re`，也支持 `\d`，但两边对「多字节字符」的处理不完全一致。**
  **所以正则写在 MySQL 上跑通，搬到别处还得再验一遍。**
  **这一条没有捷径，只能现测。**

报错那一行是 no such function: REGEXP，注意它说的是「没有这个函数」，不是「语法错误」。 这一点很关键：REGEXP 是 SQL 里的运算符，但底层实现是一个同名函数。 所以注册的时候名字必须叫 regexp，大小写无所谓，但拼错就白搭。 挂上之后立刻给出 9 行：G00001 到 G00009。 这个 9 和「不带 $ 的 10」一比，就是锚点那一课最好的注解。 最后那段代价是 54 微秒对 9.92 毫秒，183 倍。 这个倍数不是稳定值，机器不同会变；但「两个数量级」这个量级是稳的。

30 秒实验

1. 把模式改成 ^G0000，看命中数怎么变，想想为什么不是 9。
2. 把 regexp 的两个参数写反，确认它一个都匹配不上却不报错。
3. 把 LIKE 'G0001%' 换成 LIKE '%0001%'，看它还走不走索引。

常见误区

1. 以为 REGEXP 是内置函数，报「找不到函数」就去怀疑安装包。
2. 注册函数时把「模式」和「值」两个参数写反，语句正常但一个都匹配不上。
3. 在千万行的大表上用正则筛，查一次就把库拖住。

第 9 章到这里结束。下一篇进第 10 章：常用函数，从数学函数开始。

• ☐ （本篇完，下一篇 10.1 MySQL 函数）

相关笔记

• 单表查询
• 基本查询语句
• 子查询
• 🛢️ MySQL从入门到精通 · 索引