MySQL长十六进制字符串转十进制出错问题排查

问题原因 #

• 你遇到的问题是CONV函数的64位整数限制导致的。MySQL的CONV()函数内部按64位整数处理数值，无符号64位整数的最大值为18446744073709551615，而你要转换的十六进制19d971e4fe8401e74000000对应的十进制500000000000000000000000000远大于这个上限，因此CONV会溢出并返回该最大值。

解决方法 #

由于MySQL原生没有支持超大整数的十六进制转十进制函数，可通过以下两种方式解决：

方式一：自定义逐位计算函数 #

创建一个基于字符串处理的自定义函数，逐位计算十六进制对应的十进制值：

DELIMITER //
CREATE FUNCTION hex_to_decimal(hex_str VARCHAR(255)) RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE decimal_str VARCHAR(255) DEFAULT '0';
    DECLARE hex_char CHAR(1);
    DECLARE hex_val INT;
    DECLARE i INT DEFAULT 1;
    DECLARE len INT;
    SET len = LENGTH(hex_str);
    
    WHILE i <= len DO
        SET hex_char = SUBSTRING(hex_str, i, 1);
        SET hex_val = CASE hex_char
            WHEN '0' THEN 0 WHEN '1' THEN 1 WHEN '2' THEN 2 WHEN '3' THEN 3 WHEN '4' THEN 4
            WHEN '5' THEN 5 WHEN '6' THEN 6 WHEN '7' THEN 7 WHEN '8' THEN 8 WHEN '9' THEN 9
            WHEN 'a' THEN 10 WHEN 'b' THEN 11 WHEN 'c' THEN 12 WHEN 'd' THEN 13 WHEN 'e' THEN 14 WHEN 'f' THEN 15
            WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15
            ELSE 0
        END;
        SET decimal_str = CONCAT(CAST(CAST(decimal_str AS DECIMAL(65,0)) * 16 + hex_val AS CHAR));
        SET i = i + 1;
    END WHILE;
    
    RETURN decimal_str;
END //
DELIMITER ;

调用示例：

SELECT hex_to_decimal(TRIM(LEADING '0' FROM SUBSTRING(input, 139, 64))) AS burning
FROM db;

注：函数使用DECIMAL(65,0)处理中间计算，支持最大65位十进制数，完全覆盖你需要的27位数值，若需更长数值可调整DECIMAL精度。

方式二：利用MySQL 8.0+的JSON大数处理能力 #

如果使用MySQL 8.0及以上版本，可借助JSON的超大整数支持特性：

SELECT JSON_UNQUOTE(JSON_EXTRACT(CONCAT('{"val": 0x', TRIM(LEADING '0' FROM SUBSTRING(input, 139, 64)), '}'), '$.val')) AS burning
FROM db;

该方法通过JSON解析十六进制值为超大整数，再提取出结果，无需自定义函数，更简洁高效。

内容的提问来源于stack exchange，提问作者jjj