MySQL必知必会使用通配符和正则表达式数据过滤

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL必知必会使用通配符和正则表达式数据过滤

用通配符进行过滤

LIKE操作符

LIKE指示MySQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较

%百分号通配符

%表示任何字符出现任意次数。、

例:为了找出以词jet起头的产品:

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'jet%';

xn_2022-08-25_08-52-45

例:通配符可以在搜索模式的任意位置使用,并且可以使用多个通配符。

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '%anvil%';

xn_2022-08-25_08-56-52

:rainbow_flag:需要注意的是,%是可以匹配0个字符的。%代表搜索模式中给定位置的0个,1个,或多个字符。

:ideograph_advantage:还需要注意的一点是,尾部空格是会干扰通配符的匹配的。如果在保存anvil时尾部有几个空格,‘%anvil’就没有办法匹配成功的。解决方法就是你在末尾也加上一个%。或者更好的方法是使用函数。

:office:虽然似乎%可以匹配任何东西,但是有一个例外,那就是NULL。即使是WHERE prod_name LIKE '%'也不能匹配用值为NULL的产品名。

_下划线通配符

下划线只匹配单个字符。

SELECT prod_id ,prod_name 
FROM products
WHERE prod_name LIKE '_ ton anvil';

xn_2022-08-25_09-06-27

:e-mail:和%不一样,下划线只能匹配一个字符,不能多也不能少。

使用通配符的技巧

  • 不要过度使用通配符,如果其他操作符能达到同样的目的,应该使用其他的操作符。
  • 在确实需要使用统配符的时候,尽量不要把他们用到搜索模式的开始处。把通配符置于搜索模式的开始处,搜索起来是最慢的。
  • 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据。

用正则表达式进行搜索

使用MySQL正则表达式

基本字符匹配

例:检索列prod_name 包含文本1000的所有行。

SELECT prod_name 
FROM products 
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

xn_2022-08-25_09-44-36

:yellow_heart:REGEXP告诉MySQL,它后面跟着的是正则表达式。

SELECT prod_name 
FROM products 
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;

xn_2022-08-25_09-47-49

:warning:这个 . 可以说是正则表达式的一部分用途了。它表示匹配任一一个字符。

LIKE 和 REGEXP的区别

  • LIKE是匹配整个列,完全一样或者带通配符才可以匹配成功
  • REGEXP是只要列中包含文本就可以匹配成功。当然REGEXP也可以匹配整个列值。

区分大小写

  • 正则表达式的匹配是不区分大小写的。为区分大小写可使用BINARY关键字。

    • 如:WHERE prod_name REGEXP BINARY 'JetPack .000'

进行OR匹配

使用符号 |

SELECT prod_name 
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

xn_2022-08-25_10-04-21

匹配几个字符串之一

如果你只想匹配特定的字符,可以通过指定一组用 [ ] 括起来的字符完成。

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY  prod_name;

xn_2022-08-25_10-29-44

上面这个结果,还想说明一个问题,就是REGEXP虽然是可以匹配尾巴和头部的空格,但是你在单引号之间的空格还是需要你自己写上去的,单引号之间的空格你不写可是匹配不到结果的。

如上面结果所示,[]其实就是另一种方式的or语句,【123】其实也就是【1|2|3】。当然你写后者也是没有任何问题的。

下面我们再看一个例子:

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1|2|3 Ton'
ORDER BY  prod_name;

xn_2022-08-25_10-46-06

从结果上我们不难看出,你不用【】括起来,MySQL就会理解为1or2or3 Ton这样的形式

而下面那三行能匹配到也就是因为里面有1or2。

:warning:字符集也可以被否定:[ ^123 ]的意思就是匹配除了这些以外的任何东西。

这个 ^ 在【】括号里,就是除了的1,2,3,的意思,但是如果没有【】,那就是定位符是文本的开始位置的意思。

你比如说下面这两结果就是一样的哟:

xn_2022-08-25_13-46-48

xn_2022-08-25_13-46-57

你再看一个理解定位符^

xn_2022-08-25_13-48-51

当时我有一个这样的例子不明白,后来想明白了。

xn_2022-08-25_13-50-05

上面这个当时我就不明白,为什么都不包含1了,红框里还有,这就是REGEXP,你不包含1or2or3.你有其他的元素呀,所以你就得显示出来。

匹配范围

集合可以用来定义要匹配的一个或者多个字符。

例如:下面的集合将匹配数字0到9

【0123456789】

为了简化这种类型的集合,可使用 - 来定义一个范围。

【0-9】

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;

xn_2022-08-25_14-04-29

匹配特殊字符

也就是转义。我们要匹配特殊字符必须用\\为前导

SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;

xn_2022-08-25_14-10-04

:warning:多数的正则表达式实现使用单个反斜杠转义特殊字符,以便能使用这些字符本身。但是mysql要求两个反斜杠(mysql自己解释一个,正则表达式库解释一个)。

匹配字符类

存在找出你自己经常使用的数字,所有字母字符或所有数字字母字符等的匹配。为方便工作,可以使用预定义的字符集,称为字符类。

说明
【:alnum:】 任意字母和数字(同【a-zA-Z0-9】)
【:alpha:】 任意字符【a-zA-Z】
【:blank:】 空格和制表【\\t】
[:cntrl:] ASCII控制字符(ASCII0到31和127)
【:digit:】 任意数字(【0-9】)
【:graph:】 与【:print:】相同,但不包括空格
【:lower:】 任意小写字母【a-z】
【:print:】 任意可打印字符
【:punct:】 既不在【:alnum:】也不在【:cntrl:】中的任意字符
【: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} 匹配数目的范围
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;

xn_2022-08-25_15-21-40

