1、正则表达式REGEXP和LIKE的区别
SELECT prod_name
FORM products
WHERE prod_name LIKE '1000'
ORDER BY prod_name;
SELECT prod_name
FORM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
第一段代码不会返回结果,而第二段代码会返回
prod_name
JetPack 1000
因为第一段代码用的是LIKE匹配,但是它又没有使用通配符,所以他匹配的是整个列值,即JetPack 1000,结果是不匹配的。而第二段代码使用的是正则表达式匹配,他会在整个列值之下再度匹配,即匹配JetPack 1000中的1000,所以有结果返回。
2、正则表达式大小写匹配
一般不区分大小写,要区分大小写,使用BINARY关键字,如
WHERE prod_name REGEXP BINARY 'JetPack .000'。
3、进行OR匹配
正则表达式中使用OR匹配,使用操作符|,如下所示
输入:SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;
输出:
prod_name
JetPack 1000
JetPack 2000
注:操作符|可以多次使用,如 '1000|2000|3000'。
4、匹配几个字符之一
如果你只想匹配特定字符,可以使用操作符[]。
输入:SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] ton'
ORDER BY prod_name;
输出:
prod name
1 ton anvil
2 ton anvil
分析:[123]是匹配1或2或3.
输入:SELECT prod_name
FROM products
WHERE prod_name REGEXP '1|2|3 ton'
ORDER BY prod_name;
输出:prod_name
1 ton anvil
2 ton anvil
JetPack 1000
JetPack 2000
TNT (1 stick)
分析:输出不符合期望,原因是'1|2|3 ton' 意味着 匹配 1 或 2 或 3 ton,|作用于整个串,除非把它用[]扩起来。
注:字符集合[]也可以被否定,比如[^123]表示匹配除1、2、3外的所有字符。
5、匹配范围
使用集合可以进行范围匹配,如[1-5]、[6-9]、[a-z]等。
输入:SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;
输出:
prod_name
.5 ton anvil
1 ton anvil
2 ton anvil
6、匹配特殊字符
要匹配正则表达式自身的字符,如[] | ^等,须加转义符\\ 。
输入:
SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY vend_name
输出:
vend_name
Furball Inc.
注:
1、转义符转义\本身,须写为\\\。正则表达式一般是使用一个转义符转义,但是MySQL自己还需要一个,所以是两个。
2、转义符和元字符结合:
\\f 换页
\\n 换行
\\r 回车
\\t 制表
\\v 纵向制表
7、匹配字符类
字符类就是预定义的字符集,如下:
[:alnum:]——任意字母和数字==[a-zA-Z0-9]
[:alpha:]——任意字符==[a-zA-Z]
[:blank:]——空格和制表==[\\t]
[:cntrl:]——ASCII控制字符(ASCII 0-31和127)
[:digit:]——任意数字==[0-9]
[:graph:]——任意可打印字符,但不包括空格
[:lower:]——任意小写字母==[a-z]
[:print:]——任意可打印字符
[:punct:]——既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]——包括空格在内的任意空白字符==[\\f\\n\\r\\t\\v](集合里的字符表示,字符集的其中一个)
[:upper:]——任意大写字母==[A-Z]
[:xdigit:] ——任意十六进制数字==[a-fA-F0-9]
8、匹配多个实例
正则表达式重复元字符:
* 0个或多个匹配
+ 1个或多个匹配=={1,}
? 0个或1个匹配=={0,1}
{n} 指定数目的匹配
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围(m不超过255)
输入:
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;
输出:
prod_name
TNT (1 stick)
TNT (5 sticks)
分析:?是约束它前面的字符,出现0或1次。
输入:
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}’
ORDER BY prod_name;
输出:
prod_name
JetPack 1000
JetPack 2000
分析:[:digit:]匹配任意一个数字,{4}预约它之前的字符出现4次,连在一起是匹配任意四位数字=='[0-9][0-9][0-9][0-9]'。
9、定位符
上面都是匹配任意位置的文本,如果要匹配特定位置的文本,需要使用定位符,如下:
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
输入:
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;
输出:
prod_name
.5 ton anvil
1 ton anvil
2 ton anvil
分析:^定位开头,[0-9\\.]匹配一个任意数字或者一个小数点。
注:^有双重用途:
1、放在集合里的开头部分,用于否定该集合。
2、放在集合外,用于定位在串的开头。
注:可以使用^和$来是REGEXP匹配整个串,从而起到与LIKE一样的作用。
10、简单的正则表达式测试
可以在不使用数据库表的情况下用SELECT关键字来测试正则表达式,REGEXP检查会返回0或1,即匹配和不匹配。
输入:
SELECT 'hello' REGEXP '[0-9]';
输出:
0
分析:因为不匹配。