mysql 正则表达式查询

心已赠人 2024-04-20 22:21 90阅读 0赞

学习了[mysql 连接查询和子查询][mysql]和[myql join连接][myql join]，接下来学习下正则表达式查询。正则表达式的规则都是相似的。

### 8，使用正则表达式查询 ###

正则表达式通常被用来检索或替换那些符合某个模式的文本内容,根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常复杂的查询。

MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式，表3列出了REGEXP操作符中常用字符匹配列表。

表3正则表达式常用字符匹配列表

<table> 
 <tbody> 
 <tr> 
 <td colspan="1" style="width:102px;"> 选项 </td> 
 <td colspan="1" style="width:232px;"> 说明 </td> 
 <td colspan="1" style="width:200px;"> 例例子 </td> 
 <td colspan="1"> 匹匹配值示例 </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="width:102px;"> ^ </td> 
 <td colspan="1" style="width:232px;"> 匹配文本的开始字符 </td> 
 <td colspan="1" style="width:200px;"> '^b'匹配以字母b开头的字符串 </td> 
 <td colspan="1"> book,big,banana,bike </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="width:102px;"> $ </td> 
 <td colspan="1" style="width:232px;"> 匹配文本的结束字符 </td> 
 <td colspan="1" style="width:200px;"> 'st$'匹配以st结尾的字符串 </td> 
 <td colspan="1"> test,resist,persist </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="width:102px;"> . </td> 
 <td colspan="1" style="width:232px;"> 匹配任何单个字符 </td> 
 <td colspan="1" style="width:200px;"> 'b.t'匹配任何b和1之间有一个字符 </td> 
 <td colspan="1"> bit,bat,but,bite </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="width:102px;"> * </td> 
 <td colspan="1" style="width:232px;"> 匹配零个或多个在它前面的字符 </td> 
 <td colspan="1" style="width:200px;"> 'f*n'匹配字符n前面有任意个字符f </td> 
 <td colspan="1"> fn, fan,faan,faben </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="width:102px;"> + </td> 
 <td colspan="1" style="width:232px;"> 匹配前面的字符1次或多次 </td> 
 <td colspan="1" style="width:200px;"> 'ba+'匹配以b开头后面紧跟至少有一个a </td> 
 <td colspan="1"> ba,bay,bare,battle </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="width:102px;"> &lt;字符串&gt; </td> 
 <td colspan="1" style="width:232px;"> 匹配包含指定的字符串的文本 </td> 
 <td colspan="1" style="width:200px;"> 'fa' </td> 
 <td colspan="1"> fan,afa,faad </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="width:102px;"> [字符集合] </td> 
 <td colspan="1" style="width:232px;"> 如匹配字符集合中的任何一个字符 </td> 
 <td colspan="1" style="width:200px;"> '[xz]'匹配x或者z </td> 
 <td colspan="1"> dizzy,zebra,x-ray,extra </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="width:102px;"> [^] </td> 
 <td colspan="1" style="width:232px;"> 匹配不在括号中的任何字符 </td> 
 <td colspan="1" style="width:200px;"> '[^abc]'匹配任何不包含a、b或c的字符串 </td> 
 <td colspan="1"> desk,fox,f8ke </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="width:102px;"> 字符串{n,} </td> 
 <td colspan="1" style="width:232px;"> 匹配前面的字符串至少n次 </td> 
 <td colspan="1" style="width:200px;"> b{2}匹配2个或更多的b </td> 
 <td colspan="1"> bbb,bbbb,bbbbbbb </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="width:102px;"> 字符串{n,m} </td> 
 <td colspan="1" style="width:232px;"> 匹配前面的字符串至少n次,至多m次。如果n为0,此参数为可选参数 </td> 
 <td colspan="1" style="width:200px;"> b{2,4}匹配最少2个，最多4个b </td> 
 <td colspan="1"> bb,bbb,bbbb </td> 
 </tr> 
 </tbody> 
</table>

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

字符‘^’匹配以特定字符或者字符串开头的文本。

【例68】在 fruits表中，查询f\_name字段以字母‘b’开头的记录，SQL语句如下：

SELECT * FROM fruits WHERE f_name REGEXP '^b';

![c0eefa7fe498452287ca341d4c80b2aa.png][]

fruits 表中有3条记录的f\_name字段值是以字母b开头，返回结果有3条记录。

【例69】在fruits表中，查询f\_name字段以“be”开头的记录，SQL语句如下;

SELECT * FROM fruits WHERE f_name REGEXP '^be';

![0ff63181ebfa4d6a9c79edcd10b11216.png][]

只有 berry是以“be”开头。所以查询结果中只有1条记录。