:jack_o_lantern:我觉得难理解的点就在于,那个?的匹配(s后的?使s可选,因为?匹配他前面的的任何字符0次或者1次)。这也就是会有第一条结果的原因。

例:匹配连在一起的的4位数字。

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;

xn_2022-08-25_15-28-28

定位符

之前的例子都是匹配任一一个串中任意位置的文本。为了匹配特定位置的文本,我们需要使用定位符。

元字符 说明
^ 文本的开始
$ 文本的结束
[[:<:]] 词的开始
[[:>:]] 词的结束

例:找一个以数或小数点开始的所有产品

SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;

xn_2022-08-25_15-36-31

创建计算字段

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
关系型数据库 MySQL Linux
在Linux环境下备份Docker中的MySQL数据并传输到其他服务器以实现数据级别的容灾
以上就是在Linux环境下备份Docker中的MySQL数据并传输到其他服务器以实现数据级别的容灾的步骤。这个过程就像是一场接力赛,数据从MySQL数据库中接力棒一样传递到备份文件,再从备份文件传递到其他服务器,最后再传递回MySQL数据库。这样,即使在灾难发生时,我们也可以快速恢复数据,保证业务的正常运行。
64 28
|
1月前
|
存储 SQL 关系型数据库
【YashanDB知识库】MySQL迁移至崖山char类型数据自动补空格问题
**简介**:在MySQL迁移到崖山环境时,若字段类型为char(2),而应用存储的数据仅为&#39;0&#39;或&#39;1&#39;,查询时崖山会自动补空格。原因是mysql的sql_mode可能启用了PAD_CHAR_TO_FULL_LENGTH模式,导致保留CHAR类型尾随空格。解决方法是与应用确认数据需求,可将崖山环境中的char类型改为varchar类型以规避补空格问题,适用于所有版本。
|
25天前
|
SQL 关系型数据库 MySQL
【YashanDB知识库】字符集latin1的MySQL中文数据如何迁移到YashanDB
本文探讨了在使用YMP 23.2.1.3迁移MySQL Server字符集为latin1的中文数据至YashanDB时出现乱码的问题。问题根源在于MySQL latin1字符集存放的是实际utf8编码的数据,而YMP尚未支持此类场景。文章提供了两种解决方法:一是通过DBeaver直接迁移表数据;二是将MySQL表数据转换为Insert语句后手动插入YashanDB。同时指出,这两种方法适合单张表迁移,多表迁移可能存在兼容性问题,建议对问题表单独处理。
【YashanDB知识库】字符集latin1的MySQL中文数据如何迁移到YashanDB
|
1月前
|
缓存 NoSQL 关系型数据库
Redis和Mysql如何保证数据⼀致?
1. 先更新Mysql,再更新Redis,如果更新Redis失败,可能仍然不⼀致 2. 先删除Redis缓存数据,再更新Mysql,再次查询的时候在将数据添加到缓存中 这种⽅案能解决1 ⽅案的问题,但是在⾼并发下性能较低,⽽且仍然会出现数据不⼀致的问题,⽐如线程1删除了 Redis缓存数据,正在更新Mysql,此时另外⼀个查询再查询,那么就会把Mysql中⽼数据⼜查到 Redis中 1. 使用MQ异步同步, 保证数据的最终一致性 我们项目中会根据业务情况 , 使用不同的方案来解决Redis和Mysql的一致性问题 : 1. 对于一些一致性要求不高的场景 , 不做处理例如 : 用户行为数据 ,
|
2月前
|
Java 关系型数据库 MySQL
SpringBoot 通过集成 Flink CDC 来实时追踪 MySql 数据变动
通过详细的步骤和示例代码,您可以在 SpringBoot 项目中成功集成 Flink CDC,并实时追踪 MySQL 数据库的变动。
474 43
|
2月前
|
存储 SQL 关系型数据库
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分,包括表空间、段、区、页和行。表空间是最高逻辑层,包含多个段;段由若干个区组成,每个区包含64个连续的页,页用于存储多条行记录。文章还详细解析了Page结构,分为通用部分(文件头与文件尾)、数据记录部分和页目录部分。此外,文中探讨了行记录格式,包括四种行格式(Redundant、Compact、Dynamic和Compressed),重点介绍了Compact行记录格式及其溢出机制。最后,文章解释了不同行格式的特点及应用场景,帮助理解InnoDB存储引擎的工作原理。
MySQL底层概述—4.InnoDB数据文件
|
2月前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
78 9
|
2月前
|
监控 关系型数据库 MySQL
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
213 9
|
2月前
|
存储 关系型数据库 MySQL
MySQL进阶突击系列(09)数据磁盘存储模型 | 一行数据怎么存?
文中详细介绍了MySQL数据库中一行数据在磁盘上的存储机制,包括表空间、段、区、页和行的具体结构,以及如何设计和优化行数据存储以提高性能。
|
2月前
|
存储 SQL 关系型数据库
【YashanDB 知识库】MySQL 迁移至崖山 char 类型数据自动补空格问题
问题分类】功能使用 【关键字】char,char(1) 【问题描述】MySQL 迁移至崖山环境,字段类型源端和目标端都为 char(2),但应用存储的数据为'0'、'1',此时崖山查询该表字段时会自动补充空格 【问题原因分析】mysql 有 sql_mode 控制,检查是否启用了 PAD_CHAR_TO_FULL_LENGTH SQL 模式。如果启用了这个模式,MySQL 才会保留 CHAR 类型字段的尾随空格,默认没有启动。 #查看sql_mode mysql> SHOW VARIABLES LIKE 'sql_mode'; 【解决/规避方法】与应用确认存储的数据,正确定义数据

热门文章

最新文章