MySQL数据转换：如何高效地实现行列互转和字符串拆分？

在MySQL中，数据转换操作包括行列互转（Pivot/Unpivot）和字符串拆分（String Splitting）。这些操作在处理复杂数据时非常有用。以下是实现这些操作的几种高效方法：

1. 行列互转（Pivot/Unpivot）

1.1 行转列（Pivot）

行转列是将多行数据转换为一行中的多列。假设有一个表 sales，结构如下：

CREATE TABLE sales (
    id INT,
    product VARCHAR(50),
    year INT,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (id, product, year, amount) VALUES
(1, 'A', 2020, 1000),
(2, 'B', 2020, 1500),
(3, 'A', 2021, 2000),
(4, 'B', 2021, 2500);

要将每年的销售额转换为列，可以使用 CASE 语句：

SELECT
    product,
    SUM(CASE WHEN year = 2020 THEN amount ELSE 0 END) AS year_2020,
    SUM(CASE WHEN year = 2021 THEN amount ELSE 0 END) AS year_2021
FROM sales
GROUP BY product;

结果：

1.2 列转行（Unpivot）

列转行是将多列数据转换为多行数据。假设有一个表 sales_pivot，结构如下：

CREATE TABLE sales_pivot (
    product VARCHAR(50),
    year_2020 DECIMAL(10, 2),
    year_2021 DECIMAL(10, 2)
);

INSERT INTO sales_pivot (product, year_2020, year_2021) VALUES
('A', 1000, 2000),
('B', 1500, 2500);

要将列转换为行，可以使用 UNION ALL：

SELECT product, 2020 AS year, year_2020 AS amount FROM sales_pivot
UNION ALL
SELECT product, 2021 AS year, year_2021 AS amount FROM sales_pivot;

结果：

2. 字符串拆分（String Splitting）

MySQL 8.0 及以上版本提供了 JSON_TABLE 函数，可以用于字符串拆分。假设有一个表 strings，结构如下：

CREATE TABLE strings (
    id INT,
    data VARCHAR(100)
);

INSERT INTO strings (id, data) VALUES
(1, 'apple,banana,cherry'),
(2, 'dog,cat');

要将逗号分隔的字符串拆分为多行，可以使用 JSON_TABLE：

SELECT
    id,
    value
FROM
    strings,
    JSON_TABLE(
        CONCAT('["', REPLACE(data, ',', '","'), '"]'),
        '$[*]' COLUMNS (value VARCHAR(50) PATH '$')
    ) AS jt;

结果：

3. 使用存储过程或函数

对于更复杂的转换需求，可以考虑使用存储过程或函数。例如，可以编写一个存储过程来实现动态的行列转换。

4. 使用第三方工具

对于非常复杂的转换需求，可以考虑使用第三方ETL工具（如Talend、Pentaho等）或编程语言（如Python的Pandas库）来处理数据，然后将结果导入MySQL。

总结

• 行列互转：使用 CASE 语句进行行转列，使用 UNION ALL 进行列转行。
• 字符串拆分：在MySQL 8.0及以上版本中，可以使用 JSON_TABLE 函数进行字符串拆分。
• 复杂转换：考虑使用存储过程、函数或第三方工具。

这些方法可以帮助你高效地实现MySQL中的数据转换操作。