如何在SQL中使用正则表达式？REGEXP的查询技巧指南-SQL-PHP中文网

SQL中使用REGEXP实现复杂模式匹配，比LIKE更灵活。通过正则表达式可精确筛选符合特定规则的字符串，如开头、结尾、字符集、长度等。常用元字符包括^（开头）、$（结尾）、.（任意字符）、*+?{}（量词）、[]（字符类）、|（或）、()（分组）等。例如，^A.*[0-9]$匹配以A开头、数字结尾的字符串。不同数据库语法略有差异，如MySQL用REGEXP，PostgreSQL用~或~*，Oracle用REGEXP_LIKE。但REGEXP性能较差，常导致全表扫描，不适用于大表高频查询。应避免在大数据集上直接使用，可通过预处理、分阶段查询或全文检索优化。常见陷阱包括特殊字符未转义、大小写敏感性差异、贪婪匹配问题等。掌握正则语法并结合实际场景合理使用，才能高效解决问题。

如何在sql中使用正则表达式？regexp的查询技巧指南

SQL中利用正则表达式进行模式匹配，主要通过

REGEXP

登录后复制

（在某些数据库中也可能是

RLIKE

登录后复制

或

REGEXP_LIKE

登录后复制

）运算符实现。这玩意儿的强大之处在于，它能让你用远超

LIKE

登录后复制

的灵活性和精度，去筛选、查找那些看似杂乱无章，实则暗藏规律的字符串数据。说白了，就是当你需要根据复杂模式（比如：以数字开头、包含特定字符序列、或者匹配特定长度的单词）来过滤数据时，

REGEXP

登录后复制

就是你的终极武器。

解决方案

在SQL中，

REGEXP

登录后复制

运算符（或者像MySQL/SQLite中直接使用的

REGEXP

登录后复制

，PostgreSQL中的

登录后复制

或

~*

登录后复制

，Oracle的

REGEXP_LIKE

登录后复制

）允许你对字符串列执行正则表达式匹配。其基本语法通常是：

SELECT column_name(s)
FROM table_name
WHERE column_name REGEXP 'your_regex_pattern';

登录后复制

举个例子，如果你想从一个

products

登录后复制

表中找出所有以字母'A'开头，后面跟着任意字符，最后以数字结尾的产品名称，

LIKE 'A%[0-9]'

登录后复制

是做不到的，但

REGEXP

登录后复制

可以：

SELECT product_name
FROM products
WHERE product_name REGEXP '^A.*[0-9]$';

登录后复制

这里的

登录后复制

表示字符串开始，

.*

登录后复制

表示任意字符出现零次或多次，

[0-9]

登录后复制

表示任意数字，

登录后复制

表示字符串结束。这种表达能力，是

LIKE

登录后复制

望尘莫及的。

REGEXP与LIKE：何时选择更强大的正则表达式匹配？

说实话，很多人一开始接触SQL字符串匹配，都是从

LIKE

登录后复制

操作符开始的。它简单、直观，用

登录后复制

匹配任意字符序列，

登录后复制

匹配单个字符，应对一些基本场景确实绰绰有余。比如，找所有以'apple'开头的商品，

product_name LIKE 'apple%'

登录后复制

，完美。

但问题来了，如果你的需求稍微复杂一点，

LIKE

登录后复制

的局限性就暴露无遗了。想想看，如果你需要找出所有包含至少一个数字的订单号，或者所有邮箱地址格式（比如

name@domain.com

登录后复制

），

LIKE

登录后复制

就显得力不从心了。你可能会尝试

LIKE '%[0-9]%'

登录后复制

，但很遗憾，

LIKE

登录后复制

并不理解

[0-9]

登录后复制

这种字符集语法。它只会把它当成普通的方括号和数字来匹配。

这时候，

REGEXP

登录后复制

就该登场了。在我看来，

REGEXP

登录后复制

和

