如何在PostgreSQL中对TEXT列生成SHA1哈希值

针对TEXT类型的MY_XML列生成40字符的SHA1哈希值（用于变更状态检查），正确的PostgreSQL语句如下：

ENCODE(DIGEST(MY_XML::bytea, 'sha1'), 'hex') AS HASH

如果需要严格确保结果截断为40字符（SHA1的十六进制结果本身就是40字符，此步骤可选），可以使用：

SUBSTRING(ENCODE(DIGEST(MY_XML::bytea, 'sha1'), 'hex') FROM 1 FOR 40) AS HASH

语句说明 #

1. MY_XML::bytea：将TEXT类型列转换为PostgreSQL的二进制类型bytea，这是DIGEST函数的必填输入格式。若MY_XML存储的是XML数据，也可写成MY_XML::text::bytea，确保先转为文本再转二进制。
2. DIGEST(..., 'sha1')：生成输入内容的SHA1二进制哈希值，返回bytea类型结果。
3. ENCODE(..., 'hex')：将二进制哈希转换为十六进制字符串，SHA1的十六进制结果固定为40字符，完全匹配需求。

你之前尝试语句的问题分析 #

• DIGEST(CAST(MY_XML as varchar(max)),'SHA1')::varchar(40)：DIGEST返回的bytea直接转varchar会生成乱码，而非预期的十六进制格式。
• CAST(DIGEST(CAST(MY_XML as varchar),'SHA1') as varchar)：同上，二进制哈希直接转换为字符串无法得到可读的十六进制结果。
• ENCODE(DIGEST(CAST(MY_XML as varchar(max)),'SHA1'),'hex')：PostgreSQL中没有varchar(max)类型，应使用text或无长度限制的varchar，且直接转bytea更简洁。
• ENCODE(DIGEST(MY_XML::text::bytea, 'sha1'), 'hex')：此写法接近正确，但MY_XML本身为TEXT类型时，MY_XML::bytea已足够，额外转text属于冗余操作，若出现问题大概率是数据库编码与列编码不匹配导致的。

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