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(ID, VAL) SELECT (ID, VAL) FROM A LIMIT i,1;
    SET i = i + 1;
  END WHILE;
End;
;;
DELIMITER ;
CALL ROWPERROW();

This procedure counts the rows in table A, then iterates through each row and inserts its data into table B.

Using Cursors

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:

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!