PostgreSQL志愿者ID校验CHECK约束失效排查及创建方法

需求明确 #

志愿者ID为11位字符串，结构规则如下：

• 格式：6位出生日期数字 + 1位分隔符 + 3位个性化数字 + 1位校验字符，示例：150600A905P
• 分隔符规则：
  • 1800年代出生：仅允许+
  • 1900年代出生：允许-、Y、X、W、V、U
  • 2000年代出生：允许A、B、C、D、E、F
• 校验字符规则：将出生日期与个性化字符串拼接成9位数字，除以31取余数（0-30），余数对应字符集[0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,H,J,K,L,M,N,P,R,S,T,U,V,W,X,Y]

常见逻辑问题排查点 #

1. 长度校验错误：
   • 误用length()替代char_length()：length()计算字节数，若ID含多字节字符会导致判断错误，需用char_length(p_id) = 11确保是11个字符。
2. 分隔符位置判断错误：
   • PostgreSQL字符串索引从1开始，第7位分隔符需用substring(p_id,7,1)提取，若写成substring(p_id,6,1)会取到错误位置的字符。
3. 分隔符字符集不完整：
   • 遗漏允许的字符（比如1900年代的Y、X），或对特殊字符处理不当（直接用IN判断则无需转义+这类符号）。
4. 校验字符计算逻辑错误：
   • 错误拼接分隔符到计算字符串中：需仅拼接出生日期和个性化字符串，不能包含分隔符。
   • 字符集索引对应错误：PostgreSQL数组索引从1开始，余数0对应字符集第1个元素，需用余数+1取数组值。
   • 大小写不匹配：未将输入的校验字符统一转为大写（或小写），导致与字符集中的大写字符比对失败。
   • 数字溢出：9位数字最大为999999999，用bigint类型存储避免溢出（int4虽能容纳，但bigint更稳妥）。

正确的校验函数与约束实现 #

1. 创建校验函数 #

CREATE OR REPLACE FUNCTION validate_volunteer_id(p_id text)
RETURNS boolean AS $$
DECLARE
    v_separator char(1);
    v_date_part text;
    v_custom_part text;
    v_check_char char(1);
    v_combined_num bigint;
    v_remainder integer;
    v_char_set text[] := ARRAY['0','1','2','3','4','5','6','7','8','9',
                               'A','B','C','D','E','F','H','J','K','L',
                               'M','N','P','R','S','T','U','V','W','X','Y'];
BEGIN
    -- 校验长度为11字符
    IF char_length(p_id) != 11 THEN
        RETURN false;
    END IF;

    -- 拆分ID各部分
    v_date_part := substring(p_id, 1, 6);
    v_separator := substring(p_id, 7, 1);
    v_custom_part := substring(p_id, 8, 3);
    v_check_char := upper(substring(p_id, 11, 1)); -- 统一转为大写

    -- 校验出生日期和个性化部分为纯数字
    IF v_date_part !~ '^[0-9]{6}$' OR v_custom_part !~ '^[0-9]{3}$' THEN
        RETURN false;
    END IF;

    -- 校验分隔符合法性
    IF v_separator NOT IN ('+', '-', 'A', 'B', 'C', 'D', 'E', 'F', 'X', 'Y', 'W', 'V', 'U') THEN
        RETURN false;
    END IF;

    -- 计算校验字符
    v_combined_num := (v_date_part || v_custom_part)::bigint;
    v_remainder := v_combined_num % 31;

    -- 比对校验字符
    IF v_check_char != v_char_set[v_remainder + 1] THEN
        RETURN false;
    END IF;

    RETURN true;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

2. 添加CHECK约束 #

ALTER TABLE volunteers
ADD CONSTRAINT chk_volunteer_id_valid
CHECK (validate_volunteer_id(volunteer_id));

验证要点 #

• 测试边界值：比如余数为0（对应0）、余数为30（对应Y）的ID
• 测试不同分隔符的合法性：比如1900年代的Y、2000年代的F
• 测试大小写混合的校验字符：比如输入p应被转为P通过校验

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