MySQL中有关正则表达式的问题

「爱情、让人受尽委屈。」 2022-11-27 07:10 109阅读 0赞

### MySQL中有关正则表达式的问题 ###

**引言：**  
今天在MySQL中使用正则表达式进行查询操作，发现了一个比较难受的问题，结果在模拟问题的时候又遇到一个新的问题，现在将这两个问题统一记录。

--------------------

**需求场景描述：**  
从数据库表当中查询出姓名里面包含“张”和“三”的记录。

这个功能使用正则和通配符都可以实现，但是如果条件更苛刻一些，正则的优势可能更明显一些，所以准备用正则搜索实现这个功能。网友提供的可以匹配“张”和“三”正则表达式为`^(?=.*?张)(?=.*?三).+$`，将这个正则表达式嵌入 MySQL 语句中也如愿实现了预设功能，但是后来项目环境发生了改变，MySQL数据库版本由 8.0.15 更换为 5.7.26。再次使用这个命令的时候报错了。错误信息如下：

ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp

使用翻译软件看了一下：

> 从regexp获取错误“重复操作符操作数无效”

在百度上查了相关的资料，得到的解释大概是说 mysql 的正则表达式不支持 non-capturing group (?:XYZ) 这种语法。但是作为一个正则表达式小白，对这个解释没有任何解决方案，宁愿依靠数据库升级解决问题。

然而，就在模拟上面的问题的时候，又发现了一个新的问题。  
  
创建一个新的数据库表，简化了表结构，试图突出主要的匹配查询问题。但是创建好新的数据库表然后查询的时候，报的错误不一样了。

ERROR 1267 (HY000): Illegal mix of collations
    (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
    for operation 'regexp'

通过错误信息分析，似乎是和编码方式有关系，所以通过`show create table`的命令查看了完整的建表信息：

CREATE TABLE `t_student` (
      `name` char(5) DEFAULT NULL,
      `age` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

再去查看另外其他表创建的信息：

CREATE TABLE `t_student` (
      `student_name` char(5) COLLATE utf8mb4_bin DEFAULT NULL,
      `class_name` char(5) COLLATE utf8mb4_bin DEFAULT NULL,
      `age` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC

按照新的字符排序规则创建好数据库表之后，再执行下面的查询就没有问题了。

select student_name,age from t_student where student_name regexp '^(?=.*?张)(?=.*?三).+$';

综上所述：

1.  MySQL中使用正则表达式查询的语法是：`select 字段 from 表 where 字段 regexp 正则表达式;`。
2.  数据库中如果涉及中文存储，建议使用 utf8mb4。
3.  MySQL5.7.26版本的MySQL数据库不支持在正则中使用一些特殊字符。

总结的可能不到位甚至有理解偏差，希望朋友们指正。