MySQL数据库中如何实现行列互转及字符串拆分？

在MySQL数据库中，实现行列互转和字符串拆分是常见的需求。以下是实现这些操作的几种方法：

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', 2021, 100),
(2, 'B', 2021, 150),
(3, 'A', 2022, 200),
(4, 'B', 2022, 250);

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

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

结果将是：

1.2 列转行（Unpivot）

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

CREATE TABLE sales_pivot (
    product VARCHAR(50),
    `2021` DECIMAL(10, 2),
    `2022` DECIMAL(10, 2)
);

假设数据如下：

INSERT INTO sales_pivot (product, `2021`, `2022`) VALUES
('A', 100, 200),
('B', 150, 250);

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

SELECT product, '2021' AS year, `2021` AS amount FROM sales_pivot
UNION ALL
SELECT product, '2022' AS year, `2022` AS amount FROM sales_pivot
ORDER BY product, year;

结果将是：

2. 字符串拆分

MySQL 本身没有直接的字符串拆分函数，但可以通过一些技巧实现。

2.1 使用 SUBSTRING_INDEX 函数

假设有一个字符串 'apple,banana,cherry'，我们想将其拆分为多行。

SET @str = 'apple,banana,cherry';
SET @delimiter = ',';

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(@str, @delimiter, n), @delimiter, -1) AS value
FROM 
    (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3) AS numbers
WHERE 
    n <= LENGTH(@str) - LENGTH(REPLACE(@str, @delimiter, '')) + 1;

结果将是：

2.2 使用存储过程

如果需要更复杂的字符串拆分，可以编写存储过程：

DELIMITER //

CREATE PROCEDURE SplitString(IN inputStr VARCHAR(255), IN delimiter CHAR(1))
BEGIN
    DECLARE pos INT DEFAULT 1;
    DECLARE token VARCHAR(255);
    DECLARE done INT DEFAULT 0;

    WHILE done = 0 DO
        SET pos = LOCATE(delimiter, inputStr);
        IF pos = 0 THEN
            SET token = inputStr;
            SET done = 1;
        ELSE
            SET token = SUBSTRING(inputStr, 1, pos - 1);
            SET inputStr = SUBSTRING(inputStr, pos + 1);
        END IF;
        SELECT token;
    END WHILE;
END //

DELIMITER ;

CALL SplitString('apple,banana,cherry', ',');

结果将是：

总结

• 行列互转：可以使用 CASE 语句实现行转列，使用 UNION ALL 实现列转行。
• 字符串拆分：可以使用 SUBSTRING_INDEX 函数或编写存储过程来实现字符串拆分。

这些方法可以帮助你在MySQL中实现行列互转和字符串拆分的需求。