如何用PL/pgSQL实现与MySQL一致的CRC16 CRC-CCITT(0xFFFF)函数

用户需要将以下MySQL环境中的CRC16函数转换为PL/pgSQL版本，原MySQL函数代码如下：

DELIMITER //
CREATE DEFINER=`root`@`%` FUNCTION `CRC16`( _STRING VARCHAR(25)) RETURNS varchar(50) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
    DECLARE _myCRC integer;
    DECLARE _ord INTEGER;
    DECLARE _n Integer;
    DECLARE _m Integer;
    DECLARE _strlend Integer;
    SET _myCRC := x'FFFF';

      SET _n  := 1;  
      SET _strlend := LENGTH(_STRING) ;
      
        loop_crc:  LOOP
     
            IF  _n > _strlend THEN 
                LEAVE  loop_crc;
            END  IF;
              
            SET _ord := ORD(SUBSTRING(_STRING, _n, 1) );
            SET _myCRC :=  _myCRC ^ _ord;
            SET _m := 0;     
            loop_bit:  LOOP
                IF  _m = 8 THEN 
                    LEAVE  loop_bit;
                END  IF;
                IF (_myCRC & x'0001') = x'0001' THEN
                    SET _myCRC := (_myCRC >> 1) ^ x'A001';
                ELSE
                    SET _myCRC := _myCRC >> 1;        
                END IF;
                SET  _m := _m + 1;
            END LOOP;
            SET  _n := _n + 1;

        END LOOP;

      return HEX(_myCRC); 
 END//
DELIMITER ;

PL/pgSQL实现版本 #

CREATE OR REPLACE FUNCTION crc16(_string VARCHAR(25)) 
RETURNS VARCHAR(50) 
LANGUAGE plpgsql
DETERMINISTIC
AS $$
DECLARE
    _myCRC INTEGER := 0xFFFF;
    _ord INTEGER;
    _n INTEGER := 1;
    _m INTEGER;
    _strlend INTEGER;
BEGIN
    _strlend := OCTET_LENGTH(_string); -- 与MySQL LENGTH()逻辑一致，返回字符串字节数
    
    LOOP
        EXIT WHEN _n > _strlend;
        
        _ord := ASCII(SUBSTRING(_string FROM _n FOR 1)); -- 替代MySQL的ORD()函数
        _myCRC := _myCRC ^ _ord;
        
        _m := 0;
        LOOP
            EXIT WHEN _m = 8;
            
            IF (_myCRC & 0x0001) = 0x0001 THEN
                _myCRC := (_myCRC >> 1) ^ 0xA001;
            ELSE
                _myCRC := _myCRC >> 1;
            END IF;
            
            _m := _m + 1;
        END LOOP;
        
        _n := _n + 1;
    END LOOP;
    
    RETURN TO_HEX(_myCRC); -- 替代MySQL的HEX()函数
END;
$$;

关键转换说明 #

• 字符长度计算：MySQL的LENGTH()返回字符串字节数，PostgreSQL默认LENGTH()返回字符数，因此改用OCTET_LENGTH()保证逻辑一致，避免多字节字符场景下的偏差。
• ASCII值获取：MySQL的ORD()与PostgreSQL的ASCII()行为一致，均返回单个字符的ASCII码值，直接替换即可。
• 十六进制与位操作：PostgreSQL支持直接用0x前缀表示十六进制整数，异或^、与&、右移>>等位操作符的逻辑与MySQL完全匹配。
• 十六进制输出：MySQL的HEX()对应PostgreSQL的TO_HEX()函数，用于将整数转换为十六进制字符串。
• 循环语法：PL/pgSQL用LOOP + EXIT WHEN替代MySQL的LOOP...LEAVE，逻辑等价且写法更简洁。

一致性验证 #

可通过相同输入测试两个函数的输出是否一致，例如：

• MySQL执行：SELECT CRC16('test');
• PostgreSQL执行：SELECT crc16('test');

两者应返回完全相同的十六进制结果。

内容的提问来源于stack exchange，提问作者Hoh Panat Kiatrungwiraikul