How do I Traverse Rows in MySQL Tables?

Traversing Rows in MySQL Tables

While MySQL does not natively support row-by-row looping syntax like "for each record in A," it provides mechanisms to iterate through table rows systematically.

Using Procedures

One approach is to create a procedure that operates on individual rows. Procedures are user-defined blocks of code that can be executed like queries. Here's a MySQL procedure that loops through rows in table A:

DROP PROCEDURE IF EXISTS ROWPERROW;
DELIMITER ;;
CREATE PROCEDURE ROWPERROW()
BEGIN
  DECLARE n INT DEFAULT 0;
  DECLARE i INT DEFAULT 0;
  SELECT COUNT(*) FROM A INTO n;
  SET i=0;
  WHILE i<n do insert into b val select from a limit i set end while delimiter call rowperrow><p>This procedure counts the rows in table A, then iterates through each row and inserts its data into table B.</p>
<h3 id="Using-Cursors">Using Cursors</h3>
<p>Another technique is to usecursors, which allow you to retrieve data from a table row by row. Cursors provide more control over row traversal than procedures but can also be more verbose. Consider the following MySQL cursor example:</p>
<pre class="brush:php;toolbar:false">DROP PROCEDURE IF EXISTS cursor_ROWPERROW;
DELIMITER ;;
CREATE PROCEDURE cursor_ROWPERROW()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE cursor_i CURSOR FOR SELECT ID,VAL FROM 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 B(ID, VAL) VALUES(cursor_ID, cursor_VAL);
  END LOOP;
  CLOSE cursor_i;
END;
;;
DELIMITER ;
CALL cursor_ROWPERROW();

Here, a cursor is created for table A and opened. The WHILE loop fetches rows from the cursor and processes them until there are no more rows to process.

The above is the detailed content of How do I Traverse Rows in MySQL Tables?. For more information, please follow other related articles on the PHP Chinese website!