如何在MySQL查询中实现模糊分组？该功能是否可行？

我完全懂你的困扰——非英文数据用不了SOUNDEX()，SSIS又完全没接触过，想直接在MySQL里搞定模糊分组找重复记录，确实得找适配非英文场景的方案。下面给你几个实用的思路和具体实现：

1. 利用编辑距离（Levenshtein Distance）分组 #

编辑距离能衡量两个字符串的相似程度，数值越小代表越相似。MySQL本身没有内置这个函数，但你可以自定义一个：

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);
    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);
        SET cv1 = UNHEX(HEX(i));
        SET j = 1;
        WHILE j <= 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 c_temp = LEAST(
                CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + 1,
                c_temp
            );
            SET c_temp = LEAST(
                CONV(HEX(SUBSTRING(cv0, j+1, 1)), 16, 10) + 1,
                c_temp
            );
            SET cv1 = CONCAT(cv1, UNHEX(HEX(c_temp)));
            SET j = j + 1;
        END WHILE;
        SET cv0 = cv1;
    END FOR;
    
    RETURN CONV(HEX(SUBSTRING(cv0, s2_len+1, 1)), 16, 10);
END$$
DELIMITER ;

之后你可以用这个函数分组相似记录，比如把编辑距离≤2的归为一组（阈值可以根据你的数据灵活调整）：

SELECT 
    t1.id,
    t1.content,
    GROUP_CONCAT(t2.id SEPARATOR ',') AS similar_record_ids,
    GROUP_CONCAT(t2.content SEPARATOR '; ') AS similar_contents
FROM your_table t1
JOIN your_table t2 ON t1.id < t2.id 
    AND levenshtein(t1.content, t2.content) <= 2
GROUP BY t1.id, t1.content
HAVING COUNT(t2.id) > 0;

2. 基于n-gram分词的模糊匹配分组 #

对于中文、日文这类非英文数据，n-gram（比如二元分词）是更有效的相似性判断方式。你可以先把字符串拆分成n-gram集合，再计算交集比例：

先自定义一个生成n-gram的函数（以二元分词为例）：

DELIMITER $$
CREATE FUNCTION generate_ngrams(s VARCHAR(255), n INT)
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE result TEXT DEFAULT '';
    DECLARE len INT;
    DECLARE i INT DEFAULT 1;
    
    SET len = CHAR_LENGTH(s);
    IF len < n THEN RETURN s; END IF;
    
    WHILE i <= len - n + 1 DO
        SET result = CONCAT(result, SUBSTRING(s, i, n), ',');
        SET i = i + 1;
    END WHILE;
    RETURN TRIM(TRAILING ',' FROM result);
END$$
DELIMITER ;

然后用这个函数计算两个字符串的n-gram相似度，进而分组：

SELECT 
    t1.id,
    t1.content,
    GROUP_CONCAT(t2.id) AS similar_ids
FROM your_table t1
JOIN your_table t2 ON t1.id != t2.id
WHERE 
    (SELECT COUNT(*) FROM 
        (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(generate_ngrams(t1.content,2), ',', numbers.n), ',', -1) AS gram
         FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) numbers
         WHERE n <= LENGTH(generate_ngrams(t1.content,2)) - LENGTH(REPLACE(generate_ngrams(t1.content,2), ',', '')) + 1) g1
        JOIN 
        (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(generate_ngrams(t2.content,2), ',', numbers.n), ',', -1) AS gram
         FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) numbers
         WHERE n <= LENGTH(generate_ngrams(t2.content,2)) - LENGTH(REPLACE(generate_ngrams(t2.content,2), ',', '')) + 1) g2
        ON g1.gram = g2.gram) / 
    (SELECT COUNT(*) FROM 
        (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(generate_ngrams(t1.content,2), ',', numbers.n), ',', -1) AS gram
         FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) numbers
         WHERE n <= LENGTH(generate_ngrams(t1.content,2)) - LENGTH(REPLACE(generate_ngrams(t1.content,2), ',', '')) + 1) g1
        UNION
        (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(generate_ngrams(t2.content,2), ',', numbers.n), ',', -1) AS gram
         FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) numbers
         WHERE n <= LENGTH(generate_ngrams(t2.content,2)) - LENGTH(REPLACE(generate_ngrams(t2.content,2), ',', '')) + 1) g2) >= 0.6 -- 相似度阈值，可按需调整
GROUP BY t1.id, t1.content;

3. 简单前缀匹配分组（适合有重复前缀的场景） #

如果你的重复数据大多是前缀相似，比如"北京市朝阳区XX街道"和"北京市朝阳区YY街道"，可以直接截取前缀分组：

SELECT 
    SUBSTRING(content, 1, 8) AS prefix, -- 截取前8个字符作为分组依据，长度可调整
    GROUP_CONCAT(id) AS record_ids,
    GROUP_CONCAT(content) AS all_contents
FROM your_table
GROUP BY prefix
HAVING COUNT(id) > 1;

注意事项 #

• 性能优化：如果数据量很大，编辑距离和n-gram的计算会比较耗时，建议先过滤掉明显不相关的记录（比如长度差异超过一定范围的），或者给字段加合适的索引。
• 阈值调整：每个场景的相似性标准不同，你需要根据实际数据调整编辑距离阈值、n-gram相似度比例或者前缀长度。

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