MySQL如何实现支持姓名任意输入顺序的模糊搜索功能？

关于CONCAT调整拼接顺序的问题 #

MySQL内置的CONCAT函数本身就支持自定义参数拼接顺序，你可以根据需求自由调整传入参数的先后位置：

• 要得到Michael Jordan的结果：SELECT CONCAT('Michael', ' ', 'Jordan')
• 要得到Jordan Michael的结果：SELECT CONCAT('Jordan', ' ', 'Michael')

姓名任意顺序搜索的实现方案 #

方案1：同时匹配两种拼接格式（最易实现，性能稳定） #

直接在WHERE条件中用OR同时匹配「名+姓」和「姓+名」两种拼接结果，修改后的SQL片段如下：

"SELECT `follow_user`.`follow_user_id`, 
        `follow_user`.`idFollower`,
        `follow_user`.`idFollowed`, 
        `users`.`idUsers`, 
        `users`.`nameUsers`, 
        `users`.`surnameUsers`, 
        `users`.`pictureUsers`, 
        `users`.`pictureUsersType`, 
        `users`.`folderUsers` 
 FROM `follow_user` 
        LEFT JOIN `users` ON `follow_user`.`idFollowed`=`users`.`idUsers` 
 WHERE `follow_user`.`follow_user_id`<=? 
    AND `follow_user`.`idFollower`=? 
    AND (
        CONCAT(`users`.`nameUsers`, ' ', `users`.`surnameUsers`) LIKE ? 
        OR CONCAT(`users`.`surnameUsers`, ' ', `users`.`nameUsers`) LIKE ?
    )
 ORDER BY CAST(`follow_user`.`follow_user_id` AS UNSIGNED INTEGER) DESC LIMIT ?, ?";

注意：该方案新增了1个参数占位符，传参时需要把用户输入的搜索词（前后加%通配符）给两个LIKE占位符各传一次。

方案2：拆分关键词分别匹配（支持部分输入场景） #

先在业务代码中把用户输入的搜索词按空格拆分为多个关键词片段，再分别匹配姓和名字段，适合支持用户只输入名/姓也能搜到结果的场景：

AND (
    (`users`.`nameUsers` LIKE ? AND `users`.`surnameUsers` LIKE ?)
    OR (`users`.`nameUsers` LIKE ? AND `users`.`surnameUsers` LIKE ?)
)

比如用户输入Jordan Michael，拆分后得到两个关键词，依次传入四个占位符即可。

方案3：正则匹配任意顺序（代码改动最小） #

用正则表达式同时匹配两个关键词，不需要新增占位符：

AND CONCAT(`users`.`nameUsers`, ' ', `users`.`surnameUsers`) REGEXP REPLACE(TRIM(?), ' ', '|')

如果要求必须同时包含所有关键词，可把正则格式调整为：

AND CONCAT(`users`.`nameUsers`, ' ', `users`.`surnameUsers`) REGEXP CONCAT('^(?=.*', REPLACE(TRIM(?), ' ', ')(?=.*'), ').*$')

常见踩坑提示 #

你之前修改拼接顺序后不生效，大概率是只保留了单种拼接格式，只能匹配对应顺序的输入，无法同时覆盖正、反序的搜索需求。另外使用LIKE时一定要给搜索关键词前后加%通配符，否则只能命中完全一致的内容。

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