mysql update分割字符

-- 创建示例表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    hobbies VARCHAR(255)
);

CREATE TABLE user_hobbies (
    user_id INT,
    hobby VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入示例数据
INSERT INTO users (id, name, hobbies) VALUES
(1, 'Alice', 'reading,singing,dancing'),
(2, 'Bob', 'swimming,cooking');

-- 更新操作：将hobbies分割并插入到user_hobbies表中
DELIMITER $$
CREATE PROCEDURE SplitHobbies()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE userId INT;
    DECLARE hobby VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies, ',', numbers.n), ',', -1) AS hobby
                            FROM users JOIN (
                                SELECT a.N + b.N * 10 + c.N * 100 AS n
                                FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
                                CROSS JOIN (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
                                CROSS JOIN (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
                            ) numbers ON CHAR_LENGTH(hobbies) - CHAR_LENGTH(REPLACE(hobbies, ',', '')) >= numbers.n - 1
                            WHERE users.id = users.id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO userId, hobby;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO user_hobbies (user_id, hobby) VALUES (userId, hobby);
    END LOOP;

    CLOSE cur;
END$$
DELIMITER ;

-- 调用存储过程
CALL SplitHobbies();

INSERT INTO user_hobbies (user_id, hobby)
SELECT userId, hobby
FROM (
    SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies, ',', numbers.n), ',', -1) AS hobby
    FROM users JOIN (
        SELECT a.N + b.N * 10 + c.N * 100 AS n
        FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
        CROSS JOIN (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
        CROSS JOIN (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
    ) numbers ON CHAR_LENGTH(hobbies) - CHAR_LENGTH(REPLACE(hobbies, ',', '')) >= numbers.n - 1
    WHERE users.id = users.id
) AS temp
WHERE NOT EXISTS (
    SELECT 1 FROM user_hobbies WHERE user_hobbies.user_id = temp.id AND user_hobbies.hobby = temp.hobby
);