咨询：查询因字段拼写错误导致的MySQL重复客户数据方案

一、MySQL 端实现思路 #

1. 自定义字符串相似度函数 #

MySQL默认没有内置编辑距离计算函数，先创建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 = 0x00;
    FOR i FROM 1 TO s2_len DO
        SET cv0 = CONCAT(cv0, UNHEX(HEX(i)));
    END FOR;
    
    FOR i FROM 1 TO s1_len DO
        SET s1_char = SUBSTRING(s1, i, 1), cv1 = UNHEX(HEX(i));
        FOR j FROM 1 TO s2_len DO
            SET c = IF(s1_char = SUBSTRING(s2, j, 1), 0, 1);
            SET c_temp = CONV(HEX(SUBSTRING(cv0, j, 1)), 16, 10) + c;
            SET cv1 = CONCAT(cv1, UNHEX(HEX(LEAST(
                CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + 1,
                c_temp,
                CONV(HEX(SUBSTRING(cv0, j+1, 1)), 16, 10) + 1
            ))));
        END FOR;
        SET cv0 = cv1;
    END FOR;
    
    RETURN CONV(HEX(SUBSTRING(cv0, s2_len+1, 1)), 16, 10);
END //
DELIMITER ;

2. 加权得分式匹配规则 #

放弃硬条件判断，给每个匹配维度设置分数，总分超过阈值则标记为疑似重复。示例查询：

SELECT
    a.id AS id1, b.id AS id2,
    -- 计算各项匹配得分
    (CASE WHEN a.last_name = b.last_name THEN 30 ELSE 0 END) +
    (CASE WHEN LOCATE(b.first_name, a.first_name) > 0 OR LOCATE(a.first_name, b.first_name) > 0 THEN 20 ELSE 0 END) +
    (CASE WHEN a.street = b.street THEN 25 ELSE 0 END) +
    (CASE WHEN a.zip = b.zip THEN 15 ELSE 0 END) +
    (CASE WHEN LOCATE(b.location, a.location) > 0 OR LOCATE(a.location, b.location) > 0 THEN 10 ELSE 0 END) AS total_score
FROM customers a
JOIN customers b ON a.id < b.id  -- 避免重复比对同一组记录
HAVING total_score >= 70  -- 可根据业务调整阈值
ORDER BY total_score DESC;

该规则能覆盖你提供的示例场景：两条记录姓氏一致（+30）、名字互相包含（+20）、街道一致（+25）、邮编一致（+15），总分90，会被标记为疑似重复。

3. 高频姓氏误判规避 #

针对roberts这类常见姓氏，可提高阈值或增加额外限制，比如要求名字编辑距离≤2：

HAVING total_score >= 80 AND LEVENSHTEIN(a.first_name, b.first_name) <= 2

二、C# 端增强方案 #

C#的字符串处理灵活性更高，适合做精细化重复检测：

1. 实现字符串相似度计算 #

自己实现Damerau-Levenshtein距离算法，计算字符串相似度：

public static int DamerauLevenshteinDistance(string s1, string s2)
{
    int len1 = s1.Length, len2 = s2.Length;
    int[,] d = new int[len1 + 1, len2 + 1];

    for (int i = 0; i <= len1; d[i, 0] = i++) ;
    for (int j = 0; j <= len2; d[0, j] = j++) ;

    for (int i = 1; i <= len1; i++)
    {
        for (int j = 1; j <= len2; j++)
        {
            int cost = s1[i - 1] == s2[j - 1] ? 0 : 1;
            d[i, j] = Math.Min(Math.Min(d[i - 1, j] + 1, d[i, j - 1] + 1), d[i - 1, j - 1] + cost);

            if (i > 1 && j > 1 && s1[i - 1] == s2[j - 2] && s1[i - 2] == s2[j - 1])
            {
                d[i, j] = Math.Min(d[i, j], d[i - 2, j - 2] + cost);
            }
        }
    }
    return d[len1, len2];
}

// 计算相似度百分比
public static double CalculateSimilarity(string s1, string s2)
{
    int distance = DamerauLevenshteinDistance(s1.ToLower(), s2.ToLower());
    int maxLen = Math.Max(s1.Length, s2.Length);
    return maxLen == 0 ? 100 : (1 - (double)distance / maxLen) * 100;
}

2. 数据标准化预处理 #

先清洗数据，消除格式差异：

// 标准化名字：移除头衔、统一小写
public static string NormalizeName(string fullName, string title)
{
    string normalized = fullName.ToLower().Replace(title.ToLower(), "").Trim();
    return normalized;
}

// 标准化地址：移除区域标识、统一格式
public static string NormalizeLocation(string location)
{
    string normalized = location.ToLower().Trim();
    normalized = normalized.Replace(" ot ", " ").Replace(" district ", " ").Trim();
    return normalized;
}

3. 批量重复检测逻辑 #

按姓氏分组后再比对，减少无效计算：

// 假设Customer为你的实体类
public List<Tuple<Customer, Customer>> DetectDuplicates(List<Customer> customers)
{
    var duplicates = new List<Tuple<Customer, Customer>>();
    var grouped = customers.GroupBy(c => c.LastName.ToLower());

    foreach (var group in grouped)
    {
        var groupList = group.ToList();
        for (int i = 0; i < groupList.Count; i++)
        {
            for (int j = i + 1; j < groupList.Count; j++)
            {
                var c1 = groupList[i];
                var c2 = groupList[j];
                
                int score = 0;
                if (c1.LastName.Equals(c2.LastName, StringComparison.OrdinalIgnoreCase)) score += 30;
                if (CalculateSimilarity(c1.FirstName, c2.FirstName) >= 70) score += 20;
                if (c1.Street.Equals(c2.Street, StringComparison.OrdinalIgnoreCase)) score += 25;
                if (c1.Zip.Equals(c2.Zip)) score += 15;
                if (NormalizeLocation(c1.Location).Contains(NormalizeLocation(c2.Location)) || 
                    NormalizeLocation(c2.Location).Contains(NormalizeLocation(c1.Location))) score += 10;
                
                if (score >= 70)
                {
                    duplicates.Add(Tuple.Create(c1, c2));
                }
            }
        }
    }
    return duplicates;
}

三、混合方案（MySQL初筛 + C#精检） #

针对大数据量场景，先用MySQL筛选候选集，再用C#做精细化检测：

1. MySQL初筛查询：

SELECT * FROM customers
WHERE last_name IN (
    SELECT last_name FROM customers GROUP BY last_name HAVING COUNT(*) > 1
) AND zip IN (
    SELECT zip FROM customers GROUP BY zip HAVING COUNT(*) > 1
);

2. 将结果导入C#，用上述C#逻辑完成最终重复标记。

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