LIKE

登录后复制

的关系，就像是手电筒和探照灯。手电筒日常用足够，但当你需要照亮更广阔、更复杂的区域时，探照灯才是你的不二之选。

REGEXP

登录后复制

能够理解并执行更精细的模式匹配，例如：

匹配特定长度的字符串： 找出所有由5个数字组成的邮编。
```
SELECT * FROM users WHERE postcode REGEXP '^[0-9]{5}$';
```
登录后复制
匹配字符集： 找出所有产品名称中包含元音字母（a, e, i, o, u）的产品。
```
SELECT * FROM products WHERE product_name REGEXP '[aeiouAEIOU]';
```
登录后复制
排除特定模式： 找出所有不以'http://'或'https://'开头的URL。
```
SELECT * FROM urls WHERE url NOT REGEXP '^(http|https)://';
```
登录后复制

在我个人的项目经验里，当遇到需要验证数据格式（如电话号码、身份证号）、从非结构化文本中提取信息、或者进行复杂模糊搜索时，

REGEXP

登录后复制

几乎是唯一的选择。虽然它的学习曲线比

LIKE

登录后复制

陡峭一些，但一旦掌握，你会发现它能解决很多之前看似无解的问题。

SQL正则表达式的常用模式与元字符详解

要真正玩转

REGEXP

登录后复制

，理解其背后的模式（patterns）和元字符（metacharacters）是关键。这就像学习一门新的编程语言，你需要知道它的语法和关键词。以下是一些最常用、也最核心的元素：

锚点 (Anchors)：

表单大师AI
一款基于自然语言处理技术的智能在线表单创建工具，可以帮助用户快速、高效地生成各类专业表单。

74

查看详情
- ```
^
```
  登录后复制
  ：匹配字符串的开始。例如，
```
^abc
```
  登录后复制
  会匹配"abcde"但不匹配"xabc"。
- ```
$
```
  登录后复制
  ：匹配字符串的结束。例如，
```
abc$
```
  登录后复制
  会匹配"xabc"但不匹配"abcde"。
- 例子： 找出所有以'A'开头且以'Z'结尾的城市名。
```
SELECT city FROM locations WHERE city REGEXP '^A.*Z$';
```
  登录后复制
量词 (Quantifiers)： 它们定义了前一个元素可以出现的次数。
- ```
*
```
  登录后复制
  ：匹配前一个元素零次或多次。例如，
```
ab*c
```
  登录后复制
  会匹配"ac", "abc", "abbbc"。
- ```
+
```
  登录后复制
  ：匹配前一个元素一次或多次。例如，
```
ab+c
```
  登录后复制
  会匹配"abc", "abbbc"但不匹配"ac"。
- ```
?
```
  登录后复制
  ：匹配前一个元素零次或一次。例如，
```
ab?c
```
  登录后复制
  会匹配"ac", "abc"。
- ```
{n}
```
  登录后复制
  ：匹配前一个元素恰好
```
n
```
  登录后复制
  次。例如，
```
[0-9]{3}
```
  登录后复制
  匹配恰好三个数字。
- ```
{n,}
```
  登录后复制
  ：匹配前一个元素至少
```
n
```
  登录后复制
  次。例如，
```
[0-9]{3,}
```
  登录后复制
  匹配至少三个数字。
- ```
{n,m}
```
  登录后复制
  ：匹配前一个元素
```
n
```
  登录后复制
  到
```
m
```
  登录后复制
  次。例如，
```
[0-9]{3,5}
```
  登录后复制
  匹配三到五个数字。
- 例子： 找出所有包含至少两个连续数字的字符串。
```
SELECT data FROM my_table WHERE data REGEXP '[0-9]{2,}';
```
  登录后复制
字符类 (Character Classes)： 定义了可以匹配哪些字符。
- ```
.
```
  登录后复制
  ：匹配除换行符之外的任何单个字符。
