WHERE子句内使用正则表达式REGEXP更好地控制数据过滤

• 正则表达式介绍
• 使用MySQL正则表达式 REGEXP
  • 仅支持正则表达式的一个子集
  • 基本字符匹配
  • LIKE和REGEXP的区别
  • 匹配不区分大小写
  • 进行OR匹配 ｜
  • 匹配几个字符之一
  • 匹配范围
  • 匹配特殊字符（转义字符\）
    • 匹配\
    • \或\？
  • 匹配字符类
  • 匹配多个实例
    • 举例
  • 匹配0-9任意数字+stick+s可出现0次或1次
    
  • 定位符
    • 举例
    • ^的双重用途
    • REGEXP类似LIKE的作用
    • 简单的正则表达式测试

正则表达式介绍

正则表达式使用来匹配文本的特殊的串（字符集合）
例如从文本中提取电话号码、查找名字中间有数字的文件、文本块中所有重复的单词、替换一个页面的RUL为URL的实际HTML链接。
正则表达式用正则表达式语言来建立，正则表达式语言是用来完成匹配匹配文本的一种特殊语言

使用MySQL正则表达式 REGEXP

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

仅支持正则表达式的一个子集

MySQL仅支持多数正则表达式实现的一个很小的子集

基本字符匹配

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

SELECT
	prod_name 
FROM
	products 
WHERE
	prod_name REGEXP "1000" 
ORDER BY
	prod_name;

关键字REGEXP告诉MySQL其后所跟的东西作为正则表达式处理（与1000匹配的正则表达式）

SELECT
	prod_name 
FROM
	products 
WHERE
	prod_name REGEXP ".000" 
ORDER BY
	prod_name;

其正则表达式为.000。其中.表示匹配任意一个字符，所以1000和2000都匹配返回

LIKE和REGEXP的区别

• LIKE匹配的是整个列的值

SELECT
	prod_name 
FROM
	products 
WHERE
	prod_name LIKE "1000" 
ORDER BY
	prod_name;

因为LIKE匹配的是整个列的值，且没有使用通配符。即使搜索的文本在列值中出现过也不匹配，所以没有返回的行

• REGEXP匹配的是列值内的文本搜索
  如果被匹配的文本在列值中出现REGEXP将会找到它。相应的行会返回。也可以使用^和$定位符匹配整个列达到和LIKE相似的效果

匹配不区分大小写

MySQL重的正则表达式不区分大小写，但恶意使用BINARY关键字区分大小写

• 默认不区分大小写

SELECT
	prod_name 
FROM
	products 
WHERE
	prod_name REGEXP "jetpack .000" 
ORDER BY
	prod_name;

• 在REGEXP关键字后加BINARY区分大小写，无匹配项返回

SELECT
	prod_name 
FROM
	products 
WHERE
	prod_name REGEXP BINARY "jetpack .000" 
ORDER BY
	prod_name;

进行OR匹配 ｜

为搜索两个串之一，使用｜，类似于OR语句，多个OR条件可并入单个正则表达式

SELECT
	prod_name 
FROM
	products 
WHERE
	prod_name REGEXP "1000|2000|3000" 
ORDER BY
	prod_name;

匹配几个字符之一

匹配任何单一字符。只想匹配特定的字符可通过指定一组用[]括起来的字符来完成

SELECT
	prod_name 
FROM
	products 
WHERE
	prod_name REGEXP "[123] TON" 
ORDER BY
	prod_name;

匹配1或2或3，且因为没有加BINARY关键字，所以忽略TON的大小写

• 也可以使用[1|2|3] TON，但是需要使用[]来定义OR语句查找什么。因为如果不加[]变成1|2|3 TON 则会匹配1或2或3 TON文本内容。会显示如下结果
  
• 否定字符集
  ^{字符集也可以被否定，在集合的开始处放置}即可，即[^1|2|3]或[^123]，表示匹配出了123字符之外的任何东西，其结果如下
  

匹配范围

集合可以用来定义要匹配的一个或多个字符
[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;

匹配1-5 Ton的行，由于正则表达式默认不是列名值全部等于，只要文本部分内容满足即可，所以返回3行

匹配特殊字符（转义字符\）

由于正则表达式的语言具有特殊含义的特殊字符构成. [] | _ 等。如果要匹配这些特殊字符必须用\为前导。
即\表示查找 ，\.表示查找.

SELECT
	vend_name 
FROM
	vendors 
WHERE
	vend_name REGEXP "\\." 
ORDER BY
	vend_name;

表示查找vend_name列中含有.的行

正则表达式内具有特殊意义的所有字符都必须以这种方式转义
\也用来引用元字符（具有特殊含义的字符）

• \f 换页
• \n 换行
• \r 回车
• \t 制表
• \v 纵向制表

匹配\

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

\或\？

多数正则表达死实现使用单个反斜杠\转译特殊字符。
但MySQL要求用两个反斜杠(MySQL自己解释一个，正则表达式解释另一个)

匹配字符类

使用预定义的字符集，其含义如下

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

匹配多个实例

对匹配的数目进行更强的控制，可使用重复元字符来完成

• * 0个或多个匹配
• + 1个或多个匹配等价于{1,}
• ? 0个或多个匹配 等价于{0,1}
• 指定数目的匹配
• {n,} 不少于指定数目的匹配
• {n,m} 匹配数目的范围（m不超过255）

举例

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

匹配0-9任意数字+stick+s可出现0次或1次

SELECT
	prod_name 
FROM
	products 
WHERE
	prod_name REGEXP "[[:digit:]]{4}"

匹配4连的任意数字

其等价于

SELECT
	prod_name 
FROM
	products 
WHERE
	prod_name REGEXP "[0-9][0-9][0-9][0-9]"

定位符

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

• ^ 文本的开始
• $ 文本的结尾
• [[:<:]] 词的开始
• [[:>:]] 词的结尾

举例

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

匹配文本在开始时为数组或.的行

^的双重用途

1.在集合中[]用来否定取反
2.定位串符的开始位置

REGEXP类似LIKE的作用

LIKE匹配整个串，REGEXP匹配子串。
利用定位符，通过^开始每个表达式，$结束每个表达式。使得REGEXP的作用与LIKE一样。LIKE也可以加%来匹配子串

简单的正则表达式测试

可以在不使用数据库表的情况下用SELECT来测试正则表达式，REGEXP检查总是返回0或1.可以用带文字串的REGEXP来测试，例如

SELECT
	"hello" REGEXP "[0-9]";

返回0

SELECT
	"hello123" REGEXP "[0-9]";

返回1