在MySQL中,数据转换操作包括行列互转(Pivot/Unpivot)和字符串拆分(String Splitting)。这些操作在处理复杂数据时非常有用。以下是实现这些操作的几种高效方法:
行转列是将多行数据转换为一行中的多列。假设有一个表 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 |
列转行是将多列数据转换为多行数据。假设有一个表 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 |
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 |
对于更复杂的转换需求,可以考虑使用存储过程或函数。例如,可以编写一个存储过程来实现动态的行列转换。
对于非常复杂的转换需求,可以考虑使用第三方ETL工具(如Talend、Pentaho等)或编程语言(如Python的Pandas库)来处理数据,然后将结果导入MySQL。
CASE
语句进行行转列,使用 UNION ALL
进行列转行。JSON_TABLE
函数进行字符串拆分。这些方法可以帮助你高效地实现MySQL中的数据转换操作。