MySQL基础之用正则表达式进行搜索

使用MySQL正则表达式

正则表达式的作用是匹配文本, 将一个模式(正则表达式)与一个文本串进行比较, MySQL用WHERE子句对正则表达式提供了初步的支持, 允许你使用正则表达式, 过滤SELECT检索出的数据。

基本字符匹配

MariaDB [crashcourse]> SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| JP1000  | JetPack 1000 |
+---------+--------------+
1 row in set (0.002 sec)

MariaDB [crashcourse]> 

这条语句告诉MySQL:REGEXP后所跟的东西作为正则表达式处理(正则表达式可能还会降低性能)

LIKE与REGEXP: 在LIKE与REGEXP之间有一个重要的差别

LIKE匹配整个列, 如果被匹配的文本仅在列值中出现, LIKE将不会找的它, 相应的行也不被返回(除非使用通配符)

而REGEXP在列值内进行匹配, 如果被匹配的文本在列值中出现, REGEXP将会找到它, 相应的行将被返回

区分与不区分大小写

MySQL中的正则表达式不区分大小写。为区分大小写, 可使用BINARY关键字, 如

WHERE prod_name REGEXP BINARY 'JetPack .1000'

进行OR匹配

为搜索两个串, 使用|

MariaDB [crashcourse]> SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| JP1000  | JetPack 1000 |
| JP2000  | JetPack 2000 |
+---------+--------------+
2 rows in set (0.001 sec)

MariaDB [crashcourse]> 

使用|从功能上类似于在SELECT语句中使用OR语句, 多个OR条件可并入单个正则表达式

匹配几个字符之一

如果只想匹配特定的字符, 可通过指定一组用[和]括起来的字符来完成。

MariaDB [crashcourse]> SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
+---------+-------------+
| prod_id | prod_name   |
+---------+-------------+
| ANV02   | 1 ton anvil |
| ANV03   | 2 ton anvil |
+---------+-------------+
2 rows in set (0.001 sec)

MariaDB [crashcourse]> 

[123]定义一组字符, 它的意思是匹配1或2或3, 因此, 1 ton和2 ton都匹配且返回。

[]是另一种形式的OR语句。事实上, 正则表达式[123] Ton为[1|2|3]Ton的缩写, 也可使用后者。但是, 需要用[]来定义OR语句查找什么

字符集合也可以被否定, 即, 它们将被匹配除指定字符外的任何东西。为否定一个字符集, 在集合的开始处放置一个^即可。

匹配范围

集合可用来匹配一个或多个字符。可使用-来定义一个范围。

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

MariaDB [crashcourse]> SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
| ANV02   | 1 ton anvil  |
| ANV03   | 2 ton anvil  |
+---------+--------------+
3 rows in set (0.001 sec)

MariaDB [crashcourse]> 

匹配特殊字符

为了匹配特殊字符, 必须使用\为前导, \-表查找-, \.表示查找.

MariaDB [crashcourse]> SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '\.' ORDER BY prod_name;
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
+---------+--------------+
1 row in set (0.001 sec)

MariaDB [crashcourse]> 

这种处理就是所谓的转义(escaping)。正则表达式内具有特殊意义的所有字符都必须以这种方式转义。

\也用来引用元字符(具有特殊含义的字符)

匹配

为了匹配斜杠()字符本身, 需要使用\

匹配字符类

可以使用预定义的字符集可以找出经常使用的数字、所有字母字符或所有数字字母字符

匹配多个实例

如果需要对匹配的数目进行更强的控制, 那么可以使用元字符进行实现

MariaDB [crashcourse]> SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '\([0-9] sticks?\)' ORDER BY prod_name;
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
2 rows in set (0.000 sec)

MariaDB [crashcourse]> 

在使用正则表达式的时候, 编写某个特殊的表达式几乎总是有不止一种方法。

定位符

为了匹配特定位置的文本, 需要使用到定位符

MariaDB [crashcourse]> SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '^[0-9\.]' ORDER BY prod_name;
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
| ANV02   | 1 ton anvil  |
| ANV03   | 2 ton anvil  |
+---------+--------------+
3 rows in set (0.000 sec)

MariaDB [crashcourse]> 

^的双重用途

^用两种用法。在集合中(用[和]定义), 用它来否定该集合。否则用来指串开始处。

使REGEXP起类似LIKE的作用

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