如何在MariaDB中通过单条SQL对字符串所有子串执行运算

完全可以通过单条查询实现，不需要按逗号数量分多段筛选、分别写正则替换逻辑再拼接结果。核心思路是统一拆分所有逗号分隔值，逐值运算后按原顺序拼回字符串即可，适配所有1-5个数值的场景，不需要额外加条件判断。

实现方案 #

核心逻辑 #

• 按逗号作为分隔符，将字段内的多值字符串拆分为独立的单个数值
• 对拆分出的每个数值单独执行单位换算（乘以4046.86，可按需设置保留小数位数）
• 按原表的记录主键分组，将换算完成的数值按原顺序用逗号拼接，得到和原字段格式完全一致的结果

通用参考代码（支持MySQL 8.0+、PostgreSQL 12+、SQL Server 2017+、SQLite 3.30+等主流新版本数据库） #

以MySQL 8.0+为例，通过递归CTE实现任意长度逗号分隔值的处理：

WITH RECURSIVE split_cte AS (
    -- 锚点：提取每条记录的第一个数值，标记剩余待拆分的字符串
    SELECT 
        id, -- 替换为你表中的实际主键字段
        acres,
        CAST(SUBSTRING_INDEX(acres, ',', 1) AS DECIMAL(12,2)) AS single_acre,
        IF(LOCATE(',', acres) > 0, SUBSTRING(acres, LOCATE(',', acres) + 1), NULL) AS remain_str
    FROM your_table -- 替换为你的实际表名
    UNION ALL
    -- 递归：逐次拆分剩余字符串中的数值，直到无剩余内容
    SELECT 
        id,
        acres,
        CAST(SUBSTRING_INDEX(remain_str, ',', 1) AS DECIMAL(12,2)) AS single_acre,
        IF(LOCATE(',', remain_str) > 0, SUBSTRING(remain_str, LOCATE(',', remain_str) + 1), NULL) AS remain_str
    FROM split_cte
    WHERE remain_str IS NOT NULL
)
-- 按原记录分组，拼接换算后的结果
SELECT 
    id,
    acres AS original_acres,
    GROUP_CONCAT(
        ROUND(single_acre * 4046.86, 2) 
        ORDER BY 
            -- 保证拆分后的数值顺序和原字符串完全一致
            (LENGTH(acres) - LENGTH(REPLACE(acres, ',', ''))) - 
            (LENGTH(remain_str) - LENGTH(REPLACE(IFNULL(remain_str,''), ',', '')))
        SEPARATOR ','
    ) AS square_meter_result
FROM split_cte
GROUP BY id, acres;

不同环境适配提示 #

• PostgreSQL：直接用STRING_TO_ARRAY拆分字符串、UNNEST展开数组后聚合即可，不需要写递归CTE，代码更简洁
• SQL Server：使用STRING_SPLIT时必须开启enable_ordinal参数保证数值顺序和原字符串一致，最后用STRING_AGG完成拼接
• 老版本MySQL（5.x）不支持递归CTE：结合你的字段最多存5个数值的特点，可以直接用1-5的连续数字序列做关联拆分，逻辑更轻量：

-- MySQL 5.x 适配版本
SELECT
    id,
    acres,
    GROUP_CONCAT(
        ROUND(
            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(acres, ',', n.num), ',', -1) AS DECIMAL(12,2)) * 4046.86,
            2
        )
        SEPARATOR ','
    ) AS square_meter_result
FROM your_table
JOIN (
    SELECT 1 AS num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
) n 
ON n.num <= 1 + LENGTH(acres) - LENGTH(REPLACE(acres, ',', ''))
GROUP BY id, acres;

以上方案不需要提前判断字段内包含几个逗号分隔值，1个值到5个值的场景会自动适配，换算后拼接的结果和原字段的数值顺序、逗号分隔格式完全一致，比多层正则替换、分条件查询的方案执行效率更高，也不需要维护多段重复的查询逻辑。

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