MySQL 怎样将一行转换为多行

在 MySQL 数据库的操作中，将一行数据转换为多行是一个常见需求。这一操作在数据处理、报表生成等场景下尤为重要。下面我们就来探讨一下如何在 MySQL 中实现这一功能。

使用 UNION ALL 实现

UNION ALL 是一种简单直接的方法。假设我们有一张包含用户信息的表 users，其中有一个字段 hobbies，存储了用户的多个爱好，以逗号分隔。比如某一行记录中 hobbies 的值为“足球,篮球,游泳”。现在要将每个爱好拆分成单独的行。

我们可以利用 SUBSTRING_INDEX 函数来提取每个爱好。SUBSTRING_INDEX 函数可以从字符串中按照指定的分隔符截取子字符串。

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies, ',', numbers.n), ',', -1) AS hobby
FROM users
JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) numbers
ON CHAR_LENGTH(hobbies) - CHAR_LENGTH(REPLACE(hobbies, ',', '')) >= numbers.n - 1;

在这个查询中，numbers 是一个临时表，提供了数字 1、2、3，代表可能的爱好数量。JOIN 操作将 users 表与 numbers 表连接起来，通过 CHAR_LENGTH 函数计算逗号的数量来确定拆分的次数，最终使用 SUBSTRING_INDEX 函数提取出每个爱好并展示为多行。

使用 JSON_TABLE 实现（MySQL 8.0+）

如果你的 MySQL 版本是 8.0 及以上，JSON_TABLE 函数提供了更强大灵活的方式。我们先将 hobbies 字段的值转换为 JSON 数组格式。

SELECT jt.hobby
FROM users
JOIN JSON_TABLE(CONCAT('["', REPLACE(hobbies, ',', '","'), '"]'), '$[*]' COLUMNS(hobby VARCHAR(255) PATH '$')) jt;

这里，CONCAT 函数将逗号分隔的爱好字符串转换为 JSON 数组形式。JSON_TABLE 函数则将这个 JSON 数组展开，每个元素作为一行返回，hobby 字段存储了拆分后的单个爱好。

通过上述方法，无论是较旧版本的 MySQL 还是支持新特性的版本，都能轻松实现将一行数据转换为多行，满足复杂的数据处理需求，提升数据库操作的效率和灵活性，为数据分析和应用开发提供有力支持 。