如何在MariaDB的JSON列中将整数值转换为浮点型？

需求场景 #

需要将JSON字段中$.fonts_size的整数值（如1）转换为浮点型的1.0，确保应用能识别为双精度类型。

原JSON示例：

{
    "fonts_size": 1
}

目标JSON效果：

{
    "fonts_size": 1.0
}

问题分析 #

直接使用CAST(... AS FLOAT)的方案无法生效，拼接.0会导致值变为字符串类型，不符合需求。

解决方案 #

1. 批量更新JSON字段

如果需要永久修改数据库中的JSON数据，使用以下SQL：

UPDATE blocks
SET settings = JSON_REPLACE(
    settings,
    '$.fonts_size',
    CAST(JSON_EXTRACT(settings, '$.fonts_size') AS DECIMAL(10, 1)) + 0.0
)
WHERE JSON_TYPE(JSON_EXTRACT(settings, '$.fonts_size')) = 'INTEGER';

2. 查询时临时转换

如果仅需在查询结果中得到浮点型的JSON值，使用：

SELECT 
    JSON_REPLACE(
        settings,
        '$.fonts_size',
        CAST(JSON_EXTRACT(settings, '$.fonts_size') AS DECIMAL(10, 1)) + 0.0
    ) AS updated_settings
FROM blocks
WHERE JSON_TYPE(JSON_EXTRACT(settings, '$.fonts_size')) = 'INTEGER';

原理说明 #

• 通过CAST(... AS DECIMAL(10,1))将整数转为带一位小数的数值，再加上0.0强制MariaDB将其识别为浮点类型，这样写入JSON时会保留.0的格式。
• JSON_REPLACE专门用于替换JSON中已存在的属性值，比JSON_SET更贴合场景。
• JSON_TYPE过滤条件确保只处理值为整数的记录，避免对已为浮点型的数据重复操作。

内容的提问来源于stack exchange，提问作者Honza Bednář