MySQL Data Table Traversal method

MySQL traversal of data tables is a common problem. The following describes how to traverse data tables in MySQL. I hope it will help you learn how to traverse data tables in MySQL.

 
 

  
  
DELIMITER $
  
  
 
  
  
USE 'tns $
  
  
 
  
  
Drop procedure if exists 'makefriends '$
  
  
 
  
  
Create definer = 'root' @ 'localhost' PROCEDURE 'makefriends '()
  
  
BEGIN
  
  
DECLARE v_adminId BIGINT (12 );
  
  
DECLARE v_currentUserID BIGINT (12 );
  
  
DECLARE v_ExistRela INT;
  
  
DECLARE Done int default 0;
  
  
DECLARE rs cursor for select userId FROM hw_user;
  
  
/* Exception Handling */
  
  
Declare continue handler for sqlstate '000000' SET Done = 1;
  
  
 
  
  
SET v_adminId = 1;
  
  
 
  
  
/* Open the cursor */
  
  
OPEN rs;
  
  
 
  
  
/* Retrieve the values of The userId field of the current record one by one */
  
  
Fetch next from rs INTO v_currentUserID;
  
  
/* Traverse the data table */
  
  
REPEAT
  
  
If not Done THEN
  
  
Select count (*) FROM hw_relationship WHERE sourceId = v_currentUserID AND targetId = v_adminId INTO v_ExistRela;
  
  
/* SELECT v_currentUserID; SELECT v_ExistRela ;*/
  
  
IF v_currentUserID! = V_adminId AND v_ExistRela = 0 THEN
  
  
/* Insert relation records */
  
  
Insert into hw_relationship (createTime, relationType, sourceId, targetId) VALUES (NOW (), 1, v_currentUserID, v_adminId );
  
  
Insert into hw_relationship (createTime, relationType, sourceId, targetId) VALUES (NOW (), 1, v_adminId, v_currentUserID );
  
  
End if;
  
  
End if;
  
  
 
  
  
Fetch next from rs INTO v_currentUserID;
  
  
 
  
  
UNTIL Done end repeat;
  
  
 
  
  
/* Close the cursor */
  
  
CLOSE rs;
  
  
END $
  
  
 
  
  
DELIMITER;
  
  
 
 
 

Implementation of batch data import in MySQL

Create a temporary table in php mysql

Quick table creation in mysql

In-depth study on MySQL's data deletion from multiple tables

Simple MySQL large table backup method