timg.jpeg
本节将简单介绍如何在 Mysql where 子句内使用正则表达式来更好地控制数据过滤
基本字匹配符
select *
from student
where sname regexp '红'
输出为：
+------+-----------+---------------------+------+
| SId | Sname | Sage | Ssex |
+------+-----------+---------------------+------+
| 05 | 周红梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴红兰 | 1992-03-01 00:00:00 | 女 |
+------+-----------+---------------------+------+
regexp 的简单用法与 like 关键字无异，下面将介绍更深层次的用法：
select *
from order_info_utf
where user_id regexp '.1111'
order by user_id;
此 sql 语句使用了正则表达式 .1111，. 是正则语言中的一个特殊字符，表示匹配任意一个字符
+----------+---------+-----------+-------+---------------------+
| order_id | user_id | pay_state | price | pay_time |
+----------+---------+-----------+-------+---------------------+
| 527086 | 101111 | 已支付 | 762 | 2016-04-28 22:58:00 |
| 538215 | 101111 | 已支付 | 601 | 2016-04-30 20:20:00 |
| 432821 | 11111 | 已支付 | 818 | 2016-04-18 13:16:00 |
| 515923 | 21111 | 已支付 | 1139 | 2016-04-27 18:54:00 |
| 93287 | 31111 | 已支付 | 589 | 2016-03-11 21:36:00 |
| 240508 | 31111 | 已支付 | 521 | 2016-03-28 07:31:00 |
| 154261 | 41111 | 未支付 | 834 | NULL |
| 288954 | 41111 | 已支付 | 471 | 2016-04-01 23:55:00 |
| 46030 | 51111 | 未支付 | 986 | NULL |
| 93874 | 61111 | 未支付 | 485 | NULL |
| 20444 | 71111 | 已支付 | 436 | 2016-03-03 12:17:00 |
| 216118 | 71111 | 已支付 | 550 | 2016-03-25 08:22:00 |
| 258402 | 71111 | 已支付 | 954 | 2016-03-29 19:54:00 |
| 442332 | 71111 | 已支付 | 806 | 2016-04-19 12:48:00 |
| 95510 | 81111 | 已支付 | 716 | 2016-03-12 10:26:00 |
| 318583 | 91111 | 已支付 | 564 | 2016-04-05 22:35:00 |
+----------+---------+-----------+-------+---------------------+
进行 or 匹配
select *
from order_info_utf
where user_id regexp '11111|22222'
order by user_id;
语句中使用了正则表达式 11111|22222 。 | 为正则表达式的 or 操作符。它表示匹配其中之一，因此 11111 和22222 都匹配并返回，注意在 | 字符左右万不可空格，否则将查询不出数据
+----------+---------+-----------+-------+---------------------+
| order_id | user_id | pay_state | price | pay_time |
+----------+---------+-----------+-------+---------------------+
| 432821 | 11111 | 已支付 | 818 | 2016-04-18 13:16:00 |
| 49113 | 22222 | 已支付 | 439 | 2016-03-07 10:31:00 |
| 328158 | 22222 | 未支付 | 450 | NULL |
| 328548 | 22222 | 未支付 | 412 | NULL |
| 331328 | 22222 | 已支付 | 442 | 2016-04-07 08:45:00 |
+----------+---------+-----------+-------+---------------------+
匹配几个字符之一
select *
from order_info_utf
where user_id regexp '[1,2,3]1111'
order by user_id;
[] 是另一种形式的 or 语句,它的意思是匹配中括号的内容
+----------+---------+-----------+-------+---------------------+
| order_id | user_id | pay_state | price | pay_time |
+----------+---------+-----------+-------+---------------------+
| 432821 | 11111 | 已支付 | 818 | 2016-04-18 13:16:00 |
| 515923 | 21111 | 已支付 | 1139 | 2016-04-27 18:54:00 |
| 93287 | 31111 | 已支付 | 589 | 2016-03-11 21:36:00 |
| 240508 | 31111 | 已支付 | 521 | 2016-03-28 07:31:00 |
+----------+---------+-----------+-------+---------------------+
如果使用中括号来进行筛选不想被展示的内容，只需在中括号内加上 ^ 字符即可，如：
select *
from order_info_utf
where user_id regexp '[^1,2,3]1111'
order by user_id;
输出为：
+----------+---------+-----------+-------+---------------------+
| order_id | user_id | pay_state | price | pay_time |
+----------+---------+-----------+-------+---------------------+
| 527086 | 101111 | 已支付 | 762 | 2016-04-28 22:58:00 |
| 538215 | 101111 | 已支付 | 601 | 2016-04-30 20:20:00 |
| 154261 | 41111 | 未支付 | 834 | NULL |
| 288954 | 41111 | 已支付 | 471 | 2016-04-01 23:55:00 |
| 46030 | 51111 | 未支付 | 986 | NULL |
| 93874 | 61111 | 未支付 | 485 | NULL |
| 20444 | 71111 | 已支付 | 436 | 2016-03-03 12:17:00 |
| 216118 | 71111 | 已支付 | 550 | 2016-03-25 08:22:00 |
| 258402 | 71111 | 已支付 | 954 | 2016-03-29 19:54:00 |
| 442332 | 71111 | 已支付 | 806 | 2016-04-19 12:48:00 |
| 95510 | 81111 | 已支付 | 716 | 2016-03-12 10:26:00 |
| 318583 | 91111 | 已支付 | 564 | 2016-04-05 22:35:00 |
+----------+---------+-----------+-------+---------------------+
匹配范围
集合可用来定义要匹配的一个或多个字符
select *
from order_info_utf
where user_id regexp '[1-4]1111'
order by user_id;
此 sql 语句表示匹配 1-4 中任意一个字符与 1111 字符结合的所有数据，只要符合此规则就会显示（如果有 .11111 或 41111x 也满足此规则），输出为：
+----------+---------+-----------+-------+---------------------+
| order_id | user_id | pay_state | price | pay_time |
+----------+---------+-----------+-------+---------------------+
| 432821 | 11111 | 已支付 | 818 | 2016-04-18 13:16:00 |
| 515923 | 21111 | 已支付 | 1139 | 2016-04-27 18:54:00 |
| 93287 | 31111 | 已支付 | 589 | 2016-03-11 21:36:00 |
| 240508 | 31111 | 已支付 | 521 | 2016-03-28 07:31:00 |
| 154261 | 41111 | 未支付 | 834 | NULL |
| 288954 | 41111 | 已支付 | 471 | 2016-04-01 23:55:00 |
+----------+---------+-----------+-------+---------------------+
匹配特殊字符
select *
from student
where sname regexp '\\@';
为了匹配特殊字符，必须用 \\ 为前导转义。\\- 表示查找 -， \\. 表示查找 .，输出为：
+------+---------+---------------------+------+
| SId | Sname | Sage | Ssex |
+------+---------+---------------------+------+
| 01 | 赵雷@ | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电@ | 1990-12-21 00:00:00 | 男 |
+------+---------+---------------------+------+
| 元字符 | 说明 |
|---|---|
| \\f | 换页 |
| \\n | 换行 |
| \\r | 回车 |
| \\t | 制表 |
| \\v | 纵向制表 |
匹配字符类
存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配
| 类 | 说明 |
|---|---|
| [:alnum:] | 任意字母和数字（同[a-zA-Z0-9]） |
| [:cntrl:] | ASCII控制字符（ ASCII 0到31和127） |
| [:punct:] | 既不在[:alnum:]又不在[:cntrl:]中的任意字符 |
| [:alpha:] | 任意字符（同[a-zA-Z]） |
| [:blank:] | 空格和制表（同[\t]） |
| [:digit:] | 任意数字（同[0-9]） |
| [:print:] | 任意可打印字符 |
| [:graph:] | 与[:print:]相同，但不包括空格 |
| [:lower:] | 任意小写字母（同[a-z]） |
| [:space:] | 包括空格在内的任意空白字符（同[\f\n\r\t\v]） |
| [:upper:] | 任意大写字母（同[A-Z]） |
| [:xdigit:] | 任意十六进制数字（同[a-fA-F0-9]） |
匹配多个实例
| 元字符 | 说明 |
|---|---|
| * | 0 个或多个匹配 |
| + | 1个或多个匹配（等于 {1,}） |
| ? | 0 个或 1 个匹配（等于 {0,1}） |
| {n} | 指定数目的匹配 |
| {n,} | 不少于指定数目的匹配 |
| {n,m} | 匹配数目的范围（m 不超过 255） |
示例如下：
select *
from goods
where name regexp '英寸一？';
此时的 一 出现次数为 0 或 1，具体输出：
+----+---------------------------------------+--------------+------------+----------+
| id | name | cate_name | brand_name | price |
+----+---------------------------------------+--------------+------------+----------+
| 1 | r510vc 15.6英寸笔记本 | 笔记本 | 华硕 | 3399.000 |
| 2 | y400n 14.0英寸笔记本电脑 | 笔记本 | 联想 | 4999.000 |
| 3 | g150th 15.6英寸游戏本 | 游戏本 | 雷神 | 8499.000 |
| 4 | x550cc 15.6英寸笔记本 | 笔记本 | 华硕 | 2799.000 |
| 6 | u330p 13.3英寸超极本 | 超级本 | 联想 | 4299.000 |
| 8 | ipad mini 7.9英寸平板电脑 | 平板电脑 | 苹果 | 1998.000 |
| 9 | ipad air 9.7英寸平板电脑 | 平板电脑 | 苹果 | 3388.000 |
| 11 | ideacentre c340 20英寸一体电脑 | 台式机 | 联想 | 3499.000 |
| 13 | imac me086ch/a 21.5英寸一体电脑 | 台式机 | 苹果 | 9188.000 |
+----+---------------------------------------+--------------+------------+----------+
以下例子为字符类和重复元字符的结合使用：
select *
from goods
where name regexp '[:lower:]{6}';
此 sql 语句表示查询 goods 表中 name 字段出现了 6 次小写字母的所有数据，输出为：
+----+---------------------------------------+---------------------+------------+----------+
| id | name | cate_name | brand_name | price |
+----+---------------------------------------+---------------------+------------+----------+
| 10 | ipad mini 配备 retina 显示屏 | 平板电脑 | 苹果 | 2788.000 |
| 11 | ideacentre c340 20英寸一体电脑 | 台式机 | 联想 | 3499.000 |
| 12 | vostro 3800-r1206 台式电脑 | 台式机 | 戴尔 | 2899.000 |
| 16 | poweredge ii服务器 | 服务器/工作站 | 戴尔 | 5388.000 |
+----+---------------------------------------+---------------------+------------+----------+
定位符
| 元字符 | 说明 |
|---|---|
| ^ | 文本的开头 |
| $ | 文本的结尾 |
| [[:<:]] | 词的开始 |
| [[:>:]] | 词的结尾 |
示例如下：
select *
from goods
where name regexp '^[im?]';
此 sql 表示查询 goods 表中 name 字段是以 i 或 im 开头的所有数据，输出为：
+----+---------------------------------------+---------------------+------------+-----------+
| id | name | cate_name | brand_name | price |
+----+---------------------------------------+---------------------+------------+-----------+
| 8 | ipad mini 7.9英寸平板电脑 | 平板电脑 | 苹果 | 1998.000 |
| 9 | ipad air 9.7英寸平板电脑 | 平板电脑 | 苹果 | 3388.000 |
| 10 | ipad mini 配备 retina 显示屏 | 平板电脑 | 苹果 | 2788.000 |
| 11 | ideacentre c340 20英寸一体电脑 | 台式机 | 联想 | 3499.000 |
| 13 | imac me086ch/a 21.5英寸一体电脑 | 台式机 | 苹果 | 9188.000 |
| 17 | mac pro专业级台式电脑 | 服务器/工作站 | 苹果 | 28888.000 |
+----+---------------------------------------+---------------------+------------+-----------+
like 和 regexp的不同在于， like 匹配整个串而 regexp 匹配子串。利用定位符，通过用 ^ 开始每个表达式，用 $ 结束每个表达式，可以使 regexp 的作用与 like 一样
网友评论