MySQL地址模糊查询如何忽略不存在的道路类型词汇正确返回结果

地址库的街道名字段没有存储道路类型后缀（比如Street、Avenue、Road这类通用道路类型词汇），现有查询逻辑存在匹配bug：用户搜纯街道名比如"Holland"、"land"时能正常出结果，一旦输入带道路类型的完整地址比如"Holland Street"，因为库内街道字段没有"Street"内容，就会返回空结果。

当前在用的原始查询语句：

SELECT * FROM {$wpdb->prefix}coverage
WHERE Street LIKE '%$_GET[Street]%'
AND NUMBER LIKE '$_GET[NUMBER]'
AND Province LIKE '$_GET[Province]'
AND TYPE != 'null'

额外提醒：这段原始SQL直接拼接用户传入的GET参数，存在严重SQL注入风险，改造时必须同步修复。

别在SQL层写复杂的兼容逻辑，维护性和性能都差。核心逻辑放在查询前处理：先把用户输入的街道名里所有道路类型类的冗余词全部删掉，再用清洗后的纯街道名去做匹配。

具体实现步骤 #

• 第一步：先整理一份覆盖本地场景的道路类型词表，把所有可能出现的道路类型、对应的缩写都列进去，示例如下，可根据实际业务场景补充：

$roadTypeList = [
    'Street', 'St', 'Avenue', 'Ave', 'Road', 'Rd', 'Boulevard', 'Blvd',
    'Lane', 'Ln', 'Drive', 'Dr', 'Court', 'Ct', 'Place', 'Pl', 'Terrace', 'Ter'
];

• 第二步：清洗用户传入的街道搜索参数
  接收参数后先统一转小写、拆成单个词，把命中道路类型词表的内容全部剔除，剩下的内容重新拼接成用于查询的有效关键词。PHP参考实现：

// 获取原始输入
$rawStreet = $_GET['Street'] ?? '';
// 去除首尾空格、统一转小写消除大小写差异
$normalizedStreet = trim(strtolower($rawStreet));
// 按空格拆成独立词汇
$wordArr = explode(' ', $normalizedStreet);
// 过滤掉所有道路类型词汇
$validWords = array_filter($wordArr, function($word) use ($roadTypeList) {
    $word = trim($word);
    if (!$word) return false;
    // 词库统一转小写做无大小写匹配
    return !in_array(strtolower($word), array_map('strtolower', $roadTypeList));
});
// 拼接得到最终用于查询的街道关键词
$searchStreet = implode(' ', $validWords);

举个实际例子：用户输入Holland Street，拆分后得到['holland', 'street']，命中词表的street会被过滤，最终查询关键词是holland，和库内存储的"Holland"街道就能正常匹配。即使用户输入错误重复带多个道路类型词，比如"Holland Street Ave"，清洗逻辑也能一次性把所有冗余词清掉，不影响结果。

• 第三步：替换原SQL逻辑，改用预处理语句杜绝注入风险
  不要直接把参数拼进SQL，用WordPress自带的$wpdb->prepare方法做参数预处理，门牌号、省份参数也同步做清洗，改造后的查询代码：

// 同步清洗其他查询参数
$searchNumber = trim($_GET['NUMBER'] ?? '');
$searchProvince = trim($_GET['Province'] ?? '');

// 生成预处理查询语句
$query = $wpdb->prepare(
    "SELECT * FROM {$wpdb->prefix}coverage
    WHERE Street LIKE %s
    AND NUMBER LIKE %s
    AND Province LIKE %s
    AND TYPE != 'null'",
    '%' . $wpdb->esc_like($searchStreet) . '%',
    '%' . $wpdb->esc_like($searchNumber) . '%',
    '%' . $wpdb->esc_like($searchProvince) . '%'
);
$results = $wpdb->get_results($query);

• 不要在SQL中用多层REPLACE()函数逐个替换道路类型词，词库扩容后SQL会极度冗余，查询性能差，后续维护成本极高
• 道路类型词表可以根据实际业务遇到的case持续补充，不需要调整核心查询逻辑
• 如果业务需要支持精确匹配门牌号、省份，只需要去掉对应参数LIKE前后的%通配符即可，不影响核心匹配逻辑

内容的提问来源于stack exchange，提问作者Miguel Falcón Castro