PostgreSQL 符合RFC 5322规范的邮箱校验函数实现咨询

现有实现存在的问题 #

• 正则表达式语法错误：末尾错误使用^作为结束标记，正确的结束标记应为$，导致所有输入都会被判为不匹配。
• 返回值类型不匹配：REGEXP_MATCHES函数返回的是匹配结果的数组，而非布尔值，当无匹配时会返回空结果，直接作为布尔值返回会触发类型错误。
• 正则内容错误：误将网页HTML转义的"直接写入正则，实际需要匹配的双引号应直接写"，导致带引号的合法本地部分邮箱会被误判。
• 大小写校验规则错误：正则仅匹配小写字母a-z，未兼容大写字母输入，会把Test@Example.com这类合法邮箱误判为非法。
• 冗余的全局匹配标志：正则末尾的g（全局匹配）标志对全字符串匹配场景完全无用，反而可能导致多匹配结果的异常。
• 未处理输入前后空白：用户输入的邮箱常带有首尾空格，当前实现会直接将这类带空格的合法输入判为非法。
• 函数性能不佳：使用PL/pgSQL编写的非不可变函数，用作表约束或批量校验时性能远低于SQL原生不可变函数，也无法利用索引优化。

优化后的实现 #

CREATE OR REPLACE FUNCTION _is_email_valid(p_email TEXT)
RETURNS BOOLEAN AS $$
  SELECT trim(p_email) ~* '^(?:[a-z0-9!#$%&''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])$';
$$ LANGUAGE sql IMMUTABLE STRICT;

如果要求NULL输入时返回FALSE而非NULL，可移除STRICT关键字，并将trim(p_email)改为trim(COALESCE(p_email, ''))即可。

优化点说明 #

• 修正了正则结束符，将末尾的^改为$，实现完整的全字符串匹配。
• 替换REGEXP_MATCHES为PostgreSQL原生正则匹配运算符~*，直接返回布尔值，类型符合要求，性能更优。
• 将"替换为实际需要匹配的双引号"，修复带引号本地部分的邮箱匹配错误。
• 使用~*运算符开启大小写不敏感匹配，兼容大写字母输入的合法邮箱。
• 移除冗余的g全局匹配标志，避免不必要的性能开销和异常结果。
• 增加trim()处理输入首尾空白，兼容用户输入的常见格式问题。
• 改为LANGUAGE sql IMMUTABLE STRICT的纯SQL函数：IMMUTABLE标识函数输入固定则输出固定，可用于表达式索引优化，批量校验和表约束场景下性能比原PL/pgSQL实现提升数倍；STRICT标识输入为NULL时直接返回NULL，避免空输入的异常处理。

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