MySQL之DQL语句（三）

• LIKE操作符：

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

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

谓词：操作符何时不是操作符？答案是在它作为谓词（predicate）时。从技术上说，LIKE是谓词而不是操作符。虽然最终的结果是相同的，但应该对此术语有所了解。

• 百分号（%）通配符：

SELECT prod_id,prod_name FROM products WHERE prod_name LIKE 'jet%';
复制

区分大小写：根据MySQL的配置方式，搜索可以是区分大小写的。如果区分大小写，'jet%'与JetPack 1000将不匹配。

SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '%anvil%';
复制

SELECT prod_name FROM products WHERE prod_name LIKE 's%e';
复制

• 下划线（_）通配符：

SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
复制

• 使用通配符的技巧：

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

• 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

• 基本字符匹配：
  

检索列prod_name包含文本1000的所有行：

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
复制

除关键字LIKE被REGEXP替代外，这个语句看上去非常像LIKE的语句。它告诉MySQL：REGEXP后所跟的东西作为正则表达式处理。为什么要费力地使用正则表达式？上面这个例子正则表达式确实没有带来太多好处（可能还会降低性能），不过，考虑下面的例子：

SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;
复制

这里使用了正则表达式.000。.是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符。所以，1000和2000都匹配且返回。

LIKE与REGEXP的差别：

第二条语句没有返回数据，第一条语句返回一行数据。为什么？LIKE匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行会被返回，这是一个非常重要的差别。REGEXP也可以用来匹配整个列，使用^和$定位符（anchor）即可，后面会介绍。

匹配不区分大小写：MySQL中的正则表达式匹配（自版本3.23.4后）不区分大小写。为区分大小写，可以使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。

• 进行OR匹配：
  

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

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
复制

语句中使用了正则表达式1000|2000。|为正则表达式的OR操作符，它表示匹配其中之一，因此1000和2000都匹配并返回。使用|从功能上类似于SELECT语句中使用OR语句，多个OR条件可以并入单个正则表达式。如'1000|2000|3000'将匹配1000或2000或3000。

• 匹配几个字符之一：
  

匹配任何单一字符。但是，如果你只想匹配特定的字符，怎么办？可通过指定一组用[和]括起来的字符来完成。

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
复制

[123]定义一组字符，它的含义是匹配1或2或3，因此，返回1 ton和2 ton（因为没有3 ton）。

[]是另一种形式的OR语句。事实上，正则表达式[123] Ton为[1|2|3] Ton的缩写，也可以使用后者。但是，一定要使用[]来定义OR语句进行查找。看下面的这个例子：

这并不是期望的输出，两个要求的行被检索出来，但是还检索出了另外的3行。之所以这样是由于MySQL假定你的意思，'1'或'2'或'3 Ton'。除非把字符|括在一个集合中，否则将它用于整个串。

字符集也可以被否定，即取反，它们将匹配除指定字符外的任何东西。为否定一个字符集，在集合开始处放一个^即可。（尽管[123]匹配字符1、2或3，但[^123]却匹配除这些字符之外的任何东西。）

• 匹配范围：
  

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

[0123456789]

为简化这种类型的集合，可使用-来定义一个范围：（等同于上述数字列表）

[0-9]

范围不限于完整的集合，[1-3]和[6-9]也是合法的范围。此外，范围不一定是数值，[a-z]匹配任意字母字符。

SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
复制

• 匹配特殊字符：
  

匹配特殊字符，必须用\\为前导。\\-表示查找-，\\.表示查找.。

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
复制

这种处理就是所谓的转义（escaping），正则表达式内具有特殊意义的所有字符必须以这种方式转义。这包括.、|、[]以及迄今为止使用过的其他特殊字符。

\\也用来引用元字符（具有特殊含义的字符）：

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

\或\\?：多数正则表达式实现使用使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但MySQL要求两个反斜杠（MySQL字节解释一个，正则表达式库解释另一个）。

• 匹配字符类：
  

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

• 匹配多个实例：

目前为止使用的所有正则表达式都试图匹配单次出现。如果存在一个匹配，该行被检索出来，如果不存在，检索不出任何行。但有时需要对匹配的数目进行更强的控制。可以使用正则表达式重复元字符来完成。

举几个例子：

SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;
复制

正则表达式\\([0-9] sticks?\\)需要解释一下：\\(匹配)，[0-9]匹配任意数字（这个例子中为1和5），sticks?匹配stick和sticks（s后的?的s可选，因为?匹配它前面任何字符的0次或1次出现，\\)匹配）。没有?，匹配stick和sticks会非常困难。

SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;
复制

[:digit:]匹配任意数字，因而它为数字的一个集合，{4}确切地要求它前面的字符（任意）数字出现4次，所以[[:digit:]]{4}匹配连在一起的任意4位数字。需要注意的是，在使用正则表达式时，编写某个特殊的表达式几乎总是有不止一种办法。上面的例子可以写为：

SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;
复制

• 定位符：
  

目前为止的所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本，需要使用定位符。

如果你想找一个数（包含以小数点开始的数）开始的所有产品，怎么办？简单搜索[0-9\\.]（或[[:digit:]\\.]）不行，因为它将在文本内任意位置查找匹配。解决办法是使用^定位符。

SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;
复制

^匹配串的开始。因此，^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们。没有^，则还要多检索出4个别的行（那些中间有数字的行）。

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

使REGEXP起类似LIKE的作用：LIKE和REGEXP的不同在于，LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与LIKE一样。

end