Mysql 逗号分隔列转行

在MySQL中，如果有一个列存储了逗号分隔的数据（如 a,b,c），并希望将其拆分成多行，可以通过以下方法实现。由于 MySQL 本身没有直接支持的字符串拆分函数，我们需要通过特定的方式来完成，例如利用递归查询（适用于 MySQL 8.0+）或自定义函数。

方法 1：使用递归查询（适用于 MySQL 8.0+）

原始表结构：

CREATE TABLE web_tags (
    id INT,
    tags VARCHAR(255)
);

INSERT INTO web_tags (id, tags) VALUES
(1, 'a,b,c'),
(2, 'x,y,z');

目标表结构：

CREATE TABLE web_tags_data (
    id INT,
    tname VARCHAR(255)
);

查询及插入语句：

WITH RECURSIVE split_cte AS (
    -- 基础部分：提取每行第一个值
    SELECT
        id,
        SUBSTRING_INDEX(tags, ',', 1) AS tname,
        SUBSTRING(tags, INSTR(tags, ',') + 1) AS remaining_tags
    FROM web_tags
    WHERE tags LIKE '%,%'
    
    UNION ALL
    
    -- 递归部分：继续提取剩余的值
    SELECT
        id,
        SUBSTRING_INDEX(remaining_tags, ',', 1) AS tname,
        IF(remaining_tags LIKE '%,%', SUBSTRING(remaining_tags, INSTR(remaining_tags, ',') + 1), NULL) AS remaining_tags
    FROM split_cte
    WHERE remaining_tags IS NOT NULL
)
-- 插入结果到目标表
INSERT INTO web_tags_data (id, tname)
SELECT id, tname FROM split_cte;

-- 查询结果
SELECT * FROM web_tags_data;

方法 2：使用存储过程（适用于 MySQL 5.7 或更低版本）

原始表结构：

CREATE TABLE web_tags (
    id INT,
    tags VARCHAR(255)
);

INSERT INTO web_tags (id, tags) VALUES
(1, 'a,b,c'),
(2, 'x,y,z');

目标表结构：

CREATE TABLE web_tags_data (
    id INT,
    tname VARCHAR(255)
);

存储过程实现：

DELIMITER $$

CREATE PROCEDURE split_tags()
BEGIN
    DECLARE temp_id INT;
    DECLARE temp_tags TEXT;
    DECLARE temp_tname VARCHAR(255);
    DECLARE pos INT;

    -- 游标用于迭代 web_tags 表
    DECLARE cur CURSOR FOR SELECT id, tags FROM web_tags;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET temp_id = NULL;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO temp_id, temp_tags;

        IF temp_id IS NULL THEN
            LEAVE read_loop;
        END IF;

        -- 循环拆分 tags 列中的值
        WHILE temp_tags IS NOT NULL DO
            SET pos = INSTR(temp_tags, ',');
            IF pos > 0 THEN
                SET temp_tname = LEFT(temp_tags, pos - 1);
                SET temp_tags = SUBSTRING(temp_tags, pos + 1);
            ELSE
                SET temp_tname = temp_tags;
                SET temp_tags = NULL;
            END IF;

            -- 插入拆分后的值到 web_tags_data
            INSERT INTO web_tags_data (id, tname) VALUES (temp_id, temp_tname);
        END WHILE;
    END LOOP;

    CLOSE cur;
END$$

DELIMITER ;

执行存储过程：

CALL split_tags();

-- 查询结果
SELECT * FROM web_tags_data;

方法 3：手动分割和插入（适用于小数据集）

如果数据量较小且不需要动态拆分，可以直接编写 SQL 手动拆分和插入：

INSERT INTO web_tags_data (id, tname)
SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', n), ',', -1) AS tname
FROM web_tags
JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3
) numbers
WHERE n <= LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) + 1;

输出结果

总结

    （1）、推荐使用递归查询（方法 1）：高效且易于理解，但需要 MySQL 8.0+。

    （2）、使用存储过程（方法 2）：适用于 MySQL 5.7 或以下版本。

    （3）、手动分割（方法 3）：适合少量静态数据。

以上就是云梦编程网为大家介绍的关于mysql列转行方法的全部内容了，可以根据自己的MySQL版本和数据规模选择合适的方法。