运维实践｜MySQL查询时如何正确使用正则表达式

原创

Aion

发布于 2023-12-29 18:48:46

3351

发布于 2023-12-29 18:48:46

引言

正则表达式（Regular Expression），又被称规则表达式，在代码中常简写为regex、regexp或RE，是计算机科学的一个概念。正则表达式使用单个字符串来描述、匹配一系列匹配某个句法规则的字符串，通常被用来检索、替换那些符合某个模式（规则）的文本。

正则表达式是对字符串操作的一种逻辑公式，就是用事先定义好的一些特定字符、及这些特定字符的组合，组成一个“规则字符串”，这个“规则字符串”用来表达对字符串的一种过滤逻辑。它对字符串（包括普通字符（例如，a 到 z 之间的字母）和特殊字符（称为“元字符”））操作的一种逻辑公式，就是用事先定义好的一些特定字符、及这些特定字符的组合。

正则表达式的特点是：灵活性、逻辑性和功能性非常的强；可以迅速地用极简单的方式达到字符串的复杂控制；对于刚接触的人来说，比较晦涩难懂。所以正则表达式常被用在文本检索中。

理论实践操作

1 在MySQL中的正则表达式

REGEXP 是 MySQL 中的一个功能强大的正则表达式操作符，用于在字符串中执行模式匹配。它允许您使用正则表达式来搜索、替换或检查字符串。在MySQL中，正则表达式是一种为复杂搜索指定模式的强大方法。

2 正则表达式的类型

在MySQL中，有很多函数，我们常用的也就是 REGEXP，其他类型的很少使用。

名称	名称	用途
NOT REGEXP	不匹配到	这个同REGEXP相反，不在匹配内的找出来
REGEXP	匹配到	这个同NOT REGEXP相反，匹配内的找出来
REGEXP_INSTR()	匹配在字符串内的匹配项	从索引开始匹配符合条件的匹配项
REGEXP_LIKE()	匹配类似	匹配内的找出来
REGEXP_REPLACE()	匹配替换	替换匹配项并返回
REGEXP_SUBSTR()	匹配截取	截取匹配项并返回
RLIKE	匹配类似	匹配内的找出来

3 REGEXP 使用规则

格式

SELECT [字符串str] REGEXP [模式str];

规则描述

（1）如果字符串 [字符串str] 与 [模式str] 指定的正则表达式匹配，则返回1，否则返回0。

（2）如果 [字符串str] 或 [模式str] 为NULL，则返回值为NULL。

4 测试数据准备

创建表

CREATE TABLE `it_student` (
  `s_id` varchar(20) NOT NULL COMMENT 'ID',
  `s_name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `s_birth` varchar(20) NOT NULL DEFAULT '' COMMENT '生日',
  `s_sex` varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
  PRIMARY KEY (`s_id`) COMMENT '主键'
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
COMMENT '学生表';

测试数据

INSERT INTO `db_learn`.`it_student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('01', '赵雷', '1996-01-01', '男');
INSERT INTO `db_learn`.`it_student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('02', '钱电', '1996-12-21', '男');
INSERT INTO `db_learn`.`it_student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('03', '孙风', '1997-05-20', '男');
INSERT INTO `db_learn`.`it_student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('04', '李云', '1997-08-06', '男');
INSERT INTO `db_learn`.`it_student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('05', '周梅', '1996-12-01', '女');
INSERT INTO `db_learn`.`it_student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('06', '吴兰', '1997-03-01', '女');
INSERT INTO `db_learn`.`it_student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('07', '郑竹', '1996-07-01', '其他');
INSERT INTO `db_learn`.`it_student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('08', '王菊', '1996-01-20', '女');
INSERT INTO `db_learn`.`it_student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('22', 'Aion', '1999-01-20', '女');

5 REGEXP 实践

（1）MySQL中匹配枚举类

此类匹配很常见，例如是否，01，FM，男女等。下面就是我们常用的一些匹配规则：

SELECT * FROM it_student WHERE s_sex REGEXP '^[男]$';

执行结果如下：

（2）MySQL中模糊匹配编号中有2数据

这个也很常见，比较类似我们的like。他的用途是匹配前面的子表达式零次或一次，或指明一个非贪婪限定符。要匹配 ? 字符，请使用 \?。

SELECT * FROM it_student WHERE s_id REGEXP '^?2$';

执行结果如下：

到了这里，其实大家也都发现了端倪，按照固定格式只是变换掉了模式字符串就可以了。没错，这个是比较简单，后续就罗列一些常见的规则表达式。

（3）MySQL中匹配汉字

在有外国人的名字的时候，这个匹配很实用。我们可以将 Aion 这个用户过滤掉。

SELECT * FROM it_student WHERE s_name REGEXP '^[^ -~]';

执行结果如下：

有人可能有疑问，为什么不使用国籍来查询呢？这个主要取决于我们使用场景，如果你的数据库或应用程序需要处理中文字符，那么确保能够正确地匹配这些字符是很重要的。如果你的应用程序或网站面向全球用户，支持中文字符是重要的，以确保来自不同语言的用户能够顺利使用你的服务。还有就是目前大家都有可能上云，做数据迁移，你从一个支持中文字符的系统迁移到 MySQL，确保能够正确地处理这些字符是很重要的，就像我们上面的案例中所列举的。

6 REGEXP_REPLACE实践

如果你使用过replace，那么这个就很好理解了。我个人感觉这个就是一个加强版的replace。

SELECT REPLACE(s_name, 'Aion' , '替换Aion为六月暴雪') AS s_name FROM it_student WHERE s_name = 'Aion';
SELECT * FROM it_student; 

SELECT REGEXP_REPLACE(s_name, 'Aion' , '替换Aion为六月暴雪') AS s_name FROM it_student WHERE s_name = 'Aion';
SELECT * FROM it_student;

我使用 SELECT * FROM it_student; 这里加上这句是为了演示数据没有被替换，而是展示的结果数据被替换了。

7 正则表达式的性能

虽然现代的数据库系统（包括 MySQL）在处理正则表达式时已经相当高效，但在大规模数据集上执行复杂的正则表达式操作可能会对性能产生影响。确保你的查询是优化过的，以减少不必要的计算和I/O操作。是否需要使用 REGEXP 来匹配汉字取决于你的具体需求和场景。如果你需要处理中文字符，确保你的数据库、应用程序和查询都配置得当，以支持这些字符。