如何在MySQL中查询相似姓名与邮箱 识别拼写错误类重复注册

SOUNDS LIKE 不生效的核心原因：该语法是基于英语单词发音规则做的语音匹配，仅对发音接近的英语单词有效，对中文姓名、含数字/特殊符号的邮箱场景适配性极差，1-2个字符的拼写偏差如果没有造成发音趋同，就无法返回匹配结果。

核心匹配逻辑 #

采用**编辑距离（Levenshtein距离）**作为相似判定规则：即两个字符串通过新增、删除、替换单个字符的操作能互相转换的最少操作数≤2，刚好覆盖1-2个字符笔误的场景。
匹配前必须加前置过滤规则减少无效计算，避免全表笛卡尔积导致查询超时：

• 两个待比对字符串的长度差不能超过2，直接排除不可能是1-2字符偏差的记录
• 叠加强关联字段做预匹配：比如手机号前10位一致、注册IP段一致、收货地址区县一致，大幅降低误判率
• 配对时加a.user_id < b.user_id条件，避免同一条配对重复出现两次（A配B、B配A）

不同数据库的具体实现 #

MySQL 实现 #

MySQL无内置编辑距离函数，先自定义函数再执行查询：

-- 创建编辑距离计算函数，MySQL 5.x/8.x 通用，仅需执行一次
DELIMITER $$
CREATE FUNCTION levenshtein(s1 VARCHAR(255), s2 VARCHAR(255)) 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
    DECLARE s1_char CHAR(1);
    DECLARE cv0, cv1 VARBINARY(256);
    SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
    IF s1 = s2 THEN RETURN 0;
    ELSEIF s1_len = 0 THEN RETURN s2_len;
    ELSEIF s2_len = 0 THEN RETURN s1_len;
    END IF;
    WHILE j <= s2_len DO SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1; END WHILE;
    WHILE i <= s1_len DO
        SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1;
        WHILE j <= s2_len DO
            SET c = c + 1;
            IF s1_char = SUBSTRING(s2, j, 1) THEN SET cost = 0; ELSE SET cost = 1; END IF;
            SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost;
            IF c > c_temp THEN SET c = c_temp; END IF;
            SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
            IF c > c_temp THEN SET c = c_temp; END IF;
            SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
        END WHILE;
        SET cv1 = cv0, i = i + 1;
    END WHILE;
    RETURN c;
END$$
DELIMITER ;

函数创建完成后执行相似用户查询：

SELECT 
    a.user_id AS user_id_1,
    a.name AS name_1,
    a.email AS email_1,
    a.phone AS phone_1,
    b.user_id AS user_id_2,
    b.name AS name_2,
    b.email AS email_2,
    b.phone AS phone_2
FROM user_table a
JOIN user_table b 
  ON a.user_id < b.user_id
  -- 长度差超过2的直接跳过，减少计算量
  AND ABS(CHAR_LENGTH(a.name) - CHAR_LENGTH(b.name)) <= 2
  AND ABS(CHAR_LENGTH(a.email) - CHAR_LENGTH(b.email)) <= 2
  -- 可选强校验规则，根据业务调整，可大幅降低误判
  AND LEFT(a.phone,10) = LEFT(b.phone,10)
WHERE 
    -- 姓名编辑距离≤2即判定为相似
    levenshtein(a.name, b.name) <= 2
    -- 邮箱仅比对@前的用户名部分，域名笔误概率极低
    OR levenshtein(SUBSTRING_INDEX(a.email,'@',1), SUBSTRING_INDEX(b.email,'@',1)) <= 2;

PostgreSQL 实现 #

PostgreSQL自带模糊匹配扩展，无需自定义函数：

-- 启用模糊字符串匹配扩展，仅需执行一次
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

-- 执行相似用户查询
SELECT 
    a.user_id AS user_id_1,
    a.name AS name_1,
    a.email AS email_1,
    a.phone AS phone_1,
    b.user_id AS user_id_2,
    b.name AS name_2,
    b.email AS email_2,
    b.phone AS phone_2
FROM user_table a
JOIN user_table b 
  ON a.user_id < b.user_id
  AND ABS(LENGTH(a.name) - LENGTH(b.name)) <= 2
  AND ABS(LENGTH(a.email) - LENGTH(b.email)) <= 2
  AND LEFT(a.phone,10) = LEFT(b.phone,10)
WHERE 
    levenshtein(a.name, b.name) <= 2
    OR levenshtein(SPLIT_PART(a.email,'@',1), SPLIT_PART(b.email,'@',1)) <= 2;

性能优化建议 #

• 单表用户量超过10万时，不要直接全表匹配，先按注册时间段、手机号号段、收货地址区县拆分成多个数据块，块内做两两匹配，计算量可降低90%以上
• 若业务存储了注册IP、设备ID、收货手机号等字段，可将这些字段作为前置匹配条件，进一步压缩比对范围、降低误判率
• 中文姓名场景可额外叠加拼音字段的编辑距离匹配，覆盖拼音输入时的选字错误、同音字错误场景

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