SQL中为列生成唯一随机哈希的方法及触发器空值报错排查

问题说明 #

我有一张名为foo的表，其中包含一个非空且唯一的hash列。希望为该列生成唯一的64位随机字符串，尝试过用默认值但无法避免极低概率的重复：

alter TABLE foo ADD column register_key text UNIQUE NOT NULL default md5(random()::text);

之后用PL/pgSQL编写触发器，但测试插入时收到错误：ERROR: null value in column "hash" violates not-null constraint，触发器函数和创建语句如下：

create or replace function generate_hash() returns trigger
as $$
declare temp_hash text;
        counter int;
begin
    while exists(select * from foo where hash = temp_hash) and counter < 10 loop
            select substr(sha512(random()::text), 0, 64) into temp_hash;
            counter := counter + 1;
    end loop;
    if counter >= 10 
       then RAISE EXCEPTION 'cannot generate hash';
       end if;
    new.hash = temp_hash;
    return new;
end;
$$ language plpgsql;

CREATE TRIGGER insert_hash
BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE generate_hash();

错误原因 #

你的触发器函数存在两个关键问题：

1. 变量未初始化：temp_hash和counter都没有初始值，初始时temp_hash为null，exists(select * from foo where hash = temp_hash)的结果永远为false（null与任何值比较都返回null，条件不成立），导致循环完全不执行，temp_hash始终是null，赋值给new.hash后触发非空约束错误。
2. 循环逻辑顺序颠倒：先判断哈希是否存在再生成哈希，第一次判断时temp_hash还未生成，逻辑完全错误。

修正后的触发器函数 #

create or replace function generate_hash() returns trigger
as $$
declare 
    temp_hash text;
    counter int := 0; -- 初始化计数器
begin
    loop
        -- 先生成哈希再检查存在性
        select substr(encode(sha512(random()::text), 'hex'), 1, 64) into temp_hash;
        counter := counter + 1;
        
        -- 满足条件则退出循环：哈希不存在，或尝试次数超限
        exit when not exists(select 1 from foo where hash = temp_hash) or counter >= 10;
    end loop;
    
    if counter >= 10 then
        RAISE EXCEPTION '无法生成唯一哈希值，已尝试10次';
    end if;
    
    new.hash = temp_hash;
    return new;
end;
$$ language plpgsql;

注意：sha512返回的是字节流，必须用encode(..., 'hex')转换为十六进制字符串，否则直接用substr处理会得到乱码。

更优实现方案 #

方案1：使用pgcrypto扩展生成安全随机字符串 #

pgcrypto提供的gen_random_bytes生成的随机字节更安全，转换为64位十六进制字符串的效率更高：
首先启用扩展：

CREATE EXTENSION IF NOT EXISTS pgcrypto;

修改后的触发器函数：

create or replace function generate_hash() returns trigger
as $$
declare 
    temp_hash text;
    counter int := 0;
begin
    loop
        temp_hash := encode(gen_random_bytes(32), 'hex'); -- 32字节对应64位十六进制字符
        counter := counter + 1;
        
        exit when not exists(select 1 from foo where hash = temp_hash) or counter >= 10;
    end loop;
    
    if counter >= 10 then
        RAISE EXCEPTION '无法生成唯一哈希值，已尝试10次';
    end if;
    
    new.hash = temp_hash;
    return new;
end;
$$ language plpgsql;

方案2：默认值+唯一约束重试 #

如果不想用触发器，可以通过存储过程处理插入，遇到唯一约束冲突时自动重试：

CREATE OR REPLACE FUNCTION insert_foo(/* 传入其他列的参数 */) RETURNS void AS $$
DECLARE
    attempts int := 0;
BEGIN
    WHILE attempts < 10 LOOP
        BEGIN
            INSERT INTO foo (/* 其他列名, hash */) 
            VALUES (/* 其他列值, encode(gen_random_bytes(32), 'hex') */);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            attempts := attempts + 1;
        END;
    END LOOP;
    RAISE EXCEPTION '无法插入记录，哈希重复尝试10次失败';
END;
$$ LANGUAGE plpgsql;

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