mysql update分割字符

基础概念

MySQL中的UPDATE语句用于修改表中的数据。当需要对某个字段的值进行分割并更新时，通常涉及到字符串处理函数。

类型

常见的字符串分割函数有：

SUBSTRING_INDEX(str, delim, count)：根据分隔符分割字符串。
SUBSTRING(str, pos, len)：根据位置和长度截取字符串。
CONCAT(str1, str2, ...)：连接多个字符串。

应用场景

假设我们有一个用户表users，其中有一个字段hobbies存储了用户的兴趣爱好，多个兴趣之间用逗号分隔。现在我们需要将这些兴趣分割成单独的记录存储到另一个表user_hobbies中。

示例代码

-- 创建示例表
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();

可能遇到的问题及解决方法

问题：分割后的数据重复

原因：可能是因为在分割过程中没有正确处理重复数据。

解决方法：在插入数据之前，先检查user_hobbies表中是否已经存在相同的数据。

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
);