插件窝 干货文章 MySQL数据库中如何实现行列互转及字符串拆分?

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

拆分 product 字符串 delimiter 116    来源:    2025-03-16

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

结果将是:

product 2021 2022
A 100 200
B 150 250

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;

结果将是:

product year amount
A 2021 100
A 2022 200
B 2021 150
B 2022 250

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;

结果将是:

value
apple
banana
cherry

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', ',');

结果将是:

token
apple
banana
cherry

总结

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

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