MySQL之正则表达式查询

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

1 以特定字符或字符串开头 ‘^’

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^a';
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
|  100 | apple  |     5 |
+------+--------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba';
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
|  103 | banana |    12 |
+------+--------+-------+
1 row in set (0.00 sec)

2 以特定字符或字符串结尾 ‘$’

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'n$';
+------+------------+-------+
| f_id | f_name     | price |
+------+------------+-------+
|  102 | melon      |     8 |
|  106 | watermelon |    20 |
+------+------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'le$';
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
|  100 | apple  |     5 |
+------+--------+-------+
1 row in set (0.00 sec)

3 替代字符串中任意一个字符 ‘.’

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'e.o';
+------+------------+-------+
| f_id | f_name     | price |
+------+------------+-------+
|  102 | melon      |     8 |
|  106 | watermelon |    20 |
+------+------------+-------+
2 rows in set (0.00 sec)

4 匹配多个字符 ‘*‘和’+’

• *：是前面的字符出现任意次，包括0.

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ap*';
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
|  100 | apple  |     5 |
|  110 | alpp   |    10 |
+------+--------+-------+
2 rows in set (0.00 sec)

• +:表示匹配前面的字符至少一次。

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ap+';
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
|  100 | apple  |     5 |
+------+--------+-------+
1 row in set (0.00 sec)

5 匹配指定字符串

匹配多个时使用’|'隔开

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'ap|on';
+------+------------+-------+
| f_id | f_name     | price |
+------+------------+-------+
|  100 | apple      |     5 |
|  102 | melon      |     8 |
|  106 | watermelon |    20 |
+------+------------+-------+
3 rows in set (0.00 sec)

6 匹配指定字符中的任意一个 ‘[]’

mysql> SELECT * FROM fruits WHERE f_name REGEXP '[on]';//匹配有o或者有n的字符串
+------+------------+-------+
| f_id | f_name     | price |
+------+------------+-------+
|  101 | orange     |    10 |
|  102 | melon      |     8 |
|  103 | banana     |    12 |
|  104 | mango      |    20 |
|  106 | watermelon |    20 |
+------+------------+-------+
5 rows in set (0.00 sec)

[456] ：为匹配有4或5或6的，也可以写成[4-6],[a-z]即为所有字母。

7 匹配指定字符以外的字符 ‘[^字符集]’

[^xyz] ：只要字符中有非xyz即可，如axyz。
[^xyz]$ :只要结尾字符不是xyz即可。

mysql> SELECT * FROM fruits WHERE f_name REGEXP '[^ON]$';
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
|  100 | apple  |     5 |
|  101 | orange |    10 |
|  103 | banana |    12 |
|  105 | water  |     5 |
|  107 | pear   |  NULL |
+------+--------+-------+
5 rows in set (0.00 sec)

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

a{n,} :a至少连续出现n次
a{n,m} :a至少连续出现n次，少于m次
abc{n,} :ab后至少有n个c
(abc){n,} :至少有n个abc

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'an{1,2}';
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
|  101 | orange |    10 |
|  103 | banana |    12 |
|  104 | mango  |    20 |
+------+--------+-------+
3 rows in set (0.00 sec)