一、正则表达式介绍
正则表达式用来匹配文本的特殊的串(字符集合)。
二、使用MySQL正则表达式
1、MySQL仅仅支持多数正则表达式实现的一个很小的子集
2、LIKE匹配整个列值;而REGEXP匹配列值的子串
如果被匹配的文本在列值中出现,REGEXP将会找到它,相应的行将被返回。
mysql>select * from students;
+--------+-----------+
|number | name1|
+--------+-----------+
|1 | space|
|2 | warmspace |
+--------+-----------+
mysql>select name1 from students where name1 like 'space'; 全名匹配
+-------+
|name1 |
+-------+
|space |
+-------+
mysql>select name1 from students where name1 REGEXP 'space'; 子串匹配
+-----------+
|name1|
+-----------+
|space|
|warmspace |
+-----------+
3、默认MySQL中的正则表达式匹配不区分大小写,可使用BINARY关键字区分大小写
mysql> select name1 from students where name1 REGEXP 'SPACE';
+-----------+
| name1 |
+-----------+
| space |
| warmspace |
+-----------+
mysql> select name1 from students where name1 REGEXP BINARY 'SPACE';
Empty set (0.00 sec)
4、进行OR匹配:为搜索两个串之一,使用|
mysql> select * from students;
+--------+-----------+------+
| number | name1 | age |
+--------+-----------+------+
| 1 | space | 15 |
| 2 | warmspace | 34 |
| 3 | summerspace | 28 |
+--------+-----------+------+
2 rows in set (0.00 sec)
mysql> select * from students where age REGEXP '1|4';
+--------+-----------+------+
| number | name1 | age |
+--------+-----------+------+
| 1 | space | 15 |
| 2 | warmspace | 34 |
+--------+-----------+------+
5、匹配几个字符之一:匹配任何单一字符
[]是另一种形式的OR语句。'005-[123]'与’005-[1|2|3]’是等价的。
mysql> select * from students;
+--------+-------------+------+-----------------+
| number | name1 | age | id |
+--------+-------------+------+-----------------+
| 1 | space | 15 | 101-02-01-005-2 |
| 2 | warmspace | 34 | 101-02-01-005-1 |
| 3 | summerspace | 28 | 101-02-01-005-3 |
| 4 | abc | 20 | 101-02-01-005-4 |
+--------+-------------+------+-----------------+
mysql> select name1,id from students where id REGEXP '005-[123]';
+-------------+-----------------+
| name1 | id |
+-------------+-----------------+
| space | 101-02-01-005-2 |
| warmspace | 101-02-01-005-1 |
| summerspace | 101-02-01-005-3 |
+-------------+-----------------+
匹配除给定字符外的字符:
mysql> select name1,id from students where id REGEXP '005-[^123]';
+-------+-----------------+
| name1 | id |
+-------+-----------------+
| abc | 101-02-01-005-4 |
+-------+-----------------+
6、可使用-来定义一个范围
如[1-9],[a-b]。
mysql> select name1,id from students where id REGEXP '005-[1-3]';
+-------------+-----------------+
| name1 | id |
+-------------+-----------------+
| space | 101-02-01-005-2 |
| warmspace | 101-02-01-005-1 |
| summerspace | 101-02-01-005-3 |
+-------------+-----------------+
7、为了匹配特殊字符,必须用\\为前导
例如\\-,\\.等。这种处理即转义(escaping)。多数正则表达式实现使用单个反斜杠转义特殊字符,但MYSQL要求两个(MYSQL自己解释一个,正则表达式库解释另一个)。\\也用来引用元字符(具有特殊含义的字符),如下表:元字符说明
\\f换页
\\v纵向制表
8、为了更方便工作,可以使用预定义的字符集,称为字符类(character class)
mysql> select name1,age from students where age REGEXP '[[:digit:]]';
+-------------+------+
| name1 | age |
+-------------+------+
| space | 15 |
| warmspace | 34 |
| summerspace | 28 |
| abc | 20 |
+-------------+------+类说明
[: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])
9、使用正则表达式重复元字符匹配多个实例
mysql> select * from students;
+--------+-------------+------+-----------------+
| number | name1 | age | id |
+--------+-------------+------+-----------------+
| 1 | space | 15 | 101-02-01-005-2 |
| 2 | warmspace | 34 | 101-02-01-005-1 |
| 3 | summerspace | 28 | 101-02-01-005-3 |
| 4 | abc | 20 | 101-02-01-005-4 |
+--------+-------------+------+-----------------+
mysql> select * from students where name1 REGEXP 'm{2}';
+--------+-------------+------+-----------------+
| number | name1 | age | id |
+--------+-------------+------+-----------------+
| 3 | summerspace | 28 | 101-02-01-005-3 |
+--------+-------------+------+-----------------+元字符说明
*0个或多个匹配
+1个或多个匹配(等于{1,})
?0个或1个匹配(等于{0,1})
{n}指定数目的匹配
{n,}不少于指定数目的匹配
{n,m}匹配数目的范围(m不超过255)
10、定位符(为了匹配特定位置的文本)
mysql> select * from students where name1 REGEXP 'c$';
+--------+-------+------+-----------------+
| number | name1 | age | id |
+--------+-------+------+-----------------+
| 4 | abc | 20 | 101-02-01-005-4 |
+--------+-------+------+-----------------+元字符说明
^文本的开始
$文本的结尾
[[:<:>
[[:>:]]词的结尾
11、简单的正则表达式测试:可以在不使用数据库表的情况下用SELECT来测试正则表达式
REGEXP检查总是返回0或1(匹配)。
mysql> select 'hello' regexp '[0-9]';
+------------------------+
| 'hello' regexp '[0-9]' |
+------------------------+
| 0 |
+------------------------+