mysql 分段批量update

-- 创建示例表
CREATE TABLE example_table (
    id INT PRIMARY KEY,
    value VARCHAR(255)
);

-- 插入示例数据
INSERT INTO example_table (id, value) VALUES
(1, 'old_value1'),
(2, 'old_value2'),
(3, 'old_value3'),
(4, 'old_value4'),
(5, 'old_value5');

-- 分段批量更新
SET @batch_size = 2;
SET @start_id = 1;

WHILE @start_id <= (SELECT MAX(id) FROM example_table) DO
    UPDATE example_table
    SET value = CONCAT('new_', value)
    WHERE id BETWEEN @start_id AND (@start_id + @batch_size - 1);
    
    SET @start_id = @start_id + @batch_size;
END WHILE;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET @batch_size = 5;