MySQL如何实现规范姓名与带冗余标点的脏全名字段匹配查询

问题背景 #

Name表的Fullname字段存储了夹杂冗余空格、标点的非规范姓名数据，示例数据：

id Fullname
1  John , Doe

上述id=1的记录中，姓名字段值John , Doe的名、姓之间存在多余空格与逗号。直接传入规范格式姓名（如John Doe/Doe John）用如下LIKE语句无法命中结果：

-- 正序匹配无法命中
SELECT * FROM Name WHERE `FullName` LIKE '%John Doe%' 

-- 倒序匹配无法命中
SELECT * FROM Name WHERE `FullName` LIKE '%Doe John%' 

匹配失败的核心原因是存储值中夹杂的逗号、多余空格打破了John和Doe的连续匹配规则。

实现方案 #

核心逻辑是先清洗字段值中的冗余标点、多余空格，再做匹配，根据数据量和查询频率可选择两种实现方式：

方案1：查询时实时清洗字段（适合小数据量、低查询频率场景） #

利用MySQL原生REGEXP_REPLACE正则替换函数，将字段值中所有非姓名有效字符统一替换为单个空格，去除首尾空格后再做匹配，同时兼容正序、倒序的姓名输入：

SELECT *
FROM Name
WHERE 
  -- 清洗规则：连续非字母字符替换为单空格，再去除首尾空格
  TRIM(REGEXP_REPLACE(`Fullname`, '[^a-zA-Z]+', ' ')) LIKE CONCAT('%', 'John Doe', '%')
  OR
  TRIM(REGEXP_REPLACE(`Fullname`, '[^a-zA-Z]+', ' ')) LIKE CONCAT('%', 'Doe John', '%');

• 效果验证：原脏值John , Doe经过清洗后得到John Doe，可直接被第一个LIKE条件命中。
• 适配中文：如果姓名包含中文字符，将正则匹配规则修改为[^a-zA-Z\u4e00-\u9fa5]+即可保留中文字符，过滤冗余标点空格。

方案2：持久化清洗结果（适合大数据量、高查询频率场景） #

查询时对字段做函数运算会导致索引失效，数据量超过10万条时查询性能会明显下降，此时建议新增专门存储清洗后姓名的字段，提前完成数据清洗，查询时直接匹配该字段：

1. 新增清洗字段：

ALTER TABLE Name ADD COLUMN `fullname_clean` VARCHAR(255) COMMENT '清洗后无冗余标点的规范姓名';

2. 批量更新存量数据：

UPDATE Name 
SET `fullname_clean` = TRIM(REGEXP_REPLACE(`Fullname`, '[^a-zA-Z]+', ' '));

3. 给清洗字段加索引后，直接查询该字段即可，性能和普通模糊查询一致：

SELECT * FROM Name 
WHERE `fullname_clean` LIKE '%John Doe%' OR `fullname_clean` LIKE '%Doe John%';

优化提示 #

• 如果不需要支持姓名倒序匹配，直接删除OR连接的倒序匹配条件即可，减少误匹配概率。
• 如果需要支持单字/名单部分匹配，调整LIKE后的通配符位置即可，比如匹配John可写为LIKE '%John%'。

内容的提问来源于stack exchange，提问作者Happy to know