MySQL LIKE多词搜索问题：大表姓名模糊匹配需求咨询

问题背景 #

现有users表结构如下：

users

id   |   name
 1   |  Elizabeth Smith
 2   |  Smith Elizabeth
 3   |  Elizabeth  Smith -- 中间多空格
 4   |  Dr.Elizabeth Smith
 5   |  DR. Smith Elizabeth

前端搜索表单：

<form>
 <input type="text" name="name"><br>
 <input type="submit" value="Search Now" name="send" id="send" class="btn btn-danger">
</form>

需求：用户输入任意形式的姓名（单字、名+姓、姓+名、带多空格、带前缀如Dr），都能匹配所有符合的记录；表数据60K+，无法修改name字段清理空格。

当前使用LIKE '%Smith Elizabeth%'仅能返回1-2条结果，无法满足需求。

解决方案 #

1. 预处理用户输入 #

无论用哪种查询方式，先对用户输入做标准化处理，消除格式差异：

$rawInput = $_GET['name'] ?? '';
// 1. 去除首尾空格
$input = trim($rawInput);
// 2. 把多个连续空格替换成单个空格
$cleanInput = preg_replace('/\s+/', ' ', $input);
// 3. 统一转为小写（适配数据库不区分大小写的场景）
$lowerInput = strtolower($cleanInput);
// 4. 拆分关键词（用于多条件匹配）
$keywords = explode(' ', $lowerInput);

2. 方案一：全文索引查询（推荐，性能最优） #

针对60K+数据，全文索引的查询效率远高于LIKE或REGEXP，优先使用：

第一步：创建全文索引

ALTER TABLE users ADD FULLTEXT INDEX ft_idx_name (name);

第二步：构建布尔模式查询

将预处理后的关键词转为布尔模式语法，确保所有关键词都出现在记录中（不限制顺序）：

// 构建布尔模式查询字符串，每个关键词前加+表示必须包含
$booleanQuery = implode(' ', array_map(function($kw) {
    return "+$kw";
}, $keywords));

第三步：执行查询

SELECT * FROM users 
WHERE MATCH(name) AGAINST(? IN BOOLEAN MODE);

将$booleanQuery作为参数传入即可。

效果测试：

• 输入Elizabeth Smith → 匹配id1、2、3、4、5
• 输入Dr Smith → 匹配id4、5
• 输入Smith Elizabeth → 匹配id1、2、3、4、5
• 输入Elizabeth → 匹配所有包含Elizabeth的记录（id1、2、3、4、5）

3. 方案二：正则表达式查询（无需建索引，适合临时场景） #

如果无法创建全文索引，用正则匹配处理空格和顺序问题：

构建正则查询条件

针对每个关键词，生成独立的正则匹配规则，确保所有关键词都存在（不限制顺序）：

// 生成每个关键词的正则（匹配独立单词，忽略大小写）
$regexConditions = array_map(function($kw) {
    return "LOWER(name) REGEXP '[[:<:]]$kw[[:>:]]'";
}, $keywords);
// 拼接成AND条件
$whereClause = implode(' AND ', $regexConditions);

执行查询

SELECT * FROM users WHERE {$whereClause};

效果说明：

• [[:<:]]和[[:>:]]是MySQL的单词边界匹配符，避免部分匹配（比如不会把Elizabeth匹配成Liz）
• LOWER(name)确保大小写不敏感

如果要支持前缀匹配（比如输入Dr Smith匹配Dr.Elizabeth Smith），可以修改预处理步骤，把前缀统一为正则模式：

// 处理Dr/DR/Dr./DR.这类前缀，转为正则匹配
$lowerInput = preg_replace('/\b(dr)\b/', 'dr\.?', $lowerInput);

再生成正则条件即可匹配带点或不带点的前缀。

注意事项 #

• 全文索引的布尔模式不支持正则，所以如果需要匹配带点的前缀，优先用方案二，或者在预处理时把Dr.和Dr都作为关键词传入
• 若数据库排序规则是区分大小写的，需确保查询时统一转为小写或大写

内容的提问来源于stack exchange，提问作者Sing O Song