如何在数据库中实现带/不带国家码的电话号码统一搜索？

问题分析 #

你当前的SQL仅覆盖了搜索参数为无国家码本地号码时匹配存储的带国家码号码的场景，但无法处理反过来的情况：当搜索参数是带国家码的号码时，无法匹配存储的无国家码本地号码，因为缺少对搜索参数的格式转换逻辑。

解决方案 #

方案一：双向覆盖所有格式匹配

在WHERE条件中同时处理两种方向的格式转换，确保无论存储和搜索参数是哪种格式，都能互相匹配：

SELECT trainee.*, organization.organization AS orgName, course.course_name AS courseName
FROM trainee
LEFT JOIN organization ON trainee.organization = organization.organ_id
LEFT JOIN course ON trainee.course = course.id
WHERE firstName = ? OR lastName = ? OR trainee.email = ?
OR trainee.phone = ?  -- 匹配完全一致的同格式号码
OR trainee.phone = CONCAT('+251', SUBSTRING(?, 2))  -- 搜索参数是本地号码（0开头），匹配带国家码的存储值
OR trainee.phone = CONCAT('0', SUBSTRING(?, 4))  -- 搜索参数是带国家码（+251开头），匹配本地号码的存储值

注意：此处需要三次绑定同一个电话号码搜索参数。

方案二：统一转换为标准格式再匹配（更健壮）

将存储的电话号码和搜索参数都转换为统一的标准格式（比如无符号、无本地前缀的纯数字国际号码），再进行比较，彻底避免格式混乱问题：

SELECT trainee.*, organization.organization AS orgName, course.course_name AS courseName
FROM trainee
LEFT JOIN organization ON trainee.organization = organization.organ_id
LEFT JOIN course ON trainee.course = course.id
WHERE firstName = ? OR lastName = ? OR trainee.email = ?
OR (
    -- 转换存储号码为标准国际格式（无+、无本地0前缀）
    CASE
        WHEN trainee.phone LIKE '+251%' THEN SUBSTRING(trainee.phone, 2)
        WHEN trainee.phone LIKE '0%' THEN CONCAT('251', SUBSTRING(trainee.phone, 2))
        ELSE trainee.phone
    END
    =
    -- 转换搜索参数为标准国际格式
    CASE
        WHEN ? LIKE '+251%' THEN SUBSTRING(?, 2)
        WHEN ? LIKE '0%' THEN CONCAT('251', SUBSTRING(?, 2))
        ELSE ?
    END
)

说明：

• 标准格式定义为以国家码251开头的纯数字号码（如251914093949）
• 无论存储的是0914093949还是+251914093949，转换后都会统一为标准格式
• 搜索参数无论哪种格式，转换后也会和存储值的标准格式对齐，实现双向匹配

额外优化建议 #

如果数据库中电话号码格式混乱情况较多，建议在数据入库时就统一存储为标准格式，这样搜索时无需每次转换，性能和代码简洁性都会大幅提升。

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