在MySQL中将存储的md5字符串转换为十进制值 [英] Convert a stored md5 string to a decimal value in MySQL

问题描述

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

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确实支持本地存储十六进制值并将其转换为整数.但是它挂掉了从字符串转换它的麻烦.到目前为止，这是我尝试过的方法(md5_key是我的列的名称)

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)

首先，我刚刚尝试了UNHEX函数，但是返回了一个字符串，因此它给了我gooblygoop.我不会把它放在这里.接下来，我尝试了CAST功能

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

结果= 0 显示警告给我:被截断了不正确的INTEGER值:'0x000002dcc38af6f209e91518db3e79d3'"

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

但是，如果我这样做:

SELECT CAST( 0x000002dcc38af6f209e91518db3e79d3 AS UNSIGNED );

我得到正确的十进制值.

I get the correct decimal value.

所以我想我需要知道的是，有没有一种方法可以使MySQL将字符串视为十六进制值? (我也尝试将其转换为BINARY，然后转换为UNSIGNED，但这也不起作用).

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).

提前谢谢！

推荐答案

conv()限制为64位整数.您可以将高低部分转换为十进制，然后将它们加在一起:

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

其中18446744073709551616 = 2 ^ 64.因此，在您的情况下:

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;

这篇关于在MySQL中将存储的md5字符串转换为十进制值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！