MySQL正则表达式

                       **只能用来匹配字符串**

基本字符匹配

• regexp在这时与Like有点类似
• regexp的作用在于告诉MySQL：后面的是正则表达式

//Like与REGEXP的差别：Like在列值中进行匹配，完全相等的值出现Like不会返回，而REGEXP会进行返回，这是二者最主要的差别。mysql> select * from user where name like 'wang';+----+------+| id | name |+----+------+|  3 | wang |+----+------+1 row in set (0.00 sec)mysql> select * from user where name regexp 'wang';+----+----------+| id | name     |+----+----------+|  3 | wang     ||  7 | wanggggg || 73 | wang.    |+----+----------+3 rows in set (0.00 sec)

---

mysql> select * from user where name regexp 'wang';+----+----------+| id | name     |+----+----------+|  3 | wang     ||  7 | wanggggg |+----+----------+2 rows in set (0.00 sec)

进行OR匹配

mysql> select * from user where name regexp 'wang|user';+----+----------+| id | name     |+----+----------+|  1 | user1    ||  3 | wang     ||  4 | user4    ||  5 | user5    ||  7 | wanggggg |+----+----------+5 rows in set (0.00 sec)

匹配几个字符之一

• “[ ]”其实是另一种形式的“|”语句。

形式一：

mysql> select * from user where name regexp 'user[12345]';+----+-------+| id | name  |+----+-------+|  1 | user1 ||  4 | user4 ||  5 | user5 |+----+-------+3 rows in set (0.00 sec)

形式二：

Database changedmysql> select * from user where name regexp 'user 1|2|3|4|5';+----+-------+| id | name  |+----+-------+|  4 | user4 ||  5 | user5 |+----+-------+2 rows in set (0.00 sec)

取反操作：

mysql> select * from user where name regexp 'user[^123]';+----+-------+| id | name  |+----+-------+|  4 | user4 ||  5 | user5 |+----+-------+2 rows in set (0.00 sec)

匹配范围

• “[0-9]”或”[a-z]”都可以。

mysql> select * from user where name regexp 'user[1-9]';+----+-------+| id | name  |+----+-------+|  1 | user1 ||  4 | user4 ||  5 | user5 |+----+-------+3 rows in set (0.00 sec)

匹配特殊字符

• 与C++类似，MySQL正则表达式也有转义字符，只不过需要在开头添加“\”并在后面接上字符表示转义。

mysql> select * from user where name regexp 'wang\\.';+----+-------+| id | name  |+----+-------+| 73 | wang. |+----+-------+1 row in set (0.00 sec)

匹配字符类

• [:alnum:] 匹配任意字母和数字
• [:alpha:] 匹配任意字符
• [:digit:]匹配任意数字
• [:lower:]匹配任意小写字母
• [:upper:]匹配任意大写字母

mysql> select * from user where name regexp '[:alnum:]';+----+----------+| id | name     |+----+----------+|  1 | user1    ||  3 | wang     ||  4 | user4    ||  5 | user5    ||  7 | wanggggg || 73 | wang.    |+----+----------+6 rows in set (0.00 sec)

匹配多个实例

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

mysql> select * from user where name regexp 'wang?';+----+----------+| id | name     |+----+----------+|  3 | wang     ||  7 | wanggggg || 73 | wang.    |+----+----------+3 rows in set (0.00 sec)

定位符

• ^ 匹配文本开始
• $ 匹配文本结束

mysql> select * from user where name regexp '^wang';+----+----------+| id | name     |+----+----------+|  3 | wang     ||  7 | wanggggg || 73 | wang.    |+----+----------+3 rows in set (0.00 sec)