使用SQL正则(RegEX)结合LIKE运算符校验邮箱的问题排查

你的语句全部返回无效结果，核心问题有4个：

• 运算符使用错误：标准SQL的LIKE仅支持%（匹配任意长度任意字符）、_（匹配单个任意字符）两个通配符，不支持正则表达式语法，直接把正则规则写在LIKE后无法匹配任何符合正则的内容。
• 正则规则和需求反向：你写的允许字符集[A-Za-z0-9._%\-+!#$&/=?^|~]，把要求禁止的_、-、+、&、=都纳入了允许范围，完全不符合过滤要求。
• 基础语法错误：SELECT EmailAddress后缺少逗号，语句本身存在语法问题。
• 规则覆盖不全：没有实现长度校验，也没有覆盖符号不相邻、符号不在首尾、单.必须在@后等边界规则。

注意：正则匹配不能用LIKE实现，需要使用对应数据库的正则匹配函数：MySQL/MariaDB用REGEXP、PostgreSQL用~运算符、Oracle用REGEXP_LIKE、SQL Server 2017+用REGEXP_MATCHES。长度判断直接用内置长度函数实现，可读性和兼容性远好于正则写长度限制。

以下代码完全覆盖你列出的所有校验规则，以MySQL语法为例：

SELECT 
  EmailAddress,
  CASE 
    WHEN 
      -- 校验总长度至少6个字符
      CHAR_LENGTH(EmailAddress) >= 6
      -- 正则匹配所有格式规则
      AND EmailAddress REGEXP '^[A-Za-z0-9]+(\\.[A-Za-z0-9]+)*@[A-Za-z0-9]+(\\.[A-Za-z0-9]+)+$'
    THEN 'Valid'
    ELSE 'INVALID' 
  END AS valid_email
FROM `Database`
WHERE EmailAddress IS NOT NULL;

正则规则对应说明 #

使用的正则^[A-Za-z0-9]+(\.[A-Za-z0-9]+)*@[A-Za-z0-9]+(\.[A-Za-z0-9]+)+$（不同数据库按需调整转义符）可以覆盖所有格式要求：

• 仅包含1个@：正则中仅存在1个@，前后均为非@字符，直接排除多@情况
• 至少包含1个.：@后强制要求至少1组.+字母数字的组合，保证全串至少1个.
• 单.必须在@后：@前的部分允许0个.，如果全串只有1个.则必然出现在@后；如果@前有.则全串.数量≥2，符合规则要求
• .和@不相邻：@前后的字符段都要求以字母数字开头/结尾，不会出现.@或@.的相邻组合
• 符号不在首尾：正则强制开头、结尾必须为字母/数字，直接排除./@在首尾的情况
• 禁止特殊字符：正则允许的字符仅包含大小写字母、数字、.、@，要求过滤的=、_、-、+、&、<、>、,均不在允许字符集内，会被直接判定为无效。

如果使用PostgreSQL，只需要把正则部分的双反斜杠改成单反斜杠即可，即'^[A-Za-z0-9]+(\.[A-Za-z0-9]+)*@[A-Za-z0-9]+(\.[A-Za-z0-9]+)+$'，其余逻辑完全一致。

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