引导者

## MySQL 一行转多行### 简介在 MySQL 中，有时我们需要将一行数据转换为多行数据。这种情况通常出现在需要将存储在单个列中的多个值拆分为单独行的时候。例如，将用逗号分隔的多个标签拆分为多行。### 常用方法#### 1. 使用 UNION ALL 操作符UNION ALL 可以将多个 SELECT 语句的结果集合并成一个结果集。我们可以利用这个特性，根据分隔符将一行数据拆分成多行。

示例：

假设我们有一个名为 `products` 的表，其中包含以下数据：| id | name | tags | |----|-------|-----------------------| | 1 | 产品A | 科技,电子,手机 | | 2 | 产品B | 生活,家居,厨房,电器 |现在我们需要将 `tags` 列中的标签拆分成多行。```sql SELECT id, name, SUBSTRING_INDEX(tags, ',', 1) AS tag FROM products UNION ALL SELECT id, name, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', 2), ',', -1) FROM products WHERE LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) > 0 UNION ALL SELECT id, name, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', 3), ',', -1) FROM products WHERE LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) > 1 UNION ALL SELECT id, name, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', 4), ',', -1) FROM products WHERE LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) > 2; ```

结果：

| id | name | tag | |----|-------|--------| | 1 | 产品A | 科技 | | 1 | 产品A | 电子 | | 1 | 产品A | 手机 | | 2 | 产品B | 生活 | | 2 | 产品B | 家居 | | 2 | 产品B | 厨房 | | 2 | 产品B | 电器 |

说明：

使用 `SUBSTRING_INDEX` 函数根据逗号分割字符串，并获取指定位置的子字符串。

使用 `LENGTH` 和 `REPLACE` 函数计算逗号出现的次数，从而确定需要 UNION ALL 的次数。

这个方法需要根据标签的最大数量手动添加 UNION ALL 语句，如果标签数量不固定，则不太方便。#### 2. 使用存储过程或函数我们可以创建存储过程或函数，使用循环和字符串处理函数来实现一行转多行。

示例 (存储过程)：

```sql DELIMITER // CREATE PROCEDURE split_tags(IN tag_string VARCHAR(255)) BEGINDECLARE delimiter_char VARCHAR(1) DEFAULT ',';DECLARE pos INT;DECLARE remaining_tags VARCHAR(255);SET remaining_tags = tag_string;WHILE LENGTH(remaining_tags) > 0 DOSET pos = LOCATE(delimiter_char, remaining_tags);IF pos > 0 THENINSERT INTO tags_table (tag) VALUES (TRIM(SUBSTRING(remaining_tags, 1, pos - 1)));SET remaining_tags = SUBSTRING(remaining_tags, pos + 1);ELSEINSERT INTO tags_table (tag) VALUES (TRIM(remaining_tags));SET remaining_tags = '';END IF;END WHILE; END // DELIMITER ; ```

使用方法：

```sql CALL split_tags('科技,电子,手机'); ```

说明：

该存储过程接受一个字符串作为参数，并将其按照逗号分隔符拆分成多个标签，插入到 `tags_table` 表中。

您可以根据需要修改存储过程，例如修改分隔符或目标表名。#### 3. 使用 JSON 函数 (MySQL 8.0+)MySQL 8.0 及更高版本支持 JSON 函数，我们可以利用 `JSON_TABLE` 函数将包含多个值的 JSON 数组转换为多行。

示例：

假设我们有一个名为 `users` 的表，其中包含以下数据：| id | name | hobbies | |----|-------|------------------------------| | 1 | 用户A | ["阅读", "音乐", "编程"] | | 2 | 用户B | ["旅行", "摄影"] |```sql SELECT u.id, u.name, j.hobby FROM users u,JSON_TABLE(u.hobbies, '$[

]' COLUMNS(hobby VARCHAR(255) PATH '$')) j; ```

结果：

| id | name | hobby | |----|-------|-------| | 1 | 用户A | 阅读 | | 1 | 用户A | 音乐 | | 1 | 用户A | 编程 | | 2 | 用户B | 旅行 | | 2 | 用户B | 摄影 |

说明：

`JSON_TABLE` 函数将 `hobbies` 列中的 JSON 数组解析为一个表。

`'$[

]'` 表示选择 JSON 数组中的所有元素。

`COLUMNS` 子句定义了输出表的列名和数据类型。

`PATH '$'` 表示从 JSON 对象的根路径开始解析。### 总结以上是几种常见的 MySQL 一行转多行的方法。选择哪种方法取决于您的具体需求，例如数据量、MySQL 版本、性能要求等。

如果数据量较小，可以使用 UNION ALL 操作符，简单易懂。

如果需要处理大量数据，建议使用存储过程或函数，提高效率。

如果您使用的是 MySQL 8.0 及更高版本，并且数据以 JSON 格式存储，则可以使用 JSON 函数，代码简洁高效。

