如何简化MySQL中从逗号分隔字符串提取前8位日期的查询？

需求：bm_appointment表的app_recurrenceexception字段存储格式为"20240402T073000Z,20240405T073000Z..."的字符串，需提取每个日期元素的前8位（日期部分），最终以逗号分隔输出，预期结果如"20240402,20240405..."。以下是几种更简洁的实现方式：

方案1：MySQL 8.0+ 版本（推荐） #

利用正则表达式替换直接去除每个日期元素中T及之后的冗余部分，一步完成转换：

SELECT
    app_recurrenceexception,
    REGEXP_REPLACE(app_recurrenceexception, 'T[^,]+', '') AS modified_date
FROM
    `bm_appointment`
WHERE
    app_eventid = 15;

原理说明 #

正则表达式'T[^,]+'会匹配每个日期元素中从T开始到下一个逗号（或字符串末尾）的所有字符，将其替换为空字符串后，自动保留原逗号分隔的前8位日期部分。

方案2：MySQL 8.0+ 拆分聚合式（更灵活） #

如果需要对单个日期元素做额外处理，可先通过JSON_TABLE拆分字符串，再聚合结果：

SELECT
    t.app_recurrenceexception,
    GROUP_CONCAT(LEFT(j.date_str, 8) SEPARATOR ',') AS modified_date
FROM
    `bm_appointment` t,
    JSON_TABLE(
        CONCAT('["', REPLACE(t.app_recurrenceexception, ',', '","'), '"]'),
        '$[*]' COLUMNS(date_str VARCHAR(20) PATH '$')
    ) j
WHERE
    t.app_eventid = 15
GROUP BY
    t.app_recurrenceexception;

方案3：兼容MySQL 5.x 版本 #

若仍使用MySQL 5.x（无正则替换/JSON函数），可简化原查询中的数字生成逻辑，减少子查询复杂度：

SELECT
    app_recurrenceexception,
    GROUP_CONCAT(LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(app_recurrenceexception, ',', n), ',', -1), 8) SEPARATOR ',') AS modified_date
FROM
    `bm_appointment`,
    (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 
     UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) numbers
WHERE
    app_eventid = 15
    AND numbers.n <= 1 + (LENGTH(app_recurrenceexception) - LENGTH(REPLACE(app_recurrenceexception, ',', '')))
GROUP BY
    app_recurrenceexception;

说明 #

数字表仅生成1-10的序列，若业务中日期元素数量超过10，可继续添加UNION ALL SELECT 11等扩展序列长度。

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