MySQL十六进制到十进制的转换(大数字的问题)

kd3sttzy  于 2023-01-04  发布在  Mysql
关注(0)|答案(2)|浏览(315)

我的MySQL数据库中有一个输入列,以64个字符长的十六进制字符串结尾。
示例:00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
我使用下面的SUBSTRING(input,139,64)将其取出,然后使用TRIM(LEADING '0' FROM从字符串中修剪前导零,最后使用CONV(x,16,10)将其从十六进制值转换为十进制值。
最终的select语句如下所示:

SELECT CONV(TRIM(LEADING '0' FROM SUBSTRING(input, 139, 64)), 16, 10) AS burning
FROM db

当字符串如下所示时,一切正常:80转换为十进制值:128
或者当它看起来像这样:1b8转换为正确的十进制值:440
但是,当字符串如下所示时:* * 19d971e4fe8401e74000000CONV将其转换为18446744073709551615,这是一个错误的十六进制到十进制转换。正确的返回值应为:* * 500亿美元
我做错了什么?CONV中有什么限制破坏了这个函数吗?

rsaldnfx

rsaldnfx1#

来自MySQL文档:
CONV()使用64位精度
这意味着超过64位的值将被截断:

select (conv("19d971e4fe8401e74000000",16,10))\G
*************************** 1. row ***************************
(conv("19d971e4fe8401e74000000",16,10)): 18446744073709551615
1 row in set, 1 warning (0,001 sec)

show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DECIMAL value: '19d971e4fe8401e74000000'
1 row in set (0,001 sec)

select hex(18446744073709551615)\G
*************************** 1. row ***************************
hex(18446744073709551615): FFFFFFFFFFFFFFFF
1 row in set (0,001 sec)
57hvy0tb

57hvy0tb2#

SET @val = '19d971e4fe8401e74000000';
WITH RECURSIVE
cte AS (
  SELECT CONV(RIGHT(@val, 8), 16, 10) part,
         LEFT(@val, GREATEST(LENGTH(@val) - 8, 0)) rest,
         CAST(1 AS DECIMAL(40,0)) multiplier
  UNION ALL
  SELECT CONV(RIGHT(rest, 8), 16, 10),
         LEFT(rest, GREATEST(LENGTH(rest) - 8, 0)),
         CAST(multiplier AS DECIMAL(40,0)) * CAST(POW(2, 32) AS DECIMAL(40,0))
  FROM cte
  WHERE rest <> ''
)
SELECT SUM(CAST(CAST(part AS DECIMAL(40,0)) * CAST(multiplier AS DECIMAL(40,0)) AS DECIMAL(40,0))) result
FROM cte

| 结果|
| - ------|
| 500亿美元|
fiddle
当然,有些CAST可能是多余的。DECIMAL(40,0)可能会稍微扩展。

相关问题