- ```
[abc]
```
  登录后复制
  ：匹配方括号内的任何一个字符。例如，
```
[aeiou]
```
  登录后复制
  匹配任何一个小写元音字母。
- ```
[^abc]
```
  登录后复制
  ：匹配除方括号内的任何字符。例如，
```
[^0-9]
```
  登录后复制
  匹配任何非数字字符。
- ```
[a-z]
```
  登录后复制
  ：匹配指定范围内的任何字符。例如，
```
[A-Za-z]
```
  登录后复制
  匹配任何大小写字母。
- ```
\d
```
  登录后复制
  ：匹配任何数字字符（等同于
```
[0-9]
```
  登录后复制
  ）。
- ```
\d
```
  登录后复制
  ：匹配任何非数字字符（等同于
```
[^0-9]
```
  登录后复制
  ）。
- ```
\w
```
  登录后复制
  ：匹配任何单词字符（字母、数字、下划线，等同于
```
[A-Za-z0-9_]
```
  登录后复制
  ）。
- ```
\w
```
  登录后复制
  ：匹配任何非单词字符。
- ```
\s
```
  登录后复制
  ：匹配任何空白字符（空格、制表符、换行符）。
- ```
\s
```
  登录后复制
  ：匹配任何非空白字符。
- 例子： 找出所有包含一个单词字符后跟一个数字的字符串。
```
SELECT text_col FROM docs WHERE text_col REGEXP '\w\d';
```
  登录后复制
选择 (Alternation)：
- ```
|
```
  登录后复制
  ：逻辑或操作，匹配
```
|
```
  登录后复制
  符号左边或右边的表达式。例如，
```
cat|dog
```
  登录后复制
  匹配"cat"或"dog"。
- 例子： 找出所有以'Mr.'或'Ms.'开头的名字。
```
SELECT name FROM people WHERE name REGEXP '^(Mr\.|Ms\.)';
```
  登录后复制
分组 (Grouping)：
- ```
()
```
  登录后复制
  ：用于将表达式分组，可以对整个组应用量词，或者捕获匹配的子字符串。
- 例子： 找出所有以'ab'重复两次开头的字符串。
```
SELECT value FROM data WHERE value REGEXP '^(ab){2}';
```
  登录后复制

掌握了这些，你就能像搭积木一样，构建出满足各种复杂需求的正则表达式了。

处理SQL中REGEXP的性能考量与常见陷阱

尽管

REGEXP

登录后复制

功能强大，但在实际使用中，我们必须清醒地认识到它并非万能药，尤其是在性能方面。我个人就遇到过因为滥用

REGEXP

登录后复制

导致查询效率直线下降的案例，那可真是让人头疼。

性能考量：

全表扫描的常客： 大多数数据库的查询优化器，在遇到
```
REGEXP
```
登录后复制
操作时，是无法有效利用索引的。这意味着，即使你的列上建有索引，
```
WHERE column_name REGEXP 'pattern'
```
登录后复制
这样的查询也往往会触发全表扫描。对于包含数百万甚至上亿行数据的大表来说，这无疑是灾难性的。
计算开销大： 正则表达式的匹配过程本身就是一种计算密集型操作。特别是当正则表达式本身非常复杂，或者需要匹配的字符串很长时，CPU的开销会显著增加。
避免在大型数据集上滥用： 如果你的查询涉及的数据量很大，并且对响应时间有严格要求，那么应该尽量避免在
```
WHERE
```
登录后复制
子句中直接使用
```
REGEXP
```
登录后复制
。可以考虑的替代方案包括：
- 预处理数据： 在数据写入时就进行格式验证，或者提取出关键信息存储在单独的、可索引的列中。
- 分阶段查询： 先用
```
LIKE
```
  登录后复制
  或其他可索引的操作缩小结果集，再对小结果集应用
```
REGEXP
```
  登录后复制
  。
