首页 > 数据库 >使用MySQL之用正则表达式进行搜索

使用MySQL之用正则表达式进行搜索

时间：2024-10-20 19:13:21浏览次数：1  
标签：字符 匹配 name 正则表达式 之用 MySQL prod your

1. 正则表达式介绍

正则表达式是用来匹配文本的特殊的串（字符集合）。

如果你想从一个文本文件中提取电话号码，可以使用正则表达式。

如果你需要查找名字中间有数字的所有文件，可以使用一个正则表达式。

如果你想在一个文本块中找到所有重复的单词，可以使用一个正则表达式。

如果你想替换一个页面中的所有URL为这些URL的实际HTML链接，也可以使用一个正则表达式（对于最后这个例子，或者是两个正则表达式）。

与任意语言一样，正则表达式具有你必须学习的特殊的语法和指令。

补充：

学习更多内容：

完全覆盖正则表达式的内容超出了本书的范围。虽然基础知识都在这里做了介绍，但对正则表达式更为透彻的介绍可能还需要参阅《正则表达式必知必会》这本书。

2. 使用MySQL正则表达式

正则表达式的作用是匹配文本，将一个模式（正则表达式）与一个文本串进行比较。

MySQL用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式，过滤SELECT检索出的数据。

补充：

仅为正则表达式语言的一个子集：

如果你熟悉正则表达式，需要注意：MySQL仅支持多数正则表达式实现的一个很小的子集。

2.1 基本字符匹配

下面的语句检索列prod_name包含文本1000的所有行：

select prod_name from products
where prod_name regexp '1000'
order by prod_name;

输出如下：

img

除关键字LIKE被REGEXP替代外，这条语句看上去非常像使用LIKE的语句。它告诉MySQL：REGEXP后所跟的东西作为正则表达式（与文字正文1000匹配的一个正则表达式）处理。

为什么要费力地使用正则表达式？在刚才的例子中，正则表达式确实没有带来太多好处（可能还会降低性能），不过，请考虑下面的例子：

select prod_name from products
where prod_name regexp '.000'
order by prod_name;

输出如下：

img

这里使用了正则表达式 .000 。 . 是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符，因此，1000和2000都匹配且返回。

当然，这个特殊的例子也可以用LIKE和通配符来完成。

select prod_name from products
where prod_name like '%000'
order by prod_name;

输出如下：

img

补充：

  • LIKE 与 REGEXP：

    在LIKE和REGEXP之间有一个重要的差别。请看以下两条语句：

    select prod_name from products
where prod_name like '1000'
order by prod_name;

    select prod_name from products
where prod_name regexp '1000'
order by prod_name;

    如果执行上述两条语句，会发现第一条语句不返回数据，而第二条语句返回一行。为什么？

    • LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。

    • 而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别。

    • 那么，REGEXP能不能用来匹配整个列值（从而起与LIKE相同的作用）？答案是肯定的，使用^和$定位符（anchor）即可，后面会介绍。

  • 匹配不区分大小写：

    MySQL中的正则表达式匹配（自版本3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。

2.2 进行OR匹配

为搜索两个串之一（或者为这个串，或者为另一个串），使用 | 。

比如：

select prod_name from products 
where prod_name regexp '1000|2000'
order by prod_name;

输出如下：

img

语句中使用了正则表达式 1000|2000 。|为正则表达式的OR操作符。它表示匹配其中之一，因此1000和2000都匹配并返回。

插一句题外话：

当我把代码写成这样：

select prod_name from products 
where prod_name regexp '1000 | 2000'
order by prod_name;

即在 | 的两边各加一个空格时，输出结果是这样的：

img

只检索出了一条数据，说明正则表达式对空格是敏感的。

回归正题

使用 | 从功能上类似于在SELECT语句中使用OR语句，多个OR条件可并入单个正则表达式。

补充：

两个以上的OR条件：

可以给出两个以上的OR条件。例如，'1000 | 2000 | 3000' 将匹配1000或2000或3000。

2.3 匹配几个字符之一

匹配任何单一字符。但是，如果你只想匹配特定的字符，怎么办？

可通过指定一组用 [ 和 ] 括起来的字符来完成。

