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

我正在尝试将十六进制数据添加到十六进制字符串，并且需要使用其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)),'0')
        WHEN type IS 'float' THEN LPAD(HEX(CAST(value AS DECIMAL)),'0')
        ELSE 0
    END);

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

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

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

并使用以下方法

检查数字是正数还是负数。将正负号另存为0，将负号另存为1，如果负数则将其转换为正数。
将浮点数转换为二进制。
将小数部分和整数部分分开。
计算指数（E）并将其转换为二进制。
找到尾数。
合并尾数，指数和尾数的符号。将其转换为十六进制。

首先使用的功能。

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,16);
    SELECT hexstr;
END$$
DELIMITER ;

这将为您提供以下结果：

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

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

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) = '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')),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) = '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,LPAD(BIN(1023 + exponent),11,52,16);
END//

DELIMITER ;

在MySQL中无法完成。甚至不是全新的（8.0.17）：

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

此外，也没有其他方法可以解决，因为任何形式的CAST或HEX都只需要一个字符串，即“ 10.9”，而不是位/字节/十六进制的10.9。

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

XY064221 回答：将MySQL DECIMAL转换为浮点IEEE表示的十六进制

大家都在问