mysql基础(3)--使用正则表达式搜索

1. 检索一段文本

检索列prod_name中国包含文本1000的所有行(假设有两个值为’JACK 1000’和’JACK 2000’的数据)

SELECT prod_name FROM prod_name REGEXP '1000';
REGEXP之后的内容作为正则表达式处理

假如我们使用like,

SELECT prod_name FROM prod_name LIKE '1000';

这样是匹配不到值的,我们必须在1000的前后加上%,像这样:

SELECT prod_name FROM prod_name LIKE '%1000%';

注意:LIKE 和 REGEXP的区别

• LIKE 匹配整个列。如果被匹配的文本在列值中出现, LIKE 将不会找到它,相应的行也不被返回(除非使用
  通配符)。而 REGEXP 在列值内进行匹配,如果被匹配的文本在列值中出现, REGEXP 将会找到它,相应的行将被返回。
• REGEXP若果想要匹配整个列值,就需要用到^和$定位符,{如}^{\prime 1}000$’
• MYSQL中的正则表达式匹配不区分大小写,若要区分大小写使用 BINARY 关键字如:
  WHERE prod_name REGEXP BINARY ‘JACK 1000’

2. 使用OR匹配搜索名字中包含 1000 或 2000 的值(如果想要多个或就使用多个 | ,就如同使用or一样)

SELECT prod_name FROM prod_name REGEXP '1000|2000';

3. 匹配几个字符之一

假如我们有 ‘1tom’,‘2tom’,‘tom1’,‘tom2’,‘tom3’几个名字,我们想要匹配’1tom’,‘2tom’

SELECT prod_name FROM products WHERE prod_name REGEXP '[12]tom';

使用了正则表达式 [12]tom,[12] 定义一组字符,它的意思是匹配 1 或 2 ,因此, 1tom 和 2tom 都匹配且返回
[] 是另一种形式的 OR 语句。事实上,正则表达式 [12]tom为 [1|2]tom 的缩写,也可以使用后者,但是,需要用 [] 来定义 OR 语句查找什么。为更好地理解这一点,请看下面的例子:

SELECT prod_name FROM products WHERE prod_name REGEXP '1|2tom';

找到的结果为’1tom’,‘2tom’,‘tom1’

分析: 这并不是期望的输出。两个要求的行被检索出来,但还检索出了另外1行。之所以这样是由于MySQL假定你的意思是 ‘1’ 或 ‘2tom’ 。除非把字符 | 括在一个集合中,否则它将应用于整个串。
小知识: 字符集合也可以被否定,即,它们将匹配除指定字符外的任何东西。为否定一个字符集,在集合的开始处放置一个 ^ 即可。因此,尽管 [123]匹配字符 1 、 2 或 3 ,但 [^123] 却匹配除这些字符外的任何东西。

4. 匹配范围

集合可用来定义要匹配的一个或多个字符。例如,下面的集合将匹配数字0到9:[0123456789],可以简化为[0-9]

SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5]tom';

结果 ‘1tom’,‘2tom’

5. 匹配特殊字符

正则表达式语言由具有特定含义的特殊字符构成。我们已经看到 . 、[] 、| 和 - 等,还有其他一些字符。学过正则的我们都知道使用 \ 来进行转义,但是在sql中
要求使用\来进行转义(MySQL自己解释一个,正则表达式库解释另一个)

字符	说明
\f	换页
\n	换行
\r	回车
\t	制表
\v	纵向制表
\\\	一个反斜杠
\.	一个.

6. 匹配字符类

类	说明
[: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:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符(同[\f\n\r\t\v])
[:upper:]	任意大写字母(同[A-Z])
[:xdigit:]	任意十六进制数字(同[a-fA-F0-9])

在正则匹配中我们可以使用\d{4}来匹配4个连续的数字,但是在sql中我们就需要使用sql的[[:digit:]]{4},或者[0-9][0-9][0-9][0-9]
我们可以这么写sql语句

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

或者

SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]';

7. 匹配多个实例(重复元字符)

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

例:

SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] stricks?\\)';

分析
\\( 匹配(, [0-9] 匹配任意数字, sticks?匹配 stick 和 sticks ( s 后的 ? 使 s 可选,因为 ? 匹配它前面的任何字符的0次或1次出现), \) 匹配 ) 。没有 ? ,匹配 stick 和 sticks 会非常困难

8. 定位符

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

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