比如：

select prod_name from products 
where prod_name regexp '[123] Ton'
order by prod_name;

输出如下：

img

这里，使用了正则表达式[123] Ton。[123]定义一组字符，它的意思是匹配1或2或3，因此，1 ton和2 ton都匹配且返回（没有3 ton）。

正如所见， [] 是另一种形式的OR语句。

事实上，正则表达式 [123] Ton 为 [1|2|3] Ton 的缩写，也可以使用后者。但是，需要用[]来定义OR语句查找什么。

为更好地理解这一点，请看下面的例子：

select prod_name from products
where prod_name regexp '1|2|3 Ton'
order by prod_name;

输出如下：

img

这并不是期望的输出。两个要求的行被检索出来，但还检索出了另外3行。之所以这样是由于MySQL假定你的意思是'1'或'2'或'3 ton'。除非把字符|括在一个集合中，否则它将应用于整个串。

字符集合也可以被否定，即，它们将匹配除指定字符外的任何东西。

为否定一个字符集，在集合的开始处放置一个 ^ 即可。因此，尽管[123]匹配字符1、2或3，但[^123]却匹配除这些字符外的任何东西。

2.4 匹配范围

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

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

[0123456789]

为简化这种类型的集合，可使用 - 来定义一个范围。下面的式子功能上等同于上述数字列表：

[0-9]

范围不限于完整的集合，[1-3] 和 [6-9] 也是合法的范围。此外，范围不一定只是数值的，[a-z] 匹配任意字母字符。

比如：

select prod_name from products
where prod_name regexp '[1-5] Ton'
order by prod_name;

输出如下：

img

这里使用正则表达式 [1-5] Ton。[1-5] 定义了一个范围，这个表达式意思是匹配1到5，因此返回3个匹配行。由于5 ton匹配，所以返回.5 ton。

2.5 匹配特殊字符

正则表达式语言由具有特定含义的特殊字符构成。我们已经看到 . 、[] 、| 和 - 等，还有其他一些字符。

请问，如果你需要匹配这些字符，应该怎么办呢？例如，如果要找出包含.字符的值，怎样搜索？请看下面的例子：

select vend_id, vend_name from vendors
where vend_name regexp '.'
order by vend_name;

输出如下：

img

这并不是期望的输出，. 匹配任意字符，因此每个行都被检索出来。

为了匹配特殊字符，必须用 \\ 为前导。\\- 表示查找 -，\\.表示查找 . 。

比如：

select vend_name from vendors
where vend_name regexp '\\.'
order by vend_name;

输出如下：

img

这才是期望的输出。\\. 匹配 .，所以只检索出一行。这种处理就是所谓的转义（escaping），正则表达式内具有特殊意义的所有字符都必须以这种方式转义。这包括 .、|、[]以及迄今为止使用过的其他特殊字符。

\\也用来引用元字符（具有特殊含义的字符），如表9-1所列。

img

补充：

  • 匹配 \：

    为了匹配反斜杠（\）字符本身，需要使用\\\。

  • \或 \\?

    多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释另一个），具体来说，MySQL会将第一个反斜杠视为转义符，处理后会留下一个反斜杠，然后再传递给正则表达式库进行处理。

    例如，如果你想在MySQL中匹配一个点（.）字符，你需要使用\\.。这是因为MySQL会首先将\\解释为一个反斜杠，然后将\.传递给正则表达式库，该库会将其解释为一个点字符的字面意义。

2.6 匹配字符类

存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作，可以使用预定义的字符集，称为字符类（character class）。表9-2列出字符类以及它们的含义。

img

补充：

