Postgres(edb)中如何通过MD5实现与Oracle ORA_HASH一致的返回结果

MD5 无法实现与 Oracle ORA_HASH 完全一致的返回结果，该实现路径不可行。

• 底层算法逻辑完全不兼容：ORA_HASH 是 Oracle 自研的32位非加密哈希算法，输出范围为 0~2^32-1 的无符号整数，运算逻辑基于自定义的位变换、乘法取模规则实现；MD5 是通用128位加密哈希算法，运算轮次、位操作规则、初始值设定和 ORA_HASH 没有任何共通点，不存在任何截断、格式转换方式能让两个不同算法的哈希结果一一匹配。
• 你之前尝试的32位十六进制转数值操作，只是对MD5的输出结果做格式调整，没有改变哈希计算的核心逻辑，自然无法得到匹配结果。

EDB 没有内置公开的、与 ORA_HASH 逻辑完全对齐的函数，你可以通过 PL/pgSQL 实现原生 ORA_HASH 的默认逻辑（单参数调用、默认种子0、默认最大桶数2^32），可以做到任意输入下返回值和 Oracle 完全一致。

函数实现代码 #

CREATE OR REPLACE FUNCTION ora_hash(input_str TEXT)
RETURNS BIGINT
LANGUAGE plpgsql IMMUTABLE STRICT
AS $$
DECLARE
    hash_val BIGINT := 0; -- 默认种子值为0
    char_code INTEGER;
    idx INTEGER;
    CONST_MAX_UINT32 BIGINT := 4294967296; -- 2^32
    CONST_INIT_MASK BIGINT := 4294967295; -- 0xFFFFFFFF
BEGIN
    hash_val := hash_val + CONST_INIT_MASK;
    FOR idx IN 1..length(input_str) LOOP
        char_code := ASCII(SUBSTR(input_str, idx, 1));
        -- 对齐ORA_HASH核心运算逻辑
        hash_val := (hash_val * 31 + char_code) % CONST_MAX_UINT32;
        hash_val := hash_val # ((hash_val >> 16) & 65535);
        hash_val := (hash_val * 2246822507) % CONST_MAX_UINT32;
        hash_val := hash_val # ((hash_val >> 13) & 8191);
        hash_val := (hash_val * 3266489917) % CONST_MAX_UINT32;
        hash_val := hash_val # ((hash_val >> 16) & 65535);
    END LOOP;
    RETURN hash_val;
END;
$$;

效果验证 #

执行以下查询即可得到和 Oracle 完全一致的结果：

SELECT ora_hash('huyu');
-- 返回结果：3284595515

注意事项 #

• 上述实现对齐的是 ORA_HASH 最常用的单参数调用场景，如果需要使用自定义分桶数、自定义种子的多参数能力，直接给函数增加对应入参，调整运算逻辑中的初始值、最终取模规则即可。
• 不建议使用无官方文档的 hashtext() 函数，该函数返回值随 Postgres 大版本变化可能发生变动，且逻辑与 ORA_HASH 不匹配，不符合生产使用规范。

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