MariaDB GROUP_CONCAT截断float计算结果末尾字符问题排查

问题描述 #

使用MariaDB时，GROUP_CONCAT函数返回结果出现截断，且当前group_concat_max_len设置远大于结果长度。具体表现为：针对FLOAT类型字段value_mean计算后，用CONCAT拼接固定字符串'AFTER'显示正常，但嵌套进GROUP_CONCAT后，部分结果的末尾字符被截断（如最后两位'ER'丢失）。

复现测试案例 #

单条数据测试

MariaDB [testdb]> SELECT
    a.value_mean,
    b.value_mean,
    HEX(a.value_mean),
    HEX(b.value_mean),
    a.value_mean / b.value_mean,
    a.value_mean / b.value_mean * 100 - 100,
    CONCAT(a.value_mean / b.value_mean, 'AFTER'),
    CONCAT(a.value_mean / b.value_mean * 100 - 100, 'AFTER'),
    GROUP_CONCAT( CONCAT(a.value_mean / b.value_mean, 'AFTER') ),
    GROUP_CONCAT( CONCAT(a.value_mean / b.value_mean * 100 - 100, 'AFTER') )
FROM
    baseline_result AS a
    JOIN baseline_result AS b
WHERE
    a.id = 125716755
    AND b.id=125717382
\G
*************************** 1. row ***************************
                                                            value_mean: 15141600000
                                                            value_mean: 15141600000
                                                     HEX(a.value_mean): 38681F000
                                                     HEX(b.value_mean): 38681F400
                                           a.value_mean / b.value_mean: 0.9999999323715897
                               a.value_mean / b.value_mean * 100 - 100: -0.0000067628410249653825
                          CONCAT(a.value_mean / b.value_mean, 'AFTER'): 0.9999999323715897AFTER
              CONCAT(a.value_mean / b.value_mean * 100 - 100, 'AFTER'): -0.0000067628410249653825AFTER
            GROUP_CONCAT(CONCAT(a.value_mean / b.value_mean, 'AFTER')): 0.9999999323715897AFTER
GROUP_CONCAT(CONCAT(a.value_mean / b.value_mean * 100 - 100, 'AFTER')): -0.0000067628410249653825AFT
1 row in set (0.001 sec)

多条数据测试（截断出现在结果中间）

MariaDB [testdb]> SELECT
    a.value_mean,
    b.value_mean,
    HEX(a.value_mean),
    HEX(b.value_mean),
    a.value_mean / b.value_mean,
    a.value_mean / b.value_mean * 100 - 100,
    CONCAT(a.value_mean / b.value_mean, 'AFTER'),
    CONCAT(a.value_mean / b.value_mean * 100 - 100, 'AFTER'),
    GROUP_CONCAT(CONCAT(a.value_mean / b.value_mean, 'AFTER')),
    GROUP_CONCAT(CONCAT(a.value_mean / b.value_mean * 100 - 100, 'AFTER'))
FROM
    baseline_result AS a
    JOIN baseline_result AS b
WHERE
    a.id = 125716755
    AND b.id in (125717382, 125717383)
\G
*************************** 1. row ***************************
                                                            value_mean: 15141600000
                                                            value_mean: 15141600000
                                                     HEX(a.value_mean): 38681F000
                                                     HEX(b.value_mean): 38681F400
                                           a.value_mean / b.value_mean: 0.9999999323715897
                               a.value_mean / b.value_mean * 100 - 100: -0.0000067628410249653825
                          CONCAT(a.value_mean / b.value_mean, 'AFTER'): 0.9999999323715897AFTER
              CONCAT(a.value_mean / b.value_mean * 100 - 100, 'AFTER'): -0.0000067628410249653825AFTER
            GROUP_CONCAT(CONCAT(a.value_mean / b.value_mean, 'AFTER')): 0.9999999323715897AFTER,0.8918810968677036AFTER
