Convert a stored md5 string to a decimal value in MySQL

Question

I have a very large table in MySQL. I'm using a CHAR(32) field which contains an MD5 as a string of course. I'm running into an issue where I need to convert this to a decimal value using MySQL. A third party tool runs the query so writing code to do this isn't really an option.

MySQL does support storing hex values natively and converting them to integers. But it gets hung up converting it from a string. Here's what I've tried so far (md5_key is the name of my column)

First I just tried the UNHEX function but that returns a string so it gave me gooblygoop. I won't put that here. Next I tried the CAST function

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

Result = 0 Show warnings gives me: "Truncated incorrect INTEGER value: '0x000002dcc38af6f209e91518db3e79d3'"

BUT if I do:

SELECT CAST( 0x000002dcc38af6f209e91518db3e79d3 AS UNSIGNED );

I get the correct decimal value.

So I guess what I need to know, is there a way to get MySQL to see that string as a hex value? (I also tried converting it to BINARY and then to the UNSIGNED but that didn't work either).

Thanks in advance!

Answer 1

MySQL's CONV() function takes a string that contains a base-X number and converts it to base Y:

SELECT CONV('000002dcc38af6f209e91518db3e79d3', 16, 10);
# => 18446744073709551615

# So:
SELECT CONV(md5_key, 16, 10)  FROM bigtable LIMIT 1;

Answer 2

Beware MD5 are 16 Byte long, and BIGINT UNSIGNED is 8 Byte long, so even in your second case you don't get the right answer, the number can't fit you are receiving the value of the lowest 8 Byte=> 09e91518db3e79d3.

Answer 3

conv() is limited to 64 bit integers. You can convert the high and low part to decimal and then add them together:

> 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

Where 18446744073709551616 = 2^64. So in your case:

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