#### 2，查询以特定字符或字符串结尾的记录 ####

字符‘$’匹配以特定字符或者字符串结尾的文本。

【例70】在 fruits表中，查询f\_namc字段以字母 'y’结尾的记录。SQL语句如下;

SELECT * FROM fruits WHERE f_name REGEXP 'y$';

![6ab425316c404a53a94a9e9be76598e9.png][]

fruits表中有4条记录的f\_namc字段值是以字母‘y’结尾，返回结果有4条记录。

【例71】在 fruits表中，查询f\_name字段以字符串“rry”结尾的记录。SQL 语句如下：

SELECT * FROM fruits WHERE f_name REGEXP 'rry$';

![a28830239ac04351a1c35dd76aa9bdcb.png][]

fruits 表中有3条记录的f\_name字段值是以字符串“rry”结尾,返回结果有3条记录。

#### 3，用符号"."来替代字符串中的任意一个字符 ####

字符‘·' 匹配任意一个字符。

【例72】在 fruits 表中，查询f\_name字段值包含字母‘a’与‘g’且两个字母之间只有一个字母的记录，SQL语句如下，

SELECT * FROM fruits WHERE f_name REGEXP 'a.g';

![9a769db2534d4ccca04be2755b2764df.png][]

查询语句中‘a.g’指定匹配字符中要有字母a和g，且两个字母之间包含单个字符。并不限定匹配的字符的位置和所在查询字符串的总长度，因此 orange和 mango都符合匹配条件。

#### 4，使用"\*""和"+"来匹配多个字符 ####

星号‘\*’匹配前面的字符任意多次，包括0次。加号‘+’匹配前面的字符至少一次。

【例73】在： fruits表中。查询f\_name字段值以字母‘b’开头，且‘b’后面出现字母‘a’的记录，SQL语句如下;

SELECT * FROM fruits WHERE f_name REGEXP '^ba*';

![057e3ce14f73482881fd60b78d97e055.png][]

星号‘\*’可以匹配任意多个字符，blackberry和 berry 中字母b后面并没有出现字母a，但是也满足匹配条件。

【例74】在 fruits表中。查询f\_name字段值以字母‘b’开头。且‘b’后面出现字母‘a’至少一次的记录，SQL语句如下：

SELECT * FROM fruits WHERE f_name REGEXP '^ba+';

![bc00eb20f3614dea98555178aa083445.png][]

‘a+’匹配字母‘a’至少一次，只有banana滴足匹配条件。

#### 5，匹配指定字符串 ####

正则表达式可以匹配指定字符串，只要这个字符串在查询文本中即可，如要匹配多个字符串，多个字符串之间使用分隔符‘|’隔开。

【例75】在 fruits表中。查询f\_name字段值包含字符串“on”的记录，SQL语句如下：

SELECT * FROM fruits WHERE f_name REGEXP 'on';

![a237ffe46d744a80a8677e3808193cc8.png][]

可以看到，f\_name字段的 melon、lemon 和l coconut 3个值中都包含有字符串“on”，满足匹配条件。

【例76】在 fruits表中，查询f\_name字段值包含字符串“on”或者“ap”的记录，SQL语句如下：

SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';

![41726908ba344f72a1d419ab7b731c6a.png][]

可以看到f\_name字段的melon、lemon 和 coconut 3个值中都包含有字符串“on”, apple和apricot值中包含字符串“ap”，满足匹配条件。

提示：之前介绍过，LIKE运算符也可以匹配指定的字符串，但与REGEXP不同，LIKE匹配的字符串如果在文本中间出现，则找不到它，相应的行也不会返回。而REGEXP在文本内进行匹配，如果被匹配的字符串在文本中出现，REGEXP将会找到它，相应的行也会被返回。对比结果如【例77』所示。

【例77】在fruits 表中，使用LIKE运算符查询f\_name字段值为“on”的记录，SQL 语句如下：

SELECT * FROM fruits WHERE f_name LIKE 'on';

![0f23a7c5e0314d68aa77d324ca728d8d.png][]

f\_name字段没有值为“on”的记录，返回结果为空。

SELECT * FROM fruits WHERE f_name LIKE '%on';

![50cf05e6ae7b4bc4be1f4563cacb8ade.png][]

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

方括号“\[\]”指定一个字符集合，只匹配其中任何一个字符，即为所查找的文本。

【例78】在fruits表中，查找f\_name字段中包含字母‘o’或者‘t’的记录，SQL语句如下：

SELECT * FROM fruits WHERE f_name REGEXP '[ot]';

![ceb3f6f52e9e46dfb6472b189a50ca02.png][]

