Looping Through Table Rows in MySQL: Stored Procedures and Cursors Explained

Introduction

In database operations, there are scenarios where individual processing of each row in a table is necessary, such as data transformation, complex calculations, or cross-table operations. While SQL is primarily based on set operations, MySQL provides stored procedures and cursor mechanisms to fulfill row-by-row processing requirements.

Stored Procedure Implementation with WHILE Loop

Stored procedures are a common approach for implementing loop iterations in MySQL. The following complete example demonstrates how to process table data row by row using a WHILE loop:

DROP PROCEDURE IF EXISTS ROWPERROW;
DELIMITER ;;

CREATE PROCEDURE ROWPERROW()
BEGIN
  DECLARE n INT DEFAULT 0;
  DECLARE i INT DEFAULT 0;
  SELECT COUNT(*) FROM table_A INTO n;
  SET i = 0;
  WHILE i < n DO 
    INSERT INTO table_B(ID, VAL) SELECT ID, VAL FROM table_A LIMIT i, 1;
    SET i = i + 1;
  END WHILE;
END;;

DELIMITER ;
CALL ROWPERROW();

Key aspects of this implementation include: first changing the statement delimiter using the DELIMITER command to prevent premature execution during procedure creation. Then declaring counter variables within the procedure, obtaining the total row count via SELECT COUNT(*), and finally processing data row by row using the WHILE loop.

Cursor-Based Implementation Method

Cursors provide an alternative approach for row-by-row data processing, typically offering better readability and flexibility:

DROP PROCEDURE IF EXISTS cursor_ROWPERROW;
DELIMITER ;;

CREATE PROCEDURE cursor_ROWPERROW()
BEGIN
  DECLARE cursor_ID INT;
  DECLARE cursor_VAL VARCHAR(255);
  DECLARE done INT DEFAULT FALSE;
  DECLARE cursor_i CURSOR FOR SELECT ID, VAL FROM table_A;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN cursor_i;
  read_loop: LOOP
    FETCH cursor_i INTO cursor_ID, cursor_VAL;
    IF done THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO table_B(ID, VAL) VALUES(cursor_ID, cursor_VAL);
  END LOOP;
  CLOSE cursor_i;
END;;

DELIMITER ;

The core steps in cursor implementation include: declaring cursor variables, defining an end condition handler, opening the cursor, looping to fetch data, and finally closing the cursor. This method is more suitable for handling complex data logic and conditional judgments.

Performance Analysis and Optimization Recommendations

In practical applications, the performance of loop operations is a critical consideration. According to the large-scale data migration case referenced, processing 50 million rows with row-by-row operations can lead to significant performance issues.

Performance comparison analysis:

• WHILE loop method: Suitable for simple scenarios, but requires executing individual SQL statements in each iteration
• Cursor method: More efficient in memory usage, but may be subject to database connection timeout limitations
• Set-based operations: Should be prioritized when possible for optimal performance

Practical Application Scenario Analysis

In data migration, data cleansing, and complex business logic processing, loop iterations are essential tools. The customer data deduplication scenario from the reference article demonstrates the practical value of loop operations: requiring traversal of each customer record, identification of duplicate data, and updating foreign key references in related tables.

However, for large-scale data processing, a batch processing strategy is recommended:

-- Batch processing example
SET n = 10000; -- Process 10,000 records at a time
SET i = 0;
WHILE i < (SELECT COUNT(*) FROM table_A) DO
  INSERT INTO table_B(ID, VAL) 
  SELECT ID, VAL FROM table_A LIMIT i, 10000;
  SET i = i + 10000;
END WHILE;

Best Practices and Considerations

When using loop iterations, the following points should be considered:

1. Transaction management: Ensure transactions are committed at appropriate intervals to avoid prolonged table locking
2. Error handling: Use DECLARE ... HANDLER to handle potential exceptions
3. Variable type matching: Ensure cursor variables match the query field types
4. Resource release: Close cursors promptly to free database resources

Conclusion

Loop iteration mechanisms in MySQL provide necessary tools for complex data processing. While set-based operations generally offer better performance, stored procedures and cursors are indispensable solutions in specific scenarios. Developers should choose appropriate methods based on specific requirements, data scale, and performance considerations, prioritizing set-based SQL operations whenever possible.