在MySQL中将存储的md5字符串转换为十进制值

Question

我在MySQL中有一张非常大的表，我使用了一个CHAR（32）字段，其中包含MD5作为字符串。 我遇到了一个问题，需要使用MySQL将其转换为十进制值。 由于第三方工具运行查询，因此编写代码并不是一个真正的选择。

MySQL确实支持本地存储十六进制值并将它们转换为整数。 但是，它会卡在从字符串转换的过程中。 这是我迄今为止尝试过的内容（md5_key是我的列名）。

首先，我只是尝试了UNHEX函数，但它返回一个字符串，所以它给了我一些乱码。 我不会把它放在这里。 接下来，我尝试了CAST函数。

SELECT CAST( CONCAT('0x',md5_key) AS UNSIGNED ) FROM bigtable limit 1

结果 = 0 显示警告信息为："截断的不正确的整数值：'0x000002dcc38af6f209e91518db3e79d3'"

但是如果我这样做：

SELECT CAST( 0x000002dcc38af6f209e91518db3e79d3 AS UNSIGNED );

我得到了正确的十进制值。

所以我想知道，有没有一种方法让MySQL将该字符串视为十六进制值？（我还尝试将其转换为二进制，然后转换为无符号数，但那也不起作用）。

提前感谢！

- Cfreak

Answer 1

conv()函数仅适用于64位整数。您可以将高位和低位转换为十进制，然后将它们加在一起：

> select cast(conv(substr("000002dcc38af6f209e91518db3e79d3", 1, 16), 16, 10) as
              decimal(65))*18446744073709551616 +
         cast(conv(substr("000002dcc38af6f209e91518db3e79d3", 17, 16), 16, 10) as
              decimal(65));
58055532535286745202684464101843

18446744073709551616等于2的64次方。所以在你的情况下：

> select cast(conv(substr(md5_key, 1, 16), 16, 10) as 
              decimal(65))*18446744073709551616 +
         cast(conv(substr(md5_key, 17, 16), 16, 10) as
              decimal(65))
         from bigtable limit 1;

- serbaut

Answer 2

注意MD5长度为16字节，而BIGINT UNSIGNED长度为8字节，因此即使在第二种情况下，您也无法得到正确的答案，该数字无法适应，您正在接收最低8字节的值=> 09e91518db3e79d3。

- Patrick

Answer 3

很遗憾，无法将MD5转换为十进制，因为它会生成一个大于2^64-1的数字，但可以使用CRC32基于MD5创建哈希值作为数字。

示例：

SELECT concat(conv(left(md5field, 8), 16, 10), CRC32(md5field));

前面的示例计算了MD5的CRC32，同时将MD5的一部分作为数字连接起来。 前面的函数适用于MySQL无符号BigInt和BigQuery Int64（对于BigQuery，最好使用FARM_FINGERPRINT）。

如果我们将"FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF"作为MD5哈希值，可以生成的最大可能数是4,294,967,295,400,760,440。

- Juan Lago

---

谢谢你对一个旧问题的回答！我们早就用其他方法解决了这个问题（现在我甚至都不记得我们当时是怎么做的）。 - Cfreak

感谢您对这个旧问题的回答！我们早就以其他方式解决了这个问题（现在我甚至都不记得我们是怎么做的）。 - undefined

Answer 4

我编写了一个函数，用于将大的十六进制数字转换为十进制（65）。

CREATE FUNCTION `hexnum_to_decimal`(hex varchar(66)) RETURNS decimal(65,0)
    DETERMINISTIC
BEGIN
    declare group1 decimal(65);
    declare group2 decimal(65);
    declare group3 decimal(65);
    declare group4 decimal(65);
    declare multiplier decimal(65);
  
    if (substr(hex, 1, 2) = "0x") then
        set hex = substr(hex, 3); -- trim 0x if exists
    end if;
    
    set hex = trim(LEADING  '0' from hex);
    
    if (length(hex) > 54) then
        return null; -- too big number
    end if;
    
    set hex = lpad(hex, 64, 0);
    
    set group1 = cast(conv(substr(hex, 49, 16), 16, 10) as decimal(65));
    set group2 = cast(conv(substr(hex, 33, 16), 16, 10) as decimal(65));
    set group3 = cast(conv(substr(hex, 17, 16), 16, 10) as decimal(65));
    set group4 = cast(conv(substr(hex,  1, 16), 16, 10) as decimal(65));
    set multiplier = 18446744073709551616; -- 2 ^ 16
    
    -- check for overflow
    if (
        (group4 > 15930919) or
        (group4 = 15930919 and group3 > 2053574980671369030) or
        (group4 = 15930919 and group3 = 2053574980671369030 and group2 > 5636613303479645705) or
        (group4 = 15930919 and group3 = 2053574980671369030 and group2 = 5636613303479645705 and group1 > 18446744073709551615)
    ) then
        return null;
    end if;
    
    return cast(
        group1 +
        group2 * multiplier +
        group3 * multiplier * multiplier +
        group4 * multiplier * multiplier * multiplier
     as decimal(65));
END

针对你的情况，000002dcc38af6f209e91518db3e79d3

select hexnum_to_decimal("000002dcc38af6f209e91518db3e79d3");

58055532535286745202684464101843

select hexnum_to_decimal('F316271C7FC3908A8BEF464E3945EF7A253609FFFFFFFFFFFFFFFF');

99999999999999999999999999999999999999999999999999999999999999999

如果传递了更大的十六进制数，该函数将返回null。

- Vasil Nikolov