mysql 逗号分隔转为列

陈静静字椰蓉

于 2024-07-14 03:54:43 发布

阅读量94

点赞数

文章标签： mysql 数据库

将MySQL中的逗号分隔数据转为列

在日常的数据库操作中，我们经常会遇到将逗号分隔的数据转为列的需求。这种情况在MySQL数据库中尤为常见。例如，我们有一个表格存储了用户的爱好，其中的数据是逗号分隔的。如果我们需要将这些爱好拆分成单独的列，该如何操作呢？接下来，我们将通过代码示例来演示如何实现这一需求。

流程图

代码示例

首先，我们需要创建一个示例的表格来模拟这种情况。假设我们有一个名为users的表格，其中有一个名为hobbies的列存储了用户的爱好：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    hobbies VARCHAR(100)
);

INSERT INTO users (id, name, hobbies) VALUES
(1, 'Alice', 'Reading,Swimming'),
(2, 'Bob', 'Hiking,Cooking,Painting'),
(3, 'Charlie', 'Singing,Dancing');

现在，我们需要将hobbies列中的逗号分隔数据拆分成单独的列。我们可以使用以下SQL语句来实现：

ALTER TABLE users ADD COLUMN hobby1 VARCHAR(50);
ALTER TABLE users ADD COLUMN hobby2 VARCHAR(50);
ALTER TABLE users ADD COLUMN hobby3 VARCHAR(50);

UPDATE users
SET hobby1 = SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies, ',', 1), ',', -1),
    hobby2 = CASE 
                WHEN LENGTH(hobbies) - LENGTH(REPLACE(hobbies, ',', '')) >= 1 
                THEN SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies, ',', 2), ',', -1) 
                ELSE NULL 
            END,
    hobby3 = CASE 
                WHEN LENGTH(hobbies) - LENGTH(REPLACE(hobbies, ',', '')) >= 2 
                THEN SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies, ',', 3), ',', -1) 
                ELSE NULL 
            END;