MySQL 一行转多行

简介在 MySQL 中，有时我们需要将一行数据转换为多行数据。这种情况通常出现在需要将存储在单个列中的多个值拆分为单独行的时候。例如，将用逗号分隔的多个标签拆分为多行。

常用方法

1. 使用 UNION ALL 操作符UNION ALL 可以将多个 SELECT 语句的结果集合并成一个结果集。我们可以利用这个特性，根据分隔符将一行数据拆分成多行。**示例：**假设我们有一个名为 `products` 的表，其中包含以下数据：| id | name | tags | |----|-------|-----------------------| | 1 | 产品A | 科技,电子,手机 | | 2 | 产品B | 生活,家居,厨房,电器 |现在我们需要将 `tags` 列中的标签拆分成多行。```sql SELECT id, name, SUBSTRING_INDEX(tags, ',', 1) AS tag FROM products UNION ALL SELECT id, name, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', 2), ',', -1) FROM products WHERE LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) > 0 UNION ALL SELECT id, name, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', 3), ',', -1) FROM products WHERE LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) > 1 UNION ALL SELECT id, name, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', 4), ',', -1) FROM products WHERE LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) > 2; ```**结果：**| id | name | tag | |----|-------|--------| | 1 | 产品A | 科技 | | 1 | 产品A | 电子 | | 1 | 产品A | 手机 | | 2 | 产品B | 生活 | | 2 | 产品B | 家居 | | 2 | 产品B | 厨房 | | 2 | 产品B | 电器 |**说明：*** 使用 `SUBSTRING_INDEX` 函数根据逗号分割字符串，并获取指定位置的子字符串。 * 使用 `LENGTH` 和 `REPLACE` 函数计算逗号出现的次数，从而确定需要 UNION ALL 的次数。 * 这个方法需要根据标签的最大数量手动添加 UNION ALL 语句，如果标签数量不固定，则不太方便。

2. 使用存储过程或函数我们可以创建存储过程或函数，使用循环和字符串处理函数来实现一行转多行。**示例 (存储过程)：**```sql DELIMITER // CREATE PROCEDURE split_tags(IN tag_string VARCHAR(255)) BEGINDECLARE delimiter_char VARCHAR(1) DEFAULT ',';DECLARE pos INT;DECLARE remaining_tags VARCHAR(255);SET remaining_tags = tag_string;WHILE LENGTH(remaining_tags) > 0 DOSET pos = LOCATE(delimiter_char, remaining_tags);IF pos > 0 THENINSERT INTO tags_table (tag) VALUES (TRIM(SUBSTRING(remaining_tags, 1, pos - 1)));SET remaining_tags = SUBSTRING(remaining_tags, pos + 1);ELSEINSERT INTO tags_table (tag) VALUES (TRIM(remaining_tags));SET remaining_tags = '';END IF;END WHILE; END // DELIMITER ; ```**使用方法：**```sql CALL split_tags('科技,电子,手机'); ```**说明：*** 该存储过程接受一个字符串作为参数，并将其按照逗号分隔符拆分成多个标签，插入到 `tags_table` 表中。 * 您可以根据需要修改存储过程，例如修改分隔符或目标表名。

3. 使用 JSON 函数 (MySQL 8.0+)MySQL 8.0 及更高版本支持 JSON 函数，我们可以利用 `JSON_TABLE` 函数将包含多个值的 JSON 数组转换为多行。**示例：**假设我们有一个名为 `users` 的表，其中包含以下数据：| id | name | hobbies | |----|-------|------------------------------| | 1 | 用户A | ["阅读", "音乐", "编程"] | | 2 | 用户B | ["旅行", "摄影"] |```sql SELECT u.id, u.name, j.hobby FROM users u,JSON_TABLE(u.hobbies, '$[*]' COLUMNS(hobby VARCHAR(255) PATH '$')) j; ```**结果：**| id | name | hobby | |----|-------|-------| | 1 | 用户A | 阅读 | | 1 | 用户A | 音乐 | | 1 | 用户A | 编程 | | 2 | 用户B | 旅行 | | 2 | 用户B | 摄影 |**说明：*** `JSON_TABLE` 函数将 `hobbies` 列中的 JSON 数组解析为一个表。 * `'$[*]'` 表示选择 JSON 数组中的所有元素。 * `COLUMNS` 子句定义了输出表的列名和数据类型。 * `PATH '$'` 表示从 JSON 对象的根路径开始解析。

总结以上是几种常见的 MySQL 一行转多行的方法。选择哪种方法取决于您的具体需求，例如数据量、MySQL 版本、性能要求等。 * 如果数据量较小，可以使用 UNION ALL 操作符，简单易懂。 * 如果需要处理大量数据，建议使用存储过程或函数，提高效率。 * 如果您使用的是 MySQL 8.0 及更高版本，并且数据以 JSON 格式存储，则可以使用 JSON 函数，代码简洁高效。