查询结果可以看到，所有返回的记录的f\_name字段的值中都包含有字母o或者t，或者两个都有。

方括号“\[\]”还可以指定数值集合。

【例79】在 fruits 表，查询s\_id字段中数值中包含4、5或者6的记录，SQL语句如下：

SELECT * FROM fruits WHERE s_id REGEXP '[456]';

![731d771558534d9fbcc5740d779b4972.png][]

查询结果中，s\_id字段值中有3个数字中的1个即为匹配记录字段。

匹配集合“\[456\]”也可以写成“\[4-6\]”即指定集合区间。例如“\[a-z\]”表示集合区间为从a~z的字母，“\[0-9\]”表示集合区间为所有数字。

#### 7，匹配指定字符以外的字符 ####

“\[^字符集合\]”匹配不在指定集合中的任何字符。

【例80】在fruits表中，查面f\_id字经包含字却出-e和数字1~2以外的字符的记录，SQL语句如下：

SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';

![c075ee4701064a31aefc3cba2d4d8a07.png][]

返回记录中的f jid字段值中包含了指定字母和数字以外的值，如s、 m. o、t等，这些字

母均不在a~e与1~2之间，满足匹配条件。

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

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

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

例如, a\{2,\}表示字母a连续出现至少2次，也可以大于2次： a\{2,4\} 表示字母a连续出现最少2次，最多不能超过4次。

【例81】在fruits 表中，查询f\_ name字段值出现字母'x' 至少2次的记录，SQL 语句如下：

SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';

![dbb3d6c84aa54e8b9f66f220d4f59cce.png][]

可以看到，f\_name字段的“xxxx”包含了4个字母‘x’，“xxtt”包含两个字母‘x’，均为满足匹配条件的记录。

【例82】在 fruits表中，查询f\_name字段值出现字符串“ba”最少1次，最多3次的记录，SQL语句如下：

SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';

![52ca5f0bb16140099480fa5aeb849267.png][]

可以看到，f\_name字段的xbabay值中“ba”出现了2次，banana 中出现了1次，xbababa中出现了3次，都满足匹配条件的记录。

### 总结： ###

mysql正则表达式查询是很强大的，可以适合匹配复杂的情况。这个也好学，毕竟正则表达式的内容都相似。

上一篇：《[myql join连接][myql join]》

下一篇： 《mysql 常用查询》

[mysql]: https://blog.csdn.net/qq_35461948/article/details/136965100
[myql join]: https://blog.csdn.net/qq_35461948/article/details/137324842?csdn_share_tail=%7B%22type%22%3A%22blog%22%2C%22rType%22%3A%22article%22%2C%22rId%22%3A%22137324842%22%2C%22source%22%3A%22qq_35461948%22%7D
[c0eefa7fe498452287ca341d4c80b2aa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/379193130f594b0eaf3c279cd4fe486a.png
[0ff63181ebfa4d6a9c79edcd10b11216.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/44e7345639754dfdbb42262937e4945b.png
[6ab425316c404a53a94a9e9be76598e9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/2e01da7d895d4b2aa2bcdecfb20367ce.png
[a28830239ac04351a1c35dd76aa9bdcb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/e2027651d9904771a24526ccb76abe18.png
[9a769db2534d4ccca04be2755b2764df.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/1275706a583d4f79a5bb0daab83c8566.png
[057e3ce14f73482881fd60b78d97e055.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/151d5b0952c7407eb39050b7db786060.png
[bc00eb20f3614dea98555178aa083445.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/2b08080bbca946b29bb3cadb08b9e3c9.png
[a237ffe46d744a80a8677e3808193cc8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/254419fce70e4bf9b409976e0cbedcd5.png
[41726908ba344f72a1d419ab7b731c6a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/ee66915c289a467bbc1686a4361008aa.png
[0f23a7c5e0314d68aa77d324ca728d8d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/e7d127a396bf441a8a39bc8b7bfafe83.png
[50cf05e6ae7b4bc4be1f4563cacb8ade.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/3de89f053e5a4a838ced56eb2aca5795.png
[ceb3f6f52e9e46dfb6472b189a50ca02.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/96140167143c4bbc91724e5751b1cb2c.png
[731d771558534d9fbcc5740d779b4972.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/b6963350197e4fb7958dc8051e09bf06.png
[c075ee4701064a31aefc3cba2d4d8a07.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/58d09f19309e421a935cf597ce1aab7d.png
[dbb3d6c84aa54e8b9f66f220d4f59cce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/0e67410233924090af3c7027489748b1.png
[52ca5f0bb16140099480fa5aeb849267.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/c99c005804ea42fabdd2fd040cb4ddd9.png