【MySQL】（基础篇七） —— 通配符和正则表达式

通配符和正则表达式

本章介绍什么是通配符、如何使用通配符以及怎样使用LIKE操作符进行通配搜索，以便对数据进行复杂过滤；如何使用正则表达式来更好地控制数据过滤。

LIKE操作符

在之前的案例中，我们都是用确定的条件进行查找和筛选，并没有实现模糊查询，比如要查找名字中有王这个字的数据。这种需求就需要使用通配符来解决。

通配符：（wildcard） 用来匹配值的一部分的特殊字符。

搜索模式：（search pattern）由字面值、通配符或两者组合构成的搜索条件。

为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

百分号(%)通配符

最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现任意次数。

【示例】在employees表中查询first_name以al开头的条目信息

SELECT *
FROM `employees`
WHERE first_name LIKE 'al%';

运行结果：

通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。

【示例】在employees表中查询first_name中包含la的信息

SELECT first_name
FROM `employees`
WHERE first_name LIKE '%la%';

运行结果：

尾空格可能会干扰通配符匹配。例如，在保存词anvil 时， 如果它后面有一个或多个空格， 则子句WHERE
prod_name LIKE '%anvil’将不会匹配它们，因为在最后的l后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数（后续文章会详细介绍函数用法）去掉首尾空格。

虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE first_name LIKE '%'也不能匹配用值NULL作为名字的行。

下划线(_)通配符

另一个常用的通配符是下划线（_）。下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。

【示例】查询employees表中first_name中以l开头一共四个字符的信息条目

SELECT first_name
FROM `employees`
WHERE first_name LIKE 'l___';

运行结果：

与%能匹配0个字符不一样，_总是匹配一个字符，不能多也不能少。

通配符使用技巧

正如所见，MySQL的通配符很有用。但这种功能是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
• 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。总之，通配符是一种极重要和有用的搜索工具，以后我们经常会用到它。

正则表达式

对于基本的过滤（或者甚至是某些不那么基本的过滤），前面介绍的过滤方法+通配符就足够了。但随着过滤条件的复杂性的增加，WHERE子句本身的复杂性也有必要增加。

这也就是正则表达式变得有用的地方。正则表达式是用来匹配文本的特殊的串（字符集合）。如果你想从一个文本文件中提取电话号码，可以使用正则表达式。如果你需要查找名字中间有数字的所有文件，可以使用一个正则表达式。如果你想在一个文本块中找到所有重复的单词，可以使用一个正则表达式。如果你想替换一个页面中的所有URL为这些URL的实际HTML链接，也可以使用正则表达式实现。

所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表达式。有见识的程序员和网络管理员已经关注作为他们技术工具重要内容的正则表达式很长时间了。

正则表达式用正则表达式语言来建立，正则表达式语言是用来完成刚讨论的所有工作以及更多工作的一种特殊语言。此处我也仅仅是为你介绍一些简单和常用的正则语言，更详细的内容可以关注我的博客，我后续会专门为正则语言开一个专栏。

MySQL常用正则

MySQL用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式过滤SELECT检索出的数据。

基本字符匹配

我们从一个非常简单的例子开始。

【示例】下面的语句检索employees表中列first_name包含文本al的所有行：

SELECT first_name
FROM `employees`
WHERE first_name REGEXP 'al';

运行结果：

除关键字LIKE被REGEXP替代外，这条语句看上去非常像使用LIKE的语句。它告诉MySQL，REGEXP后所跟的东西作为正则表达式处理。

REGEXP 是一个用于执行正则表达式匹配的函数，它允许你在SQL查询中进行复杂的文本模式匹配。基本语法如下：

expr REGEXP pattern

这里：

• expr 是你想要检查是否匹配正则表达式模式的表达式（通常是字段名或字符串值）。
• pattern 是定义匹配规则的正则表达式模式。

LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用
通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别。

OR匹配

为搜索两个串之一（或者为这个串，或者为另一个串），使用|

【示例】检索employees表中列first_name包含文本al或ch的所有行：

SELECT first_name
FROM `employees`
WHERE first_name REGEXP 'al|ch';

运行结果：

匹配几个字符之一

如果你只想匹配特定的字符，可通过指定一组用[和]括起来的字符来完成，如下所示：

【示例】检索employees表中列first_name包含文本al、bl、cl的所有行：

SELECT first_name
FROM `employees`
WHERE first_name REGEXP '[abc]l';

运行结果：

这里，使用了正则表达式[abc]l。[abc]定义一组字符，它的意思是匹配a或b或c，因此，al和cl都匹配且返回（没有bl）。

正如所见，[]是另一种形式的OR语句。事实上，正则表达式[abc]l为[a|b|c]l的缩写，也可以使用后者。

匹配范围

集合可用来定义要匹配的一个或多个字符。例如，下面的集合将匹配数字0到9：

[0-9]

范围不一定只是数值的，[a-z]匹配任意字母字符。

匹配特殊字符

正则表达式语言由具有特定含义的特殊字符构成。我们已经看到.、[]、|和-等，还有其他一些字符。如果你需要匹配这些字符，应该使用转义为了匹配特殊字符，必须用\为前导。\\-表示查找-，\\.表示查找.。

为了匹配反斜杠（\）字符本身，需要使用\\\。

匹配字符类

存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作，可以使用预定义的字符集，称为字符类（character class）。下表列出字符类以及它们的含义。

类	说明
[:alnum:]	任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	空格和制表（同[\t]）
[:cntrl:]	ASCII控制字符（ASCII 0到31和127）
[:digit:]	任意数字（同[0-9]）
[:graph:]	与[:print:]相同，但不包括空格
[:lower:]	任意小写字母（同[a-z]）
[:print:]	任意可打印字符
[:space:]	包括空格在内的任意空白字符（同[\f\n\r\t\v]）
[:upper:]	任意大写字母（同[A-Z]）
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:xdigit:]	任意十六进制数字（同[a-fA-F0-9]）

匹配多个实例

目前为止使用的所有正则表达式都试图匹配单次出现。如果存在一个匹配，该行被检索出来，如果不存在，检索不出任何行。但有时需要对匹配的数目进行更强的控制。例如，你可能需要寻找所有的数，不管数中包含多少数字，或者你可能想寻找一个单词并且还能够适应一个尾随的s（如果存在），等等。

重复元字符

元字符	说明
*	0个或多个匹配
+	1个或多个匹配（等于{1,}）
？	0个或1个匹配（等于{0,1}）
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围（m不超过255）

【示例】

\\([0-9] sticks?\\)

\\(匹配)，[0-9]匹配任意数字，sticks?匹配stick和sticks（s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现），``\`)匹配)。

【示例】匹配连在一起的任意4位数字。

[[:digit:]]{4}

定位符

为了匹配特定位置的文本，需要使用下表列出的定位符。

元字符	说明
^	文本开始
$	文本结尾
[[:<:]]	词的开始
[[:>:]]	词的末尾

【示例】想找出以一个数（包括以小数点开始的数）开始的所有产品

^[0-9\\.]

^的双重用途： ^有两种用法。在集合中（用[和]定义），用它来否定该集合，否则，用来指串的开始处。