MySQL正则表达式

MySQL正则表达式

MySQL中使用 REGEXP 操作符来进行正则表达式匹配 。

^ 匹配输入字符串的开始位置。

mysql> select word from english where word regexp '^comp';
+--------------+
| word         |
+--------------+
| companion    |
| compare      |
| compensation |
| competition  |
| complex      |
+--------------+
5 rows in set (0.01 sec)

$ 匹配输入字符串的结束位置。

mysql> select word from english where word regexp 'pe$';
+--------+
| word   |
+--------+
| escape |
+--------+
1 row in set (0.01 sec)

. 匹配除 “\n” 之外的任何单个字符。

mysql> select word from english where word regexp 'compa.e';
+---------+
| word    |
+---------+
| compare |
+---------+
1 row in set (0.02 sec)

mysql> select example_sentence from english where example_sentence regexp '比较.';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| example_sentence                                                                                                                                                                                                  |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| The difference in the horizontal and vertical factorial moments is compared and studied using Monte-Carlo simulation.
用蒙特-卡洛模拟方法对纵向阶乘矩和横向阶乘矩进行了比较和研究。                               |
| They are normally a more benign audience
他们通常观看时比较温和。                                                                                                                                                 |
| Compare this one with this one.拿这个和这个比较。                                                                                                                                                                 |
| Tin is a comparatively easy metal to smelt.锡是比较容易熔化的金属。                                                                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

[...] 字符集合。匹配所包含的任意一个字符。

mysql> select word from english where word regexp '[zq]';
+------------+
| word       |
+------------+
| dozen      |
| freeze     |
| horizontal |
| initialize |
| magazine   |
| mosquito   |
| quarrel    |
| query      |
| require    |
| squiggle   |
| squint     |
| unequally  |
+------------+
12 rows in set (0.01 sec)

p1|p2|p3 匹配 p1 或 p2 或 p3。

mysql> select word from english where word regexp 'despite|cription';
+-------------+
| word        |
+-------------+
| description |
| despite     |
+-------------+
2 rows in set (0.01 sec)

'despite|cription'可以理解为'^d.*cription$'和'^d.*espite$'的并集

mysql> select word from english where word regexp '^d.*cription$';
+-------------+
| word        |
+-------------+
| description |
+-------------+
1 row in set (0.01 sec)

mysql> select word from english where word regexp '^d.*espite$';
+---------+
| word    |
+---------+
| despite |
+---------+
1 row in set (0.01 sec)

* 匹配前面的子表达式零次或多次。

mysql> select word from english where word regexp 'frs*eeze';
+--------+
| word   |
+--------+
| freeze |
+--------+
1 row in set (0.01 sec)

+ 匹配前面的子表达式一次或多次。

mysql> select word from english where word regexp 'frs+eeze';
Empty set (0.01 sec)

{n} n 是一个非负整数。匹配确定的 n 次。

mysql> select word from english where word regexp 'frs{0}eeze';
+--------+
| word   |
+--------+
| freeze |
mysql> select word from english where word regexp 'frs{0}e{2}ze';
+--------+
| word   |
+--------+
| freeze |
+--------+
1 row in set (0.01 sec)

{n,m} m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

mysql> select word from english where word regexp 'fre{0,2}ze';
+--------+
| word   |
+--------+
| freeze |
+--------+
1 row in set (0.01 sec)