MySQL实现两张表的城市名称模糊匹配问题求助

问题背景 #

现有两张MySQL表：

1. list_city：存储标准欧洲城市列表，数据示例：

=========================
= ID = CITY   = COUNTRY =
=========================
= 1  = LONDON = UK      =
= 2  = PARIS  = France  =
= 3  = ROME   = Italy   =
=========================

2. data_customer：包含客户ID和存在大小写混乱、拼写错误的城市字段，数据示例：

====================
= ID_CUST = CITY   =
====================
= 012AGH  = paris  =
= 2X4BV   = London =
= M3RT45  = romE   =
= 1F546   = Lndon  =
= 345GC   = PArs   =
= 54A78   = roma   =
====================

需要将data_customer中的城市匹配到list_city的标准城市名，得到规范后的结果。

原SQL语句未达预期，核心问题是匹配方向错误（用客户表城市包含标准城市，而非反向）以及大小写敏感导致的匹配失效。以下提供三种纯MySQL实现方案：

方案1：子串匹配+大小写统一 #

适合处理大小写混乱、拼写缺失少量字符的场景，逻辑为：将两张表的城市名统一转为小写后，判断标准城市名是否包含客户表的城市名，同时添加长度过滤减少误匹配。

SELECT 
    c1.ID_CUST, 
    c2.CITY
FROM data_customer c1
INNER JOIN list_city c2 
    ON LOWER(c2.CITY) LIKE CONCAT('%', LOWER(c1.CITY), '%')
    -- 过滤长度差过大的匹配，避免误匹配
    AND LENGTH(c2.CITY) - LENGTH(c1.CITY) <= 2
ORDER BY c1.ID_CUST;

方案2：编辑距离匹配（Levenshtein Distance） #

适合处理各类拼写错误（字符缺失、替换、顺序错误），需先创建编辑距离计算函数，再通过阈值判断匹配度。

步骤1：创建Levenshtein函数 #

DELIMITER //
CREATE FUNCTION LEVENSHTEIN(s1 VARCHAR(255), s2 VARCHAR(255)) 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE s1_len, s2_len, i, j, c, c_temp INT;
    DECLARE s1_char CHAR;
    DECLARE cv0, cv1 VARBINARY(256);
    
    SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2);
    IF s1_len = 0 THEN RETURN s2_len; END IF;
    IF s2_len = 0 THEN RETURN s1_len; END IF;
    
    SET cv0 = CAST(0 AS VARBINARY(256));
    SET i = 0;
    WHILE i < s2_len DO
        SET cv0 = CONCAT(cv0, CAST(i+1 AS VARBINARY(1)));
        SET i = i + 1;
    END WHILE;
    
    SET i = 0;
    WHILE i < s1_len DO
        SET s1_char = SUBSTRING(s1, i+1, 1);
        SET cv1 = CAST(i+1 AS VARBINARY(1));
        SET j = 0;
        WHILE j < s2_len DO
            SET c = IF(s1_char = SUBSTRING(s2, j+1, 1), 0, 1);
            SET c_temp = CAST(SUBSTRING(cv0, j+1, 1) AS INT) + c;
            SET cv1 = CONCAT(cv1, CAST(IF(c_temp > CAST(SUBSTRING(cv1, j+1, 1) AS INT) + 1, CAST(SUBSTRING(cv1, j+1, 1) AS INT) + 1, c_temp) AS VARBINARY(1)));
            SET j = j + 1;
        END WHILE;
        SET cv0 = cv1;
        SET i = i + 1;
    END WHILE;
    
    RETURN CAST(SUBSTRING(cv0, s2_len+1, 1) AS INT);
END //
DELIMITER ;

步骤2：执行匹配查询 #

设置编辑距离阈值为2（可根据实际拼写错误程度调整）：

SELECT 
    c1.ID_CUST, 
    c2.CITY
FROM data_customer c1
INNER JOIN list_city c2 
    ON LEVENSHTEIN(LOWER(c1.CITY), LOWER(c2.CITY)) <= 2
ORDER BY c1.ID_CUST;

方案3：发音匹配（SOUNDEX） #

适合发音相似但拼写不同的场景，利用MySQL内置的SOUNDEX函数将字符串转换为发音编码，匹配编码相同的记录。

SELECT 
    c1.ID_CUST, 
    c2.CITY
FROM data_customer c1
INNER JOIN list_city c2 
    ON SOUNDEX(c1.CITY) = SOUNDEX(c2.CITY)
ORDER BY c1.ID_CUST;

方案选择建议 #

• 若仅存在大小写混乱、少量字符缺失：优先使用方案1，性能最优。
• 若存在多种拼写错误：使用方案2，匹配精度最高，但需创建函数。
• 若拼写错误源于发音相似：使用方案3，实现最简单。

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