MySQL Loop: Iterate Through Rows with Cursors

1 year ago
Sophia Anderson
1 minute

In MySQL, you can use loop statements such as WHILE or REPEAT to loop through data queries. Here is a simple example:

Assume there is a table called “users” with two columns, id and name, and now it is required to loop through and query the names of all users.

DELIMITER $$

CREATE PROCEDURE get_users()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE user_id INT;
    DECLARE user_name VARCHAR(255);
    
    DECLARE cur CURSOR FOR SELECT id, name FROM users;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    get_users_loop: LOOP
        FETCH cur INTO user_id, user_name;
        IF done = 1 THEN
            LEAVE get_users_loop;
        END IF;

        -- 这里可以对每一条数据进行处理，例如输出到控制台
        SELECT user_name;
    END LOOP;
    
    CLOSE cur;
END$$

DELIMITER ;

Then call the stored procedure get_users() to execute the loop query.

CALL get_users();

This allows for looping through all user data in the users table. It is important to note that looping queries may increase the burden on the database, so it is best to avoid using them with large amounts of data.

bannerAds