SQL LIKE 操作符的用法

本文介绍什么是通配符、如何使用通配符，以及怎样使用 SQL LIKE 操作符进行通配搜索，以便对数据进行复杂过滤。

一、SQL LIKE 操作符

SQL AND、OR、IN 和 NOT 操作符介绍的所有操作符都是针对已知值进行过滤的。不管是匹配一个值还是多个值，检验大于还是小于已知值，或者检查某个范围的值，其共同点是过滤中使用的值都是已知的。

但是，这种过滤方法并不是任何时候都好用。例如，怎样搜索产品名中包含文本 bean bag 的所有产品？用简单的比较操作符肯定不行，必须使用通配符。利用通配符，可以创建比较特定数据的搜索模式。

在这个 SQL LIKE 例子中，如果你想找出名称包含 bean bag 的所有产品，可以构造一个通配符搜索模式，找出在产品名的任何位置出现 bean bag 的产品。

通配符（wildcard）

用来匹配值的一部分的特殊字符。

搜索模式（search pattern）

由字面值、通配符或两者组合构成的搜索条件。

通配符本身实际上是 SQL WHERE 子句中有特殊含义的字符，SQL 支持几种通配符。为在搜索子句中使用通配符，必须使用 SQL LIKE 操作符。

LIKE 指示 DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。

谓词（predicate）

操作符何时不是操作符？答案是，它作为谓词时。

从技术上说，SQL LIKE 是谓词而不是操作符。虽然最终的结果是相同的，但应该对此术语有所了解，以免在 SQL 文献或手册中遇到此术语时不知所云。

通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。

1.1 百分号（%）通配符

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

例如，为了找出所有以词 Fish 起头的产品，可写以下的 SELECT 语句：

1
2
3
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

输出：

1
2
3
prod_id     prod_name
-------     ------------------
BNBG01      Fish bean bag toy

此例子使用了搜索模式 'Fish%'。在执行这条子句时，将检索任意以 Fish 起头的词。% 告诉 DBMS 接受 Fish 之后的任意字符，不管它有多少字符。

说明：区分大小写

根据 DBMS 的不同及其配置，搜索可以是区分大小写的。如果区分大小写，则 'fish%' 与 Fish bean bag toy 就不匹配。

通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，它们位于模式的两端：

1
2
3
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

输出：

1
2
3
4
5
prod_id      prod_name
--------     --------------------
BNBG01       Fish bean bag toy
BNBG02       Bird bean bag toy
BNBG03       Rabbit bean bag toy

搜索模式 '%bean bag%' 表示匹配任何位置上包含文本 bean bag 的值，不论它之前或之后出现什么字符。

通配符也可以出现在搜索模式的中间，虽然这样做不太有用。下面的例子找出以 F 起头、以 y 结尾的所有产品：

1
2
3
SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y';

提示：根据部分信息搜索电子邮件地址

有一种情况下把通配符放在搜索模式中间是很有用的，就是根据邮件地址的一部分来查找电子邮件，例如 WHERE email LIKE 'b%@forta.com'。

需要特别注意，除了能匹配一个或多个字符外，% 还能匹配 0 个字符。% 代表搜索模式中给定位置的 0 个、1 个或多个字符。

说明：请注意后面所跟的空格

有些 DBMS 用空格来填补字段的内容。

例如，如果某列有 50 个字符，而存储的文本为 Fish bean bag toy（17 个字符），则为填满该列需要在文本后附加 33 个空格。

这样做一般对数据及其使用没有影响，但是可能对上述 SQL 语句有负面影响。

子句 WHERE prod_name LIKE 'F%y' 只匹配以 F 开头、以 y 结尾的 prod_name。

如果值后面跟空格，则不是以 y 结尾，所以 Fish bean bag toy 就不会检索出来。

简单的解决办法是给搜索模式再增加一个 % 号：'F%y%' 还匹配 y 之后的字符（或空格）。更好的解决办法是用函数去掉空格。请参阅 SQL 如何使用函数处理数据。

注意：请注意 NULL

通配符 % 看起来像是可以匹配任何东西，但有个例外，这就是 NULL。

子句 WHERE prod_name LIKE '%' 不会匹配产品名称为 NULL 的行。

1.2 下划线（_）通配符

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

说明：DB2 通配符

DB2 不支持通配符 _。

举一个例子：

1
2
3
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

说明：请注意后面所跟的空格

与上例一样，可能需要给这个模式添加一个通配符。

输出：

1
2
3
4
prod_id      prod_name
--------     --------------------
BR02         12 inch teddy bear
BR03         18 inch teddy bear

这个 WHERE 子句中的搜索模式给出了后面跟有文本的两个通配符。

结果只显示匹配搜索模式的行：第一行中下划线匹配 12，第二行中匹配 18。

8 inch teddy bear 产品没有匹配，因为搜索模式要求匹配两个通配符而不是一个。

对照一下，下面的 SELECT 语句使用 % 通配符，返回三行产品：

1
2
3
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '% inch teddy bear';

输出：

1
2
3
4
5
prod_id      prod_name
--------     --------------------
BR01         8 inch teddy bear
BR02         12 inch teddy bear
BNR3         18 inch teddy bear

与 % 能匹配多个字符不同，_ 总是刚好匹配一个字符，不能多也不能少。

1.3 方括号（[]）通配符

方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

说明：并不总是支持集合

与前面描述的通配符不一样，并不是所有 DBMS 都支持用来创建集合的 []。

微软的 SQL Server 支持集合，但是 MySQL，Oracle，DB2，SQLite 都不支持。为确定你使用的 DBMS 是否支持集合，请参阅相应的文档。

例如，找出所有名字以 J 或 M 起头的联系人，可进行如下查询：

1
2
3
4
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

输出：

1
2
3
4
5
cust_contact
-----------------
Jim Jones
John Smith
Michelle Green

此语句的 WHERE 子句中的模式为 '[JM]%'。这一搜索模式使用了两个不同的通配符。[JM] 匹配方括号中任意一个字符，它也只能匹配单个字符。因此，任何多于一个字符的名字都不匹配。[JM]之后的 % 通配符匹配第一个字符之后的任意数目的字符，返回所需结果。

此通配符可以用前缀字符 ^（脱字号）来否定。例如，下面的查询匹配以 J 和 M 之外的任意字符起头的任意联系人名（与前一个例子相反）：

1
2
3
4
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;

当然，也可以使用 NOT 操作符得出类似的结果。^ 的唯一优点是在使用多个 WHERE 子句时可以简化语法：

1
2
3
4
SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

二、使用通配符的技巧

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

不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

总之，通配符是一种极其重要和有用的搜索工具，以后我们经常会用到它。

三、小结

本文介绍了什么是通配符，如何在 WHERE 子句中使用 SQL 通配符，还说明了通配符应该细心使用，不要使用过度。

（完）