正则表达式
bitscn.com mysql学习足迹记录07--数据过滤--用正则表达式进行检索
本文用到的检索数据
mysql> select prod_name from products -> order by prod_name;+----------------+| prod_name |+----------------+| .5 ton anvil || 1 ton anvil || 2 ton anvil || bird seed || carrots || detonator || fuses || jetpack 1000 || jetpack 2000 || oil can || safe || sling || tnt (1 stick) || tnt (5 sticks) |+----------------+14 rows in set (0.00 sec)
1.基本字符匹配
eg: mysql> select prod_name from products -> where prod_name regexp '1000' #匹配1000 -> order by prod_name;+--------------+| prod_name |+--------------+| jetpack 1000 |+--------------+1 row in set (0.00 sec) eg: mysql> select prod_name from products -> where prod_name regexp '.000' #'.'表示匹配任意一个字符 -> order by prod_name;+--------------+| prod_name |+--------------+| jetpack 1000 || jetpack 2000 |+--------------+2 rows in set (0.00 sec)
2.进行or匹配
为了搜索n个串之一，使用 ‘|’
eg: mysql> select prod_name from products -> where prod_name regexp '1000 | 2000' -> order by prod_name;+--------------+| prod_name |+--------------+| jetpack 1000 || jetpack 2000 |+--------------+2 rows in set (0.00 sec)
3.匹配几个字符之一
*匹配特定的单字符，可以通过指定一组【】括起来的字符来完成
eg: mysql> select prod_name from products -> where prod_name regexp '[123] ton' -> order by prod_name;+-------------+| prod_name |+-------------+| 1 ton anvil || 2 ton anvil |+-------------+2 rows in set (0.00 sec) 等效于： mysql> 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 |+-------------+2 rows in set (0.00 sec)
4.否定一个字符集‘^'
eg: mysql> select prod_name from products -> where prod_name regexp '[^123] ton' -> order by prod_name;+--------------+| prod_name |+--------------+| .5 ton anvil |+--------------+1 row in set (0.00 sec)
5.匹配范围【n-m】
eg: mysql> 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 |+--------------+3 rows in set (0.00 sec)
注：以下操作所用到的表格数据
mysql> select vend_name from vendors order by vend_name;+----------------+| vend_name |+----------------+| acme || anvils r us || furball inc. || jet set || jouets et ours || lt supplies |+----------------+6 rows in set (0.00 sec)
6.匹配特殊字符，需用//为前导，即转义字符
*mysql要求两个反斜杠（mysql自己解释一个，正则表达式库解释另一个）
匹配'.'
eg: mysql> select vend_name from vendors -> where vend_name regexp '.' #未用转义字符，所以不是期望的结果 -> order by vend_name;+----------------+| vend_name |+----------------+| acme || anvils r us || furball inc. || jet set || jouets et ours || lt supplies |+----------------+6 rows in set (0.00 sec) 正确的应为： mysql> select vend_name from vendors -> where vend_name regexp '//.' -> order by vend_name;+--------------+| vend_name |+--------------+| furball inc. |+--------------+1 row in set (0.00 sec)
#以下7，8，9，10列出的仅作参考，无需记忆
7.匹配字符类
[:alnum:] ==> [a-za-z0-9]
[:alpha:] ==> [a-za-z]
[:blank:] ==>空格和制表符[//t]
[:cntrl:] ==>ascii控制字符(ascii0到31和127）
[:digit:] ==>[0-9]
[:graph:] ==>与[:print:]相同，但不包括空格
[:lower:] ==>[a-z]
[:punct:] ==>即不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] ==>包括空格在内的任意空白字符[//f//n//r//t//v]
[:upper:] ==>[a-z]
[:xdigit:]==>任意十六进制数[a-fa-f0-9]
[:print:] ==>任意可打印字符
8.空白元字符
//f ==>换页
//r ==>回车
//v ==>纵向制表
9.匹配多个实例
重复元字符
* ==> 0个或多个匹配
+ ==> 1个或多个匹配（等于{1,}）
? ==> 0个或1个匹配（等于{0,1}）
{n} ==> 指定数目的匹配
{n,} ==> 不小于指定数目的匹配
{n,m} ==> 匹配数目的范围（m select prod_name from products -> where prod_name regexp '//([0-9] sticks?//) ' #'?'匹配它前面的任何字符的0次或1次出现 -> order by prod_name; +----------------+| prod_name |+----------------+| tnt (1 stick) || tnt (5 sticks) |+----------------+2 rows in set (0.00 sec) mysql> select prod_name from products -> where prod_name regexp '[[:digit:]]{4}' #匹配连在一起的任意四位数字 -> order by prod_name;+--------------+| prod_name |+--------------+| jetpack 1000 || jetpack 2000 |+--------------+2 rows in set (0.00 sec)
10.定位符
*定位元字符
^ ==> 文本的开始
$ ==> 文本的结尾
[[: 词的开始
[[:>:]] ==> 词的结尾
eg: mysql> select prod_name from products -> where prod_name regexp '^[0-9//.]' #'^'定位到串开头，[0-9//.]表示只有在'.'或任一数字为 -> order by prod_name; #串中的第一个字符，才匹配它+--------------+| prod_name |+--------------+| .5 ton anvil || 1 ton anvil || 2 ton anvil |+--------------+3 rows in set (0.00 sec)
11.'^'的双重用途：在集合'[]'中用来否定集合，否则，用来指串的开始处
bitscn.com