代码之家 › 专栏 › 技术社区 › Cfreak

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

type-conversion md5 mysql

Cfreak · 技术社区 · 15 年前

我在MySQL中有一个非常大的表。我使用了一个CHAR(32)字段,其中包含一个MD5作为字符串。我遇到了一个问题,需要使用MySQL将其转换为十进制值。第三方工具运行查询,因此编写代码来执行查询实际上不是一个选项。

MySQL确实支持本机存储十六进制值并将其转换为整数。但是它从一个字符串转换它时会挂起。以下是我到目前为止所做的尝试(md5_key是我列的名称)

首先,我刚刚尝试了UNHEX函数,但它返回一个字符串,所以它给了我一个gooblygoop。我不会把它放在这里。接下来我尝试了CAST函数

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

结果=0 Show warnings告诉我:“截断的整数值不正确:'0x000002dcc38af6f209e91518db3e79d3'”

但如果我这样做:

SELECT CAST( 0x000002dcc38af6f209e91518db3e79d3 AS UNSIGNED );

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

所以我想我需要知道的是,有没有办法让MySQL将该字符串视为十六进制值(我还尝试将其转换为二进制,然后再转换为无符号,但也不起作用)。

提前谢谢!

2 回复 | 直到 15 年前

serbaut 15 年前

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

> 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;

Patrick 15 年前

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

Vasil Nikolov 4 年前

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

对于您的00000 2DC38AF6F209E91518DB3E79D3

select hexnum_to_decimal("000002dcc38af6f209e91518db3e79d3");

58055532535286745202684464101843

select hexnum_to_decimal('F316271C7FC3908A8BEF464E3945EF7A253609FFFFFFFFFFFFFFFF');

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