插件窝 干货文章 MySQL数据转换:如何高效地实现行列互转和字符串拆分?

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

year 使用 转换 product 77    来源:    2025-03-16

在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;

结果:

product year_2020 year_2021
A 1000 2000
B 1500 2500

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;

结果:

product year amount
A 2020 1000
B 2020 1500
A 2021 2000
B 2021 2500

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;

结果:

id value
1 apple
1 banana
1 cherry
2 dog
2 cat

3. 使用存储过程或函数

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

4. 使用第三方工具

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

总结

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

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