mysql 双重循环

DELIMITER //

CREATE PROCEDURE double_loop_example()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE max_id INT;

    -- 获取 table1 的最大 ID
    SELECT MAX(id) INTO max_id FROM table1;

    -- 外层循环遍历 table1 的所有行
    WHILE i <= max_id DO
        -- 内层循环处理每一行的数据
        INSERT INTO table2 (id, name) VALUES (i, (SELECT name FROM table1 WHERE id = i));

        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

CALL double_loop_example();

DELIMITER //

CREATE PROCEDURE double_loop_batch_insert()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE max_id INT;
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE names VARCHAR(255);
    DECLARE ids VARCHAR(255);

    -- 获取 table1 的最大 ID
    SELECT MAX(id) INTO max_id FROM table1;

    -- 外层循环遍历 table1 的所有行
    WHILE i <= max_id DO
        -- 内层循环处理每一行的数据
        SET names = CONCAT(names, (SELECT name FROM table1 WHERE id = i), ',');
        SET ids = CONCAT(ids, i, ',');

        -- 每 batch_size 行插入一次
        IF i % batch_size = 0 THEN
            INSERT INTO table2 (id, name) VALUES (ids, names);
            SET names = '';
            SET ids = '';
        END IF;

        SET i = i + 1;
    END WHILE;

    -- 插入剩余的数据
    IF names <> '' THEN
        INSERT INTO table2 (id, name) VALUES (ids, names);
    END IF;
END //

DELIMITER ;

CALL double_loop_batch_insert();