1、REGEXP ‘匹配方式’

2、常用匹配方式

模式字符 含义
^ 匹配字符开始的部分
$ 匹配字符串结尾的部分
. 代表字符串中的任意一个字符，包括回车换行
[字符合集] 匹配字符合集中的任何一个字符
[^字符合集] 匹配除了字符合集以外的任何一个字符
S1 | S2 | S3 匹配S1、S2、S3中的任意一个字符串
* 代表0个1个或多个其前的字符
+ 代表1个或多个其前的字符
String{N} 字符串出现N次
字符串{M,N} 字符串至少出现M次，最多N次

1、^匹配字符开始的部分

以用户表为例，匹配用户名以t开始的用户：

1
2
3
4
5
6
7
8
mysql> SELECT * FROM cms_user WHERE username REGEXP '^t';
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| id | username | password | email       | regTime    | face     | proId | age  | sex    |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| 12 | test1    | test1    | user@qq.com | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2    | TEST2    | user@qq.com | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
2 rows in set (0.00 sec)

2、$匹配字符结尾的部分

以用户表为例，匹配用户名以g结尾的用户：

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM cms_user WHERE username REGEXP 'g$';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email       | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  4 | long     | long     | user@qq.com | 1419814708 | user.jpg |     4 |   44 | 女   |
|  5 | ring     | ring     | user@qq.com | 1419815708 | user.jpg |     2 |   25 | 男   |
|  7 | sing     | sing     | user@qq.com | 1419817708 | user.jpg |     5 |   56 | 男   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
3 rows in set (0.00 sec)

3、.代表任意字符

比如查询用户名中出现rg且中间有两个任意字符：

1
2
3
4
5
6
7
mysql> SELECT * FROM cms_user WHERE username REGEXP 'r..g';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email       | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  5 | ring     | ring     | user@qq.com | 1419815708 | user.jpg |     2 |   25 | 男   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
1 row in set (0.00 sec)

该命令等价于：

1
SELECT * FROM cms_user WHERE username LIKE 'r__g';

4、[字符合集]，匹配字符合集中的任何一个字符

例如匹配用户名中包含[lt]的的用户：

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM cms_user WHERE username REGEXP '[lt]';
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| id | username | password | email       | regTime    | face     | proId | age  | sex    |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
|  4 | long     | long     | user@qq.com | 1419814708 | user.jpg |     4 |   44 | 女     |
|  8 | blek     | blek     | user@qq.com | 1419818708 | user.jpg |     1 |   88 | 女     |
| 10 | lily     | lily     | user@qq.com | 1419831708 | user.jpg |     2 |   32 | 女     |
| 12 | test1    | test1    | user@qq.com | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2    | TEST2    | user@qq.com | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
5 rows in set (0.00 sec)

也可以指定集合，比如出现过a-f的：

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM cms_user WHERE username REGEXP '[a-f]';
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| id | username | password | email       | regTime    | face     | proId | age  | sex    |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
|  6 | queen    | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   77 | 女     |
|  8 | blek     | blek     | user@qq.com | 1419818708 | user.jpg |     1 |   88 | 女     |
|  9 | rose     | rose     | user@qq.com | 1419821708 | user.jpg |     2 |   12 | 男     |
| 12 | test1    | test1    | user@qq.com | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2    | TEST2    | user@qq.com | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
5 rows in set (0.01 sec)

5、[^字符合集]，匹配除了字符合集以外的任何一个字符

例如匹配用户名中除了[long]的的用户(如果用户名叫longe也会显示出来)：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> SELECT * FROM cms_user WHERE username REGEXP '[^long]';
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   11 | 男     |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   21 | 女     |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   33 | 男     |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   25 | 男     |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   77 | 女     |
|  7 | sing      | sing         | user@qq.com | 1419817708 | user.jpg |     5 |   56 | 男     |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   88 | 女     |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   12 | 男     |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   32 | 女     |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   65 | 保密   |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2     | TEST2        | user@qq.com | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
12 rows in set (0.01 sec)

同理也可以指定集合不在a-k的：

1
SELECT * FROM cms_user WHERE username REGEXP '[^a-k]';

6、S1 | S2 | S3，匹配S1、S2、S3中的任意一个字符串

例如匹配ng|qu的：

1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM cms_user WHERE username REGEXP 'ng|qu';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email       | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  4 | long     | long     | user@qq.com | 1419814708 | user.jpg |     4 |   44 | 女   |
|  5 | ring     | ring     | user@qq.com | 1419815708 | user.jpg |     2 |   25 | 男   |
|  6 | queen    | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   77 | 女   |
|  7 | sing     | sing     | user@qq.com | 1419817708 | user.jpg |     5 |   56 | 男   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
4 rows in set (0.00 sec)

7、*，代表0个1个或多个其前的字符

比例出现qu的：

1
2
3
4
5
6
7
mysql> SELECT * FROM cms_user WHERE username REGEXP 'qu*';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email       | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  6 | queen    | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   77 | 女   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
1 row in set (0.00 sec)

8、+，代表1个或多个其前的字符

比如t+一定出现t:

1
2
3
4
5
6
7
8
mysql> SELECT * FROM cms_user WHERE username REGEXP 't+';
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| id | username | password | email       | regTime    | face     | proId | age  | sex    |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| 12 | test1    | test1    | user@qq.com | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2    | TEST2    | user@qq.com | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
2 rows in set (0.00 sec)

9、String{N}，字符串出现N次

比如que出现2次：

1
2
3
4
5
6
7
mysql> SELECT * FROM cms_user WHERE username REGEXP 'que{2}';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email       | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  6 | queen    | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   77 | 女   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
1 row in set (0.00 sec)

10、字符串{M,N},字符串至少出现M次，最多N次

比如que最多出现3次最少出现一次：

1
2
3
4
5
6
7
mysql> SELECT * FROM cms_user WHERE username REGEXP 'que{1,3}';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email       | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  6 | queen    | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   77 | 女   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
1 row in set (0.00 sec)