MySQL如何实现支持姓/名不同输入顺序的全名模糊查询

问题根源 #

你当前的查询存在两个问题：

1. CONCAT_WS函数用法错误：该函数第一个参数需要传入分隔符，你原写法CONCAT_WS(c.last_name,' ',c.first_name)会把姓氏作为分隔符，拼接空格和名字，得到的结果不符合预期。
2. 仅匹配了姓+空格+名这一种姓名拼接顺序，所以输入名+姓格式的搜索词时无法命中结果。

修正方案 #

方案1：直接匹配两种姓名拼接顺序（简单快速） #

同时判断「姓+名」和「名+姓」两种格式是否匹配搜索词即可，修正后的完整SQL如下：

SELECT DISTINCT p.pm_id, CONCAT(c.last_name, ' ', c.first_name ) AS NAME
FROM ap_plans p
INNER JOIN ap_contacts c ON c.id = p.pm_id 
WHERE p.is_deleted=0 
  AND p.URL = 'gettogether'  
  AND p.internal_project = 0
  AND c.staff_status=0 
  AND (
    CONCAT_WS(' ', c.last_name, c.first_name) LIKE '%Egill Olafsson%'
    OR CONCAT_WS(' ', c.first_name, c.last_name) LIKE '%Egill Olafsson%'
  )
ORDER BY NAME 

这里顺便把原SQL的隐式表连接改成了显式INNER JOIN，可读性更强。

方案2：拆分搜索词分别匹配（准确率更高） #

如果希望避免空格、特殊字符干扰，可以先把搜索词按空格拆分为两个独立关键词，分别匹配姓氏和名字字段，不管顺序：

SELECT DISTINCT p.pm_id, CONCAT(c.last_name, ' ', c.first_name ) AS NAME
FROM ap_plans p
INNER JOIN ap_contacts c ON c.id = p.pm_id 
WHERE p.is_deleted=0 
  AND p.URL = 'gettogether'  
  AND p.internal_project = 0
  AND c.staff_status=0 
  AND (
    (c.last_name LIKE '%Egill%' AND c.first_name LIKE '%Olafsson%')
    OR (c.last_name LIKE '%Olafsson%' AND c.first_name LIKE '%Egill%')
  )
ORDER BY NAME 

优化建议 #

• 如果数据量较大，频繁使用前后带%的模糊匹配会影响查询性能，可以考虑为姓名字段建立MySQL全文索引，使用MATCH() AGAINST()语法做搜索，效率更高。
• 提前对用户输入的搜索词做标准化处理，比如把连续空格替换为单个空格、过滤特殊字符，能进一步提升匹配准确率。

内容的提问来源于stack exchange，提问作者Musaveer Sharief