将MySQL十进制转换为浮点IEEE表示的十六进制

Question

我正在尝试将十六进制数据添加到十六进制字符串中，我需要使用它们的IEEE表示将浮点数添加到该字符串中。对于整数，这很简单：

SET params = CONCAT(params,
    CASE
        WHEN type IS 'int' THEN LPAD(HEX(CAST(value AS SIGNED INTEGER)), 8, '0')
        WHEN type IS 'long' THEN LPAD(HEX(CAST(value AS SIGNED INTEGER)), 16, '0')
        WHEN type IS 'double' THEN LPAD(HEX(CAST(value AS DECIMAL)), 16, '0')
        WHEN type IS 'float' THEN LPAD(HEX(CAST(value AS DECIMAL)), 8, '0')
        ELSE 0
    END);

其中value是数字的VARCHAR，params是包含十六进制字符串的VARCHAR。这个技巧适用于整数，但对于十进制，它会截断十进制部分，并将整数部分转换为十六进制整数。如果十进制的大小是固定的（java float或double），如何将值转换为十进制数的IEEE浮点表示形式的十六进制？

Answer 1

它不能在MySQL中完成。即使是全新的（8.0.17）：

CAST(UNHEX('412E6666') AS FLOAT)

此外，也没有其他办法，因为任何形式的强制转换或十六进制都只需要一个字符串，即“10.9”，而不是10.9的位/字节/十六进制。

如果你能后退一步，也许可以通过其他方式实现更大的目标。

Answer 2

虽然@nbk的答案是正确的，但他的实现不适用于次正态数，并且缺少双精度浮点数的实现。这是他的答案的简化版本，支持单精度和双精度，并适用于次正态数。我个人不需要将逗号转换为小数点，因为我的数据库语言是英语，但在你的情况下，你可能必须这样做。

DELIMITER //

DROP FUNCTION IF EXISTS FLOAT_BIN//

CREATE FUNCTION FLOAT_BIN(number FLOAT, places INT)
RETURNS TEXT CHARSET utf8mb4 DETERMINISTIC
BEGIN
    DECLARE whole INT;
    DECLARE dec1 FLOAT;
    DECLARE res TEXT;

    SET whole = FLOOR(number);
    SET dec1 = number - whole;

    SET res = CONCAT(BIN(whole), '.');

    WHILE 0 < places DO
        SET dec1 = dec1 * 2;

        SET whole = FLOOR(dec1);
        SET dec1 = dec1 - whole;

        SET res = CONCAT(res, whole);
        SET places = places - 1;
    END WHILE;
    RETURN res;
END//

DROP FUNCTION IF EXISTS IEEE754;

CREATE FUNCTION IEEE754(n FLOAT) RETURNS CHAR(8)
BEGIN
    DECLARE sign INT;
    DECLARE whole VARCHAR(256);
    DECLARE dec1 VARCHAR(256);
    DECLARE exponent INT;
    DECLARE mantissa VARCHAR(256);

    # check if number is negative
    SET sign = 0;
    IF n < 0 THEN
        SET sign = 1;
        SET n = n * -1;
    END IF;

    # convert float to binary
    SET dec1 = FLOAT_BIN(n, 256); # good upper bound is twice the max exponent

    # separate the decimal part
    # and the whole number part
    SET whole = SUBSTRING_INDEX(dec1, '.', 1);
    SET dec1 = SUBSTRING_INDEX(dec1, '.', -1);

    # calculating the exponent(E)

    IF n >= 1 THEN
        SET exponent = LENGTH(whole) - 1;
        SET mantissa = CONCAT(SUBSTR(whole, 2), dec1);
    ELSE
        SET exponent = -1;
        WHILE SUBSTR(dec1, 1, 1) = '0' AND exponent > -127 DO
            SET exponent = exponent - 1;
            SET dec1 = SUBSTR(dec1, 2);
        END WHILE;

        IF exponent = -127 THEN
            SET mantissa = dec1;
        ELSE
            SET mantissa = SUBSTR(dec1, 2);
        END IF;
    END IF;

    RETURN CONV(CONCAT(sign, LPAD(BIN(127 + exponent), 8, '0'), RPAD(mantissa, 23, '0')), 2, 16);
END//

DROP FUNCTION IF EXISTS IEEE754_DOUBLE;

CREATE FUNCTION IEEE754_DOUBLE(n FLOAT) RETURNS CHAR(16)
BEGIN
    DECLARE sign INT;
    DECLARE whole VARCHAR(4096);
    DECLARE dec1 VARCHAR(4096);
    DECLARE exponent INT;
    DECLARE mantissa VARCHAR(4096);

    # check if number is negative
    SET sign = 0;
    IF n < 0 THEN
        SET sign = 1;
        SET n = n * -1;
    END IF;

    # convert float to binary
    SET dec1 = FLOAT_BIN(n, 4096);

    # separate the decimal part
    # and the whole number part
    SET whole = SUBSTRING_INDEX(dec1, '.', 1);
    SET dec1 = SUBSTRING_INDEX(dec1, '.', -1);

    # calculating the exponent(E)

    IF n >= 1 THEN
        SET exponent = LENGTH(whole) - 1;
        SET mantissa = CONCAT(SUBSTR(whole, 2), dec1);
    ELSE
        SET exponent = -1;
        WHILE SUBSTR(dec1, 1, 1) = '0' AND exponent > -1023 DO
                SET exponent = exponent - 1;
                SET dec1 = SUBSTR(dec1, 2);
            END WHILE;

        IF exponent = -1023 THEN
            SET mantissa = dec1;
        ELSE
            SET mantissa = SUBSTR(dec1, 2);
        END IF;
    END IF;

    RETURN CONV(CONCAT(sign, LPAD(BIN(1023 + exponent), 11, '0'), RPAD(mantissa, 52, '0')), 2, 16);
