如何通过哈希对比MySQL与SQL Server等不同数据库的TEXT字段内容

问题核心原因 #

哈希结果不匹配的根本原因是两个数据库传给哈希函数的字节序列不一致：

• MySQL的sha1()函数对TEXT类型输入，默认直接取字段存储的原始字节流（绝大多数场景是UTF-8编码）
• SQL Server中你把TEXT转成了nvarchar(max)，对应是UTF-16编码的字节序列，和MySQL的输入字节完全不同，哈希结果自然对不上

修复后的实现代码 #

统一规则：两边都把文本转成UTF-8编码的字节流再做SHA1哈希，结果即可匹配。

MySQL端 #

如果你的MySQL库/表/字段的默认字符集是utf8/utf8mb4，原有逻辑可直接使用，不确定编码的话可以用显式转码的写法兼容性更好：

SELECT SHA1(CONVERT(myData USING utf8mb4)) FROM myTable WHERE mypk=1;

SQL Server端 #

核心修改转码逻辑，不要转nvarchar，先转成UTF8编码的二进制再计算哈希：

-- 适用于SQL Server 2019及以上版本，原生支持UTF8编码
SELECT RIGHT(CONVERT(VARCHAR(45), HASHBYTES('SHA1', CAST(CAST(myData AS VARCHAR(MAX)) COLLATE Latin1_General_100_CI_AI_SC_UTF8 AS VARBINARY(MAX))), 1), 40) 
FROM myTable WHERE mypk=1;

2019以下版本可先通过自定义UTF8转码函数处理文本，再传入哈希函数即可。

其他数据库通用方案 #

核心规则不变：统一把文本转成UTF-8编码的字节序列，再用相同哈希算法计算，各数据库实现如下：

Oracle #

-- 适用于Oracle 12c及以上版本
SELECT LOWER(RAWTOHEX(STANDARD_HASH(UTL_I18N.STRING_TO_RAW(myData, 'AL32UTF8'), 'SHA1'))) FROM myTable WHERE mypk=1;

DB2 #

-- 适用于DB2 9.7及以上版本，HASH函数第二个参数2对应SHA1算法
SELECT LOWER(HEX(HASH(UTL_RAW.CAST_TO_RAW(CONVERT(myData, 'UTF8')), 2))) FROM myTable WHERE mypk=1;

注意事项 #

• 如果文本包含生僻字、emoji等4字节Unicode字符，必须选择支持4字节编码的字符集转码，避免字符丢失导致哈希不一致
• 大文本哈希计算性能随文本长度线性下降，大批量对比时建议分批次执行，避免占用过多数据库CPU资源

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