MySQL如何将年度响应字符串拆分并转换为年度行数据

我有一个名为answer的表，包含id、value列，其中value是由逗号分隔年度信息的字符串：

在上述示例中，id为1的记录里，值3对应2021年，2对应2020年，0对应2019年，最新年份始终位于字符串末尾。

如何将上述表转换为以下格式的表？

我尝试过其他语言中类似split()和explode()的实现示例，但未解决问题。

1. MySQL 8.0+ 版本 #

利用JSON_TABLE拆分字符串，结合位置倒推年份：

SELECT 
    a.id,
    2021 - (j.pos - 1) AS 年份,
    j.val AS value
FROM answer a
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(a.value, ',', '","'), '"]'),
    '$[*]' COLUMNS (
        pos FOR ORDINALITY,
        val INT PATH '$'
    )
) j
ORDER BY a.id, 年份 DESC;

说明：将逗号分隔字符串转为JSON数组，通过JSON_TABLE拆分出每个值的位置pos，最新年份对应最大的pos，因此用2021减去位置差得到对应年份。

2. PostgreSQL 版本 #

使用string_to_array拆分字符串，配合unnest带索引展开：

SELECT 
    a.id,
    2021 - (idx - 1) AS 年份,
    val::INT AS value
FROM answer a
JOIN unnest(string_to_array(a.value, ',')) WITH ORDINALITY AS t(val, idx) ON true
ORDER BY a.id, 年份 DESC;

说明：string_to_array将字符串转为数组，WITH ORDINALITY获取元素的索引位置，再按位置计算对应年份。

3. 低版本MySQL（无JSON_TABLE） #

借助数字辅助表拆分字符串：
假设存在数字表nums，包含n列（值为1、2、3...）：

SELECT 
    a.id,
    2021 - (n - 1) AS 年份,
    SUBSTRING_INDEX(SUBSTRING_INDEX(a.value, ',', n), ',', -1) AS value
FROM answer a
JOIN nums n 
    ON n.n <= LENGTH(a.value) - LENGTH(REPLACE(a.value, ',', '')) + 1
ORDER BY a.id, 年份 DESC;

说明：通过计算逗号数量得到元素总数，用SUBSTRING_INDEX逐层截取每个位置的元素，结合数字表的n值推导年份。

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