在MySQL中，正则表达式语法要求字符类必须用双括号包裹起来。

  1. 匹配任何字母或数字（[:alnum:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:alnum:]]';

    这个查询会匹配 your_column 中包含至少一个字母或数字的行。

  2. 匹配所有字母字符（[:alpha:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:alpha:]]';

    这个查询会选出 your_column 中包含至少一个字母（无论大小写）的行。

  3. 匹配所有空格或制表符（[:blank:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:blank:]]';

    这个查询会选出包含空格或制表符的行。

  4. 匹配所有控制字符（[:cntrl:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:cntrl:]]';

    这个查询会匹配包含ASCII控制字符的行（ASCII 0到31和127之间的字符）

  5. 匹配所有数字（[:digit:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:digit:]]';

    这个查询会选出 your_column 中包含数字的行，效果与 [0-9] 相同。

  6. 匹配所有小写字母（[:lower:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:lower:]]';

    这个查询会选出 your_column 中包含至少一个小写字母的行。

  7. 匹配所有大写字母（[:upper:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:upper:]]';

    这个查询会选出 your_column 中包含至少一个大写字母的行。

  8. 匹配所有可打印字符（[:print:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:print:]]';

    这个查询会选出包含可打印字符的行，包括空格。

  9. 匹配所有标点符号（[:punct:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:punct:]]';

    这个查询会选出包含标点符号的行，比如 !、.、? 等。

  10. 匹配所有十六进制数字（[:xdigit:]）：

    SELECT * FROM your_table 
WHERE your_column REGEXP '[[:xdigit:]]';

    这个查询会选出 your_column 中包含十六进制数字的行（即0-9和a-f/A-F）。

2.7 匹配多个实例

目前为止使用的所有正则表达式都试图匹配单次出现。如果存在一个匹配，该行被检索出来，如果不存在，检索不出任何行。但有时需要对匹配的数目进行更强的控制。例如，你可能需要寻找所有的数，不管数中包含多少数字，或者你可能想寻找一个单词并且还能够适应一个尾随的s（如果存在），等等。

这可以用表9-3列出的正则表达式重复元字符来完成。

img

举几个例子来进行说明：

  • 第一个例子

    select prod_name from products
where prod_name regexp '\\([0-9] sticks?\\)'
order by prod_name;

    输出如下：

    img

    正则表达式 \\([0-9] sticks?\\) 需要解说一下。

    \\( 匹配 (，[0-9]匹配任意数字（这个例子中为1和5），sticks?匹配stick和sticks（s后的?使s可选，因为 ? 匹配它前面的任何字符的0次或1次出现），\\) 匹配 )。没有?，匹配stick和sticks会非常困难。

    注意：在正则表达式中，括号 () 是特殊字符，通常用于分组（即捕获组）。如果你想匹配字面上的括号，而不是使用它们的分组功能，则需要对它们进行转义。

  • 另一个例子，这次打算匹配连在一起的4位数字：

    select prod_name from products
where prod_name regexp '[[:digit:]]{4}'
order by prod_name;

    输出如下：

    img

    如前所述，[:digit:] 匹配任意数字，因而它为数字的一个集合。{4}确切地要求它前面的字符（任意数字）出现4次，所以[[:digit:]]{4} 匹配连在一起的任意4位数字。

  • 需要注意的是，在使用正则表达式时，编写某个特殊的表达式几乎总是有不止一种方法。上面的例子也可以如下编写：

    select prod_name from products
where prod_name regexp '[0-9][0-9][0-9][0-9]'
order by prod_name;

    输出如下：

    img

2.8 定位符

目前为止的所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本，需要使用表9-4列出的定位符。

img

例如，如果你想找出以一个数（包括以小数点开始的数）开始的所有产品，怎么办？简单搜索 [0-9\\.]（或 [[:digit:]\\.] ）不行，因为它将在文本内任意位置查找匹配。解决办法是使用 ^ 定位符，如下所示：

select prod_name from products
where prod_name regexp '^[0-9\\.]'
order by prod_name;

输出如下：

img

^ 匹配串的开始。因此，^[0-9\\.] 只在.或任意数字为串中第一个字符时才匹配它们。没有 ^，则还要多检索出4个别的行（那些中间有数字的行）。

补充：

  • ^的双重用途：

    ^ 有两种用法。在集合中（用[ 和 ] 定义），用它来否定该集合，否则，用来指串的开始处。

  • 使REGEXP起类似LIKE的作用：

    前面说过，LIKE和REGEXP的不同在于，LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与LIKE一样。

  • 简单的正则表达式测试：

    可以在不使用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0（没有匹配）或1（匹配）。可以用带文字串的REGEXP来测试表达式，并试验它们。相应的语法如下：

    select 'hello' regexp '[0-9]';

    输出如下：

    img

    这个例子显然将返回0（因为文本hello中没有数字）。

