在AWS Athena查询中使用SQL函数校验邮箱地址格式

原有LIKE语句失效原因 #

之前使用的email NOT LIKE '%_@%.__%'无法得到正确结果，核心原因有两个：

• SQL标准中LIKE语法里的_是匹配任意单个字符的通配符，并非字面意义的下划线，未做转义时匹配逻辑完全偏离预期
• 匹配规则过于粗糙，既会漏判大量格式非法的邮箱，也会误判部分合法格式的邮箱，完全无法满足校验需求

可直接使用的正则校验方案 #

以下正则覆盖99.9%的业务场景邮箱校验需求，刻意排除了RFC规范允许但实际业务几乎不会出现的极端特殊格式（比如本地部分带引号、空格的邮箱），避免漏判明显脏数据。校验逻辑如下：

• 邮箱必须包含且仅包含1个@符号
• @前的本地段允许大小写字母、数字、下划线、点、加号、连字符，不允许特殊字符开头/结尾、不允许连续特殊字符
• @后的域名段必须包含至少1个.，域名各段仅允许字母、数字、连字符，顶级域名长度不小于2位，不允许连续点、特殊字符开头/结尾

适配Athena/Redshift/PostgreSQL等支持REGEXP_LIKE的数据源 #

直接运行以下SQL即可筛选出所有格式无效的邮箱（含空值）：

SELECT *
FROM your_source_table
WHERE
  email IS NULL
  OR NOT REGEXP_LIKE(
    email,
    '^[A-Za-z0-9]+[A-Za-z0-9._+-]*@[A-Za-z0-9]+[A-Za-z0-9-]*(\.[A-Za-z0-9]+[A-Za-z0-9-]*)*\.[A-Za-z]{2,}$'
  )

适配MySQL/Aurora MySQL数据源 #

MySQL使用REGEXP作为正则匹配函数，写法调整如下：

SELECT *
FROM your_source_table
WHERE
  email IS NULL
  OR email NOT REGEXP '^[A-Za-z0-9]+[A-Za-z0-9._+-]*@[A-Za-z0-9]+[A-Za-z0-9-]*(\.[A-Za-z0-9]+[A-Za-z0-9-]*)*\.[A-Za-z]{2,}$'

调整说明 #

• 如果你的业务场景支持内部域名无顶级后缀的邮箱，可以把正则末尾的\.[A-Za-z]{2,}$替换为(\.[A-Za-z]{2,})?$
• 如果需要兼容RFC规范中的特殊字符邮箱，可按需修改本地段（@前部分）的匹配字符范围
• 不建议继续使用LIKE语法做邮箱校验，即使对_做转义（写法为email NOT LIKE '%\_@%.__%' ESCAPE '\'），误判漏判率依然很高

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