mysql查询语法汇总-使用正则表达式

正则表达式通常被用来检索或者替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。

初识

mysql中使用regexp关键字指定正则表达式的字符匹配模式。

先查看下环境数据

mysql> select * from test;
+----+-----------+------+----------+------+
| id | name      | age  | area     | like |
+----+-----------+------+----------+------+
|  1 | zhangsan  |   20 | shanxi   | eat  |
|  4 | zhangsan1 |   20 | shanxi   | eat  |
|  5 | lisi      |   22 | beijing  | 123  |
|  6 | wangwu    |   24 | shandong | 222  |
|  7 | NULL      |   24 | shandong | 222  |
+----+-----------+------+----------+------+
5 rows in set (0.02 sec)

mysql>

查询以特定字符或字符串开头的记录

字符^匹配以特定字符或者字符串开头的数据；

例如，我们查下test数据表中，name字段，以zh开头的数据；

mysql> select * from test where name regexp '^zh';
+----+-----------+------+--------+------+
| id | name      | age  | area   | like |
+----+-----------+------+--------+------+
|  1 | zhangsan  |   20 | shanxi | eat  |
|  4 | zhangsan1 |   20 | shanxi | eat  |
+----+-----------+------+--------+------+
2 rows in set (0.00 sec)

mysql>

匹配以特定字符或者字符串结尾的记录

字符$匹配以特定字符或者字符串结尾的数据；

例如：我们查下test数据表中，area字段，以dong结尾的数据；

mysql> select * from test where area regexp 'dong$';
+----+--------+------+----------+------+
| id | name   | age  | area     | like |
+----+--------+------+----------+------+
|  6 | wangwu |   24 | shandong | 222  |
|  7 | NULL   |   24 | shandong | 222  |
+----+--------+------+----------+------+
2 rows in set (0.00 sec)

mysql>

用符号.来替代字符串中的任意一个字符

字符.匹配任意一个字符；

例如，我们查下test数据表中，name字段，在a和g之间有且只有一个字符的数据；

mysql> select * from test where name regexp 'a.g';
+----+-----------+------+----------+------+
| id | name      | age  | area     | like |
+----+-----------+------+----------+------+
|  1 | zhangsan  |   20 | shanxi   | eat  |
|  4 | zhangsan1 |   20 | shanxi   | eat  |
|  6 | wangwu    |   24 | shandong | 222  |
+----+-----------+------+----------+------+
3 rows in set (0.00 sec)

mysql>

使用*和+来匹配多个字符

星号*匹配前面的字符任意多次，包括0次；

加号+匹配前面的字符至少一次。

首先我们在表里插入一条数据，这样子能更好的看出来效果；

mysql> insert into test values (8, 'zhaosi', 30, 'dongbei', 'fly');
Query OK, 1 row affected (0.01 sec)

mysql>

此时test表中的数据就变成了以下信息：

mysql> select * from test;
+----+-----------+------+----------+------+
| id | name      | age  | area     | like |
+----+-----------+------+----------+------+
|  1 | zhangsan  |   20 | shanxi   | eat  |
|  4 | zhangsan1 |   20 | shanxi   | eat  |
|  5 | lisi      |   22 | beijing  | 123  |
|  6 | wangwu    |   24 | shandong | 222  |
|  7 | NULL      |   24 | shandong | 222  |
|  8 | zhaosi    |   30 | dongbei  | fly  |
+----+-----------+------+----------+------+
6 rows in set (0.00 sec)

mysql>

例如，我们查下test数据表中，name字段，以字符z开头，并且后面的字符中出现了n的数据；

mysql> select * from test where name regexp '^z.*n*';
+----+-----------+------+---------+------+
| id | name      | age  | area    | like |
+----+-----------+------+---------+------+
|  1 | zhangsan  |   20 | shanxi  | eat  |
|  4 | zhangsan1 |   20 | shanxi  | eat  |
|  8 | zhaosi    |   30 | dongbei | fly  |
+----+-----------+------+---------+------+
3 rows in set (0.00 sec)

mysql>

可以看到出现了三条数据，其中id为1和4的name字段在zh之后存在n，但是id为8的name字段后面并没有出现n字符；

那么这个时候我们使用+号来运行下看看结果；

mysql> select * from test where name regexp '^z.*n+';
+----+-----------+------+--------+------+
| id | name      | age  | area   | like |
+----+-----------+------+--------+------+
|  1 | zhangsan  |   20 | shanxi | eat  |
|  4 | zhangsan1 |   20 | shanxi | eat  |
+----+-----------+------+--------+------+
2 rows in set (0.00 sec)

mysql>

这个时候就只能查到两条数据了。

匹配指定字符串

当mysql数据库在使用正则表达式的时候，可以匹配指定字符串，只要这个字符串在查询本文内存在过即可，如果我们要匹配多个字符串，我们可以通过分隔符|隔开。

例如，我们查看test数据表中，name字段，包括ang字符的数据；

mysql> select * from test where name regexp 'ang';
+----+-----------+------+----------+------+
| id | name      | age  | area     | like |
+----+-----------+------+----------+------+
|  1 | zhangsan  |   20 | shanxi   | eat  |
|  4 | zhangsan1 |   20 | shanxi   | eat  |
|  6 | wangwu    |   24 | shandong | 222  |
+----+-----------+------+----------+------+
3 rows in set (0.00 sec)