标签：字符,匹配,name,正则表达式,之用,MySQL,prod,your
From： https://www.cnblogs.com/hisun9/p/18487643

相关文章

  • mysql索引
       2.1索引概述2.1.1介绍索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。在无索引情况下，就需要......
  • RockyLinux安装MySQL
    本文介绍RockyLinux使用dnf在线安装MySQL并修改密码设置远程登陆。本博客使用RetHat系的新版本系统，如使用Debian系的系统如Ubuntu，只需使用apt安装，其余部分类似。1、使用如下命令安装MySQLsudodnfinstallmysql-server2、安装完成后可以使用systemctl工具对MySQL服务进行控......
  • MySQL 5.7 Reference Manual Optimization Overview（翻译）
    使用Kimi翻译文档地址：https://dev.mysql.com/doc/refman/5.7/en/optimize-overview.html目录8.1OptimizationOverview在数据库层面进行优化在硬件层面进行优化平衡可移植性和性能8.1OptimizationOverview数据库性能取决于数据库层面的多个因素，例如表、查询和配置设置。这......
  • MySQL 5.7 Reference Manual Optimizing SQL Statements（翻译）
    使用Kimi翻译文档地址：https://dev.mysql.com/doc/refman/5.7/en/optimize-overview.html目录8.2OptimizationOverview8.2.1优化SELECT语句8.2.2优化子查询、派生表和视图引用8.2.3优化INFORMATION_SCHEMA查询8.2.4优化数据变更语句8.2.5优化数据库权限8.2.6其他优化技巧......
  • SQL Injection | MySQL 手工注入全流程
    0x01：MySQL手工注入——理论篇手工注入MySQL数据库，一般分为以下五个阶段，如下图所示：第一阶段-判断注入点：在本阶段中，我们需要判断注入点的数据类型（数字型、字符型、搜索型、XX型）与后端查询方式，并使用对应的SQL语句进行测试，判断出目标是否存在注入点。第二阶段-......
  • 深入优化MySQL深度分页：从第10000页出发，Java模拟高效分页技巧
    在深度分页（如LIMIT99990,10）中，SQL的优化方式主要是为了避免MySQL在执行时需要扫描大量的无用数据，从而提高查询效率。以下是几种常见的SQL层面的优化方法：1.使用覆盖索引优化覆盖索引是一种索引优化技术，即查询只通过索引就可以获得所需的数据，而不需要访问实际的数据......
  • Mysql高级-day01
    Mysql高级-day01MySQL高级课程简介序号Day01Day02Day03Day041Linux系统安装MySQL体系结构应用优化MySQL常用工具2索引存储引擎查询缓存优化MySQL日志3视图优化SQL步骤内存管理及优化MySQL主从复制4存储过程和函数索引使用MySQL锁问题综......
  • 一文彻底弄懂MySQL的MVCC多版本控制器
    InnoDB的MVCC（Multi-VersionConcurrencyControl，多版本并发控制）是MySQL实现高并发事务处理的一种机制。通过MVCC，InnoDB可以在高并发环境下支持事务隔离，并提供非阻塞的读操作，从而避免锁定所有读操作带来的性能瓶颈。MVCC允许事务在不加锁的情况下读取数据，保证了性能和一......
  • mysql8.0安装和配置教程
    1.下载地址：https://dev.mysql.com/downloads/mysql/进入界面是最新的版本，这里下载。1.安装MySQL2.下载3.选择自定义安装4.安装结束5.配置mysql环境变量我使用系统Windows11安装mysql选择自定义安装路径是：D:\ProgramFiles\MySQLServer8.0右键......
  • MySQL数据库备份与恢复
    MySQL数据库的备份与恢复是数据库管理的重要部分，它可以帮助我们保护数据不受硬件故障、数据损坏、人为错误等因素的影响。以下是MySQL数据备份与恢复的详细步骤和代码示例。数据备份1.使用 mysqldump 工具mysqldump是MySQL自带的一个强大的命令行工具，用于导出数据库......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99