mysql逗号列转行

原创

mob64ca12ebf2cc 2023-08-30 05:44:15 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12ebf2cc的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL逗号列转行

在MySQL数据库中，有时候我们会遇到一些特殊的需求，需要将一列数据转换为行数据，其中每个元素由逗号分隔。这种情况下，我们可以使用MySQL的字符串函数来实现逗号列转行的功能。

逗号列转行的需求

假设我们有一张名为users的表，其中有一列名为skills，存储了每个用户的技能，用逗号分隔。例如：

id	name	skills
1	John	PHP,MySQL,JavaScript
2	Mary	Java,Python
3	Tom	HTML,CSS

我们希望将这些技能按行展示，结果如下：

id	name	skill
1	John	PHP
1	John	MySQL
1	John	JavaScript
2	Mary	Java
2	Mary	Python
3	Tom	HTML
3	Tom	CSS

利用MySQL字符串函数实现转换

为了实现逗号列转行的功能，我们可以使用MySQL的字符串函数SUBSTRING_INDEX和LENGTH。

首先，我们需要确定技能列中逗号的个数，从而确定需要生成的行数。我们可以使用以下SQL语句来计算逗号的个数：

SELECT 
    id,
    name,
    LENGTH(skills) - LENGTH(REPLACE(skills, ',', '')) + 1 AS skill_count
FROM
    users;

接下来，我们可以使用循环和SUBSTRING_INDEX函数来将技能列转换为行数据。我们可以使用以下SQL语句来实现转换：

SET @max_id = (SELECT MAX(id) FROM users);

SET @i = 1;

WHILE @i <= @max_id DO
    SET @skills = (SELECT skills FROM users WHERE id = @i);
    SET @skill_count = LENGTH(@skills) - LENGTH(REPLACE(@skills, ',', '')) + 1;
    
    SET @j = 1;
    WHILE @j <= @skill_count DO
        INSERT INTO converted_users (id, name, skill)
        SELECT id, name, SUBSTRING_INDEX(SUBSTRING_INDEX(skills, ',', @j), ',', -1)
        FROM users
        WHERE id = @i;
        
        SET @j = @j + 1;
    END WHILE;
    
    SET @i = @i + 1;
END WHILE;

在上面的代码中，我们使用converted_users表来存储转换后的数据。需要注意的是，这里假设我们已经创建了converted_users表，并且与users表具有相同的结构。