mysql>

可以看到查出来了三条数据；

那么我们再来查下，name字段中，包括ang或者si字符的数据；

mysql> select * from test where name regexp 'ang|si';
+----+-----------+------+----------+------+
| id | name      | age  | area     | like |
+----+-----------+------+----------+------+
|  1 | zhangsan  |   20 | shanxi   | eat  |
|  4 | zhangsan1 |   20 | shanxi   | eat  |
|  5 | lisi      |   22 | beijing  | 123  |
|  6 | wangwu    |   24 | shandong | 222  |
|  8 | zhaosi    |   30 | dongbei  | fly  |
+----+-----------+------+----------+------+
5 rows in set (0.00 sec)

mysql>

这次可以看到我们查出来了5条数据哈。

匹配指定字符中的任意一个

方括号[]指定一个字符集合，只匹配其中任何一个字符，即为所查找的文本。就是说只要出现在方括号中的数据，能匹配到一个即返回数据。

例如，我们查下test数据表中，name字段，里面有l或者a的数据；

mysql> select * from test where name regexp '[la]';
+----+-----------+------+----------+------+
| id | name      | age  | area     | like |
+----+-----------+------+----------+------+
|  1 | zhangsan  |   20 | shanxi   | eat  |
|  4 | zhangsan1 |   20 | shanxi   | eat  |
|  5 | lisi      |   22 | beijing  | 123  |
|  6 | wangwu    |   24 | shandong | 222  |
|  8 | zhaosi    |   30 | dongbei  | fly  |
+----+-----------+------+----------+------+
5 rows in set (0.00 sec)

mysql>

可以看到查出来了5条数据。

小提示

虽然空值在查询的时候结果显示的是null，但是实际上是空值，不能将null看做字符串，所以当我们查询l的时候，并没有显示这个值。

匹配指定字符串以外的字符

[^字符集合]匹配不在指定集合中的任何字符，也就是说，只要你的字段中包括你字符集合之外的任何数据，就能被匹配出来。

例如，我们查下test数据表中，name字段，里面没有lisi和zhangsan的数据；

mysql> select * from test where name regexp '[^lisizhangsan]';
+----+-----------+------+----------+------+
| id | name      | age  | area     | like |
+----+-----------+------+----------+------+
|  4 | zhangsan1 |   20 | shanxi   | eat  |
|  6 | wangwu    |   24 | shandong | 222  |
|  8 | zhaosi    |   30 | dongbei  | fly  |
+----+-----------+------+----------+------+
3 rows in set (0.00 sec)

mysql>

可以看到查出来了3条数据。

使用{n,}或者{n,m}来指定字符串连续出现的次数

字符串{n,}表示至少匹配n次前面的字符；

字符串{n,m}表示匹配前面的字符串不少于n次，不多于m次。

注意：需要注意的是，这里匹配的是连续哈，即比如nn表示连续出现两个n，或者xxx表示连续出现3个x，但是xnx就不算连续出现两个x。

造数据：这里我们还需要插入两条模拟数据；

mysql> insert into test (`name`, `age`, `area`, `like`) values ('zhann', 35, 'beijing', 'eat'), ('nnnni', 33, 'shanghai', 'play');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

再次查询下test数据表中的内容；

mysql> select * from test;
+----+-----------+------+----------+------+
| id | name      | age  | area     | like |
+----+-----------+------+----------+------+
|  1 | zhangsan  |   20 | shanxi   | eat  |
|  4 | zhangsan1 |   20 | shanxi   | eat  |
|  5 | lisi      |   22 | beijing  | 123  |
|  6 | wangwu    |   24 | shandong | 222  |
|  7 | NULL      |   24 | shandong | 222  |
|  8 | zhaosi    |   30 | dongbei  | fly  |
|  9 | zhann     |   35 | beijing  | eat  |
| 10 | nnnni     |   33 | shanghai | play |
+----+-----------+------+----------+------+
8 rows in set (0.00 sec)

mysql>

例如，我们查下test数据表中，name字段，至少包含一个n，且以i或者n结尾的数据；

mysql> select * from test where name regexp 'n{1,}[n|i]$';
+----+-------+------+----------+------+
| id | name  | age  | area     | like |
+----+-------+------+----------+------+
|  9 | zhann |   35 | beijing  | eat  |
| 10 | nnnni |   33 | shanghai | play |
+----+-------+------+----------+------+
2 rows in set (0.00 sec)

mysql>

可以看到我们查到了两条数据；

那么我们再试试，最少两个n，最多三个n的数据，且以i或者n结尾的数据；

mysql> select * from test where name regexp 'n{2,3}[n|i]$';
+----+-------+------+----------+------+
| id | name  | age  | area     | like |
+----+-------+------+----------+------+
| 10 | nnnni |   33 | shanghai | play |
+----+-------+------+----------+------+
1 row in set (0.00 sec)

mysql>

这个时候我们发现只能查到一条数据了。

至此，本文结束。 

长按以识别二维码关注运维家~

微信号：yunweijia0909