How can you use a for loop statement to add data in MySQL?

2 years ago
Benjamin Taylor
1 minute

In MySQL, stored procedures and cursors can be used to achieve a functionality similar to a for loop to add data.

Firstly, create a stored procedure to use a cursor to iterate through the data that needs to be added.

DELIMITER //
CREATE PROCEDURE add_data()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE id INT;
    DECLARE value VARCHAR(255);

    -- 创建一个游标来遍历需要添加的数据
    DECLARE cur CURSOR FOR SELECT id, value FROM your_table;
    
    -- 如果没有更多的数据，则设置 done 为 TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN cur;

    loop_insert: LOOP
        -- 从游标中获取数据
        FETCH cur INTO id, value;

        -- 如果没有更多的数据，则退出循环
        IF done THEN
            LEAVE loop_insert;
        END IF;

        -- 添加数据到目标表中
        INSERT INTO target_table (id, value) VALUES (id, value);

    END LOOP loop_insert;

    -- 关闭游标
    CLOSE cur;
END //
DELIMITER ;

Next, call the stored procedure to execute the data addition operation.

CALL add_data();

In this way, you can use a method similar to a for loop to add data to the target table. Please modify the table name, field names, and conditions accordingly.

bannerAds