MySQL列转行|极客笔记

MySQL列转行

1. 引言

在处理关系型数据库中的数据时，经常会遇到需要将某列中的数据转化为行的需求。例如，在某个表中有一列记录了某个用户的多个爱好，每个爱好之间用逗号隔开。我们可能需要将这些爱好分别提取出来，存储到另一个表中的不同行中。

本文将详细介绍如何使用MySQL来实现列转行的操作。我们将会讨论两种常见的方法：使用MySQL内置的函数和使用MySQL的存储过程来实现列转行。

2. 列转行的方法

2.1 使用MySQL内置函数

MySQL提供了一些内置函数来处理字符串，我们可以利用这些函数来实现列转行的操作。

2.1.1 GROUP_CONCAT函数

GROUP_CONCAT函数是MySQL中非常有用的一个函数，它可以将一个列中的多个值拼接成一个字符串。我们可以通过设置GROUP_CONCAT函数的分隔符参数，将原先用逗号隔开的数据拆分成多个值。

首先，我们创建一个示例表来演示列转行的操作：

CREATE TABLE user (
    id INT,
    name VARCHAR(100),
    hobbies VARCHAR(200)
);

INSERT INTO user VALUES (1, 'John', 'reading,swimming,coding');
INSERT INTO user VALUES (2, 'Alice', 'singing,dancing,reading');
INSERT INTO user VALUES (3, 'Bob', 'swimming,coding');

现在，我们来使用GROUP_CONCAT函数将每个用户的hobbies列拆分成多个行：

SELECT 
    id,
    name,
    SUBSTRING_INDEX(hobbies, ',', 1) AS hobby_1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies,',',2),',',-1) AS hobby_2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies,',',3),',',-1) AS hobby_3
FROM user;

运行结果如下：

+------+-------+---------+---------+---------+
| id   | name  | hobby_1 | hobby_2 | hobby_3 |
+------+-------+---------+---------+---------+
|    1 | John  | reading | swimming| coding  |
|    2 | Alice | singing | dancing | reading |
|    3 | Bob   | swimming| coding  | NULL    |
+------+-------+---------+---------+---------+

在上述示例中，我们使用了SUBSTRING_INDEX函数来逐个获取每个hobby值。这种方法适用于我们已经知道hobbies列中的值的数量固定的情况。

2.1.2 FIND_IN_SET函数

FIND_IN_SET函数可以用于查找某个值在一个以逗号分隔的字符串中的位置。

现在我们来使用FIND_IN_SET函数和GROUP_CONCAT函数实现列转行的操作：

SELECT 
    id,
    name,
    SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies,',', FIND_IN_SET('reading', hobbies)), ',', -1) AS hobby_reading,
    SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies,',', FIND_IN_SET('swimming', hobbies)), ',', -1) AS hobby_swimming,
    SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies,',', FIND_IN_SET('coding', hobbies)), ',', -1) AS hobby_coding
FROM user;

运行结果如下：

+------+-------+---------------+----------------+-------------+
| id   | name  | hobby_reading | hobby_swimming | hobby_coding|
+------+-------+---------------+----------------+-------------+
|    1 | John  | reading       | swimming       | coding      |
|    2 | Alice | reading       | NULL           | NULL        |
|    3 | Bob   | NULL          | swimming       | coding      |
+------+-------+---------------+----------------+-------------+

在上述示例中，我们通过在FIND_IN_SET函数中查找目标值的位置，然后使用SUBSTRING_INDEX函数逐个提取每个爱好的值。这种方法比较灵活，我们可以根据需要提取任意数量的值。

2.2 使用MySQL存储过程

另一种实现列转行操作的方法是使用MySQL的存储过程。

我们可以通过创建一个存储过程来动态生成并执行SQL语句，从而实现列转行的操作。

下面是一个使用存储过程实现列转行的示例：

DELIMITER //

CREATE PROCEDURE column_to_row()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE total INT;
    DECLARE hobby VARCHAR(100);
    DECLARE sql_query VARCHAR(1000);

    -- 获取总行数
    SELECT COUNT(*) INTO total FROM user;

    -- 创建临时表
    CREATE TEMPORARY TABLE temp_table (
        id INT,
        name VARCHAR(100),
        hobby VARCHAR(100)
    );

    -- 循环处理每一行数据
    WHILE i < total DO
        SET i = i + 1;

        -- 获取当前行的hobbies列
        SELECT hobbies INTO hobby FROM user LIMIT i-1, 1;

        -- 将hobbies列拆分成多个行
        SET @sql_query = CONCAT('INSERT INTO temp_table SELECT id, name, "', REPLACE(hobby, ',', '"), SELECT id, name, "') , '" FROM user LIMIT ', i-1, ', 1');
        PREPARE stmt FROM @sql_query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END WHILE;

    -- 查询结果
    SELECT * FROM temp_table;

    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS temp_table;

END //

DELIMITER ;

运行存储过程：

CALL column_to_row();

运行结果如下：

+------+-------+---------+
| id   | name  | hobby   |
+------+-------+---------+
|    1 | John  | reading |
|    1 | John  | swimming|
|    1 | John  | coding  |
|    2 | Alice | singing |
|    2 | Alice | dancing |
|    2 | Alice | reading |
|    3 | Bob   | swimming|
|    3 | Bob   | coding  |
+------+-------+---------+

在上述示例中，我们首先创建了一个存储过程column_to_row，其中使用了循环和动态生成SQL语句的方式将每个hobby拆分为一行，并将结果存储在一个临时表中。最后，我们查询临时表的结果并删除该临时表。