END//

DELIMITER ;

Answer 3

所以过了一段时间，我得到了一个解决方案，它由两个函数和一个存储过程组成（这也可以实现为函数，但我喜欢过程。

这基于python脚本，https://www.geeksforgeeks.org/python-program-to-represent-floating-number-as-hexadecimal-by-ieee-754-standard/

并使用以下方法

>

检查数字是正数还是负数。将符号另存为0表示正，1表示负，如果为负，则将数字转换为正。

将浮点数转换为二进制数。

首先是使用的功能。

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `decimal_converter`(num INTEGER) RETURNS decimal(10,10)
    DETERMINISTIC
BEGIN
   DECLARE outnum DECIMAL(10,10);

   SET outnum = num/10;
   label1: WHILE outnum > 1 DO
     SET outnum = outnum / 10;
   END WHILE label1;
RETURN outnum;
END$$
DELIMITER ;

而且还需要

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `float_bin`(number float
, places INT) RETURNS text CHARSET utf8mb4
    DETERMINISTIC
BEGIN
    DECLARE whole INT;
    DECLARE dec1  INT;
    DECLARE res TEXT;
    IF places = NULL THEN SET places = 3; END IF;
    SELECT
    SUBSTRING_INDEX(REPLACE(CAST(TRIM(TRAILING '.' FROM TRIM(TRAILING '0' from (number))) as CHAR(90)),',','.'), '.', 1) INTO @a;
    SELECT
    SUBSTRING_INDEX(REPLACE(CAST(TRIM(TRAILING '.' FROM TRIM(TRAILING '0' from (number))) as CHAR(90)),',','.'), '.', -1) iNTO @b;
    SET whole = @a;
    SET dec1 = @b ;
    SET res = BIN(whole);
    SET res = CONCAT(res , '.');
    while 0 < places do

        SELECT
          SUBSTRING_INDEX(REPLACE(CAST(TRIM(TRAILING '.' FROM TRIM(TRAILING '0' from (decimal_converter(dec1) * 2))) as CHAR(90)),',','.'), '.', 1) INTO @a;
       SELECT
          SUBSTRING_INDEX(REPLACE(CAST(TRIM(TRAILING '.' FROM TRIM(TRAILING '0' from (decimal_converter(dec1) * 2))) as CHAR(90)),',','.'), '.', -1) INTO @b;
        SET whole = @a;
        SET dec1 = @b;
        SET res = CONCAT(res , whole) ;
        SET places=places-1;
  end while;  
RETURN res;
END$$
DELIMITER ;

以及最终存储过程

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `IEEE754`(
IN n FLOAT
)
BEGIN
    DECLARE sign Integer;
    DECLARE whole TEXT;
    DECLARE dec1  TEXT;
    DECLARE p INT;
    DECLARE exponent  INT;
    DECLARE tmpstr  VARCHAR(60);
    DECLARE exponent_bits INT;
    DECLARE exponent_bitsstr  TEXT;
    DECLARE mantissa TEXT;
    DECLARE finally TEXT;
    DECLARE  hexstr TEXT;
    #check if number is negative
    SET sign = 0;
    IF n < 0 Then
        SET sign = 1;
        SET n = n  * -1;
    END IF;
    SET p = 30 ;
    # convert float to binary 
    SET dec1 = float_bin (n, p);
    # separate the decimal part 
    # and the whole number part 
    SELECT
      SUBSTRING_INDEX(REPLACE(CAST(dec1 as CHAR(90)),',','.'), '.', 1) INTO @a;
    SELECT
      SUBSTRING_INDEX(REPLACE(CAST(dec1 as CHAR(90)),',','.'), '.', -1) iNTO @b;
    SET whole = @a;
    SET dec1 = @b ;
    # calculating the exponent(E) 
    SET tmpstr = CAST(whole as CHAR(60));
    SET exponent = LENGTH(tmpstr) - 1;
    SET exponent_bits = 127 + exponent;
    SET exponent_bitsstr = BIN(exponent_bits);

    # finding the mantissa 
    SET  mantissa = SUBSTRING(tmpstr,2,exponent);
    SET  mantissa = CONCAT(mantissa,dec1);
    SET  mantissa = SUBSTRING(mantissa,1,23); 

    # the IEEE754 notation in binary 
    SET finally = CONCAT(sign,exponent_bitsstr,mantissa );
    SET hexstr = CONV(finally,2,16);
    SELECT hexstr;
END$$
DELIMITER ;

这将为您提供以下结果：

call IEEE754(263.3);
4383A666
call IEEE754(10.9);
412E6666