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

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数据库不支持在正则中使用一些特殊字符。

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