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

Question

我正在尝试将十六进制数据添加到十六进制字符串，并且需要使用其 IEEE 表示形式将 float 添加到该字符串。对于整数，这很简单:

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

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

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

并使用以下方法

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

• 将 float 转换为二进制。

• 将小数部分和整数部分分开。
• 计算指数(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,2,16);
    SELECT hexstr;
END$$
DELIMITER ;

这会给你以下结果:

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