如何在MySQL中将单词拆分为单个字符的行？

原表（假设表名为words） #

期望结果 #

以下是不同数据库的实现方案：

MySQL（8.0+） #

使用递归CTE拆分每个字符：

WITH RECURSIVE split_letters AS (
    SELECT 
        id, 
        SUBSTRING(name, 1, 1) AS letter,
        SUBSTRING(name, 2) AS remaining
    FROM words
    WHERE name IS NOT NULL AND LENGTH(name) > 0
    UNION ALL
    SELECT 
        id, 
        SUBSTRING(remaining, 1, 1) AS letter,
        SUBSTRING(remaining, 2) AS remaining
    FROM split_letters
    WHERE remaining IS NOT NULL AND LENGTH(remaining) > 0
)
SELECT id, letter
FROM split_letters;

PostgreSQL #

利用generate_series生成字符位置，配合substring提取单个字母：

SELECT 
    id,
    SUBSTRING(name, n, 1) AS letter
FROM words
CROSS JOIN generate_series(1, LENGTH(name)) AS n;

SQL Server #

通过递归CTE逐位提取字符：

WITH split_letters AS (
    SELECT 
        id, 
        SUBSTRING(name, 1, 1) AS letter,
        2 AS pos
    FROM words
    WHERE name IS NOT NULL AND LEN(name) > 0
    UNION ALL
    SELECT 
        id, 
        SUBSTRING(name, pos, 1) AS letter,
        pos + 1
    FROM split_letters
    WHERE pos <= LEN(name)
)
SELECT id, letter
FROM split_letters;

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