GROUP_CONCAT(CONCAT(a.value_mean / b.value_mean * 100 - 100, 'AFTER')): -0.0000067628410249653825AFT,-10.81189031322964AFTER
1 row in set (0.001 sec)

环境版本信息

[root@test ~]# mysql --version
mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

MariaDB [testdb]> SELECT VERSION();
+----------------+
| VERSION()      |
+----------------+
| 5.5.56-MariaDB |
+----------------+
1 row in set (0.000 sec)

新版本MariaDB复现

MariaDB [(none)]> SELECT VERSION();
+---------------------------------------+
| VERSION()                             |
+---------------------------------------+
| 10.5.13-MariaDB-1:10.5.13+maria~focal |
+---------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> select GROUP_CONCAT(a) FROM ( SELECT CONCAT(conv('38681F000', 16, 10) / conv('38681F400', 16, 10) * 100 - 100, 'AFTER') as a) xxx;
+------------------------------+
| GROUP_CONCAT(a)              |
+------------------------------+
| -0.0000067628410249653825AFT |
+------------------------------+
1 row in set (0.000 sec)

补充：value_mean字段为FLOAT类型，直接查询显示数值相同，通过HEX()可区分底层存储差异。

MariaDB [testdb]> SELECT
id,
value_mean,
HEX(value_mean)
FROM baseline_result
WHERE id in (125716755,125717382,125717383);
+-----------+-------------+-----------------+
| id        | value_mean  | HEX(value_mean) |
+-----------+-------------+-----------------+
| 125716755 | 15141600000 | 38681F000       |
| 125717382 | 15141600000 | 38681F400       |
| 125717383 | 16977100000 | 3F3EA2800       |
+-----------+-------------+-----------------+
3 rows in set (0.000 sec)

根本原因 #

该问题的核心是FLOAT类型浮点数的精度特性导致字符串转换时产生隐藏的空字节：

1. FLOAT是单精度浮点数，存储的是近似值，两个看似相同的数值（如15141600000）底层二进制存储存在差异（通过HEX()可验证）。
2. 当对这类FLOAT数值进行算术运算后，转换为字符串的过程中，内部可能引入了不可见的空字节（\x00）。
3. CONCAT函数在客户端显示时会忽略空字节，或者处理逻辑不截断；但GROUP_CONCAT在拼接字符串时，会将空字节视为字符串结束标志，导致后续字符被截断（如'AFTER'变成'AFT'）。

解决方案 #

1. 显式将数值转换为无隐藏字符的字符串 #

使用CAST()或FORMAT()函数将算术运算结果显式转换为字符串，避免浮点数自动转换时引入空字节：

-- 使用CAST转换为CHAR类型
GROUP_CONCAT(CONCAT(CAST(a.value_mean / b.value_mean * 100 - 100 AS CHAR), 'AFTER'))

-- 或使用FORMAT指定精度，确保转换后的字符串完整
GROUP_CONCAT(CONCAT(FORMAT(a.value_mean / b.value_mean * 100 - 100, 18), 'AFTER'))

2. 替换FLOAT为DOUBLE类型存储 #

将value_mean字段类型从FLOAT改为DOUBLE（双精度浮点数），双精度的精度更高，算术运算后转换为字符串时更不容易产生异常的隐藏字符，从根源减少此类问题。

3. 使用DECIMAL类型转换后再拼接 #

先将FLOAT数值转换为DECIMAL类型，消除浮点数精度问题，再进行字符串拼接：

GROUP_CONCAT(CONCAT(CAST(a.value_mean AS DECIMAL(20,10)) / CAST(b.value_mean AS DECIMAL(20,10)) * 100 - 100, 'AFTER'))

4. 临时方案优化（替代ROUND） #

若需要保留原始精度，可使用TRIM()函数去除可能的隐藏字符，但显式转换是更可靠的方案：

GROUP_CONCAT(CONCAT(TRIM(a.value_mean / b.value_mean * 100 - 100), 'AFTER'))

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