- 全文搜索方案： 对于复杂的文本搜索需求，专门的全文搜索引擎（如Elasticsearch、Solr）或数据库内置的全文搜索功能（如MySQL的
```
FULLTEXT
```
  登录后复制
  索引）会是更高效的选择。

常见陷阱：

特殊字符的转义： 正则表达式中有很多元字符（如
```
.
```
登录后复制
,
```
*
```
登录后复制
,
```
+
```
登录后复制
,
```
?
```
登录后复制
,
```
(
```
登录后复制
,
```
)
```
登录后复制
,
```
[
```
登录后复制
,
```
]
```
登录后复制
,
```
{
```
登录后复制
,
```
}
```
登录后复制
,
```
^
```
登录后复制
,
```
$
```
登录后复制
,
```
|
```
登录后复制
,
```
\
```
登录后复制
）。如果你想匹配这些字符本身，而不是它们的特殊含义，就必须使用反斜杠
```
\
```
登录后复制
进行转义。比如，要匹配字符串中的句点
```
.
```
登录后复制
，你得写成
```
\.
```
登录后复制
。我经常看到有人忘了转义，结果匹配结果一塌糊涂。
```
-- 错误：匹配任何字符
SELECT 'my.domain' REGEXP 'my.domain'; -- 结果可能是1 (true)
-- 正确：只匹配句点
SELECT 'my.domain' REGEXP 'my\.domain'; -- 结果是1 (true)
SELECT 'mydomain' REGEXP 'my\.domain'; -- 结果是0 (false)
```
登录后复制
大小写敏感性： 不同的数据库系统对
```
REGEXP
```
登录后复制
的默认大小写敏感性处理不同。例如，MySQL的
```
REGEXP
```
登录后复制
默认是大小写不敏感的，而PostgreSQL的
```
~
```
登录后复制
是大小写敏感的，
```
~*
```
登录后复制
才是不敏感的。如果你需要精确控制，可能需要使用特定的修饰符（如MySQL的
```
REGEXP BINARY
```
登录后复制
）或者数据库提供的函数（如
```
LOWER()
```
登录后复制
或
```
UPPER()
```
登录后复制
将字符串统一大小写后再匹配）。
```
-- MySQL (默认不敏感)
SELECT 'Apple' REGEXP 'apple'; -- 结果是1
-- MySQL (强制敏感)
SELECT 'Apple' REGEXP BINARY 'apple'; -- 结果是0
```
登录后复制
贪婪与非贪婪匹配： 量词（
```
*
```
登录后复制
,
```
+
```
登录后复制
,
```
?
```
登录后复制
,
```
{n,m}
```
登录后复制
）默认是“贪婪”的，它们会尽可能多地匹配字符。有时候这会导致意想不到的结果。如果你想进行“非贪婪”匹配（尽可能少地匹配），可以在量词后面加上
```
?
```
登录后复制
。例如，
```
.*?
```
登录后复制
。不过，这个概念稍微高级一点，对于日常使用，通常先理解贪婪匹配即可。
```
-- 贪婪匹配：匹配到最后一个'>'
SELECT '<a><b>' REGEXP '<.*>'; -- 匹配到 '<a><b>'
-- 非贪婪匹配：匹配到第一个'>'
-- 注意：并非所有SQL REGEXP引擎都支持非贪婪匹配，需要查阅具体数据库文档
-- 例如，在某些环境中，你可能需要 'REGEXP_SUBSTR(..., '<.*?>(.*)', 1, 1, 'i', 1)' 这样的函数
```
登录后复制
不同SQL方言的差异： 就像前面提到的，MySQL、PostgreSQL、Oracle、SQLite等数据库在
```
REGEXP
```
登录后复制
的实现和语法上都有细微差别。当你从一个数据库迁移到另一个时，可能需要调整你的正则表达式。始终查阅你正在使用的数据库的官方文档，这是最稳妥的做法。