MySQL에서 테이블의 모든 행을 하나씩 반복(loop) 처리하려면 저장 프로시저(Stored Procedure)를 활용하면 됩니다. 저장 프로시저 안에서 변수를 선언하고 WHILE 문을 사용해 전체 행 수만큼 순회하는 방식입니다.
기본 문법
delimiter //
CREATE PROCEDURE yourProcedureName()
BEGIN
DECLARE anyVariableName1 INT DEFAULT 0;
DECLARE anyVariableName2 INT DEFAULT 0;
SELECT COUNT(*) FROM yourTableName1 INTO anyVariableName1;
SET anyVariableName2 =0;
WHILE anyVariableName2 < anyVariableName1 DO
INSERT INTO yourTableName2(yourColumnName,...N) SELECT (yourColumnName1,...N)
FROM yourTableName1 LIMIT anyVariableName2,1;
SET anyVariableName2 = anyVariableName2+1;
END WHILE;
End;
//
문법 이해하기
위 문법의 동작 흐름은 다음과 같습니다.
먼저 첫 번째 변수에는 대상 테이블의 전체 행 개수(COUNT)를 저장합니다. 두 번째 변수는 반복 시작 위치를 나타내며 0으로 초기화합니다. 그다음 WHILE 문을 통해 두 번째 변수가 전체 행 수보다 작은 동안 LIMIT 절을 이용해 한 행씩 읽어 다른 테이블에 삽입하고, 변수 값을 1씩 증가시키며 모든 행을 순회합니다.
실제 예제를 통해 이해해 보겠습니다. 두 개의 테이블을 생성합니다. 첫 번째 테이블에는 원본 데이터가 들어가고, 두 번째 테이블에는 저장 프로시저의 반복문을 통해 데이터가 복사됩니다.
1단계: 첫 번째 테이블 생성
다음 쿼리로 첫 번째 테이블을 만듭니다.
mysql> create table AllRows
-> (
-> Id int,
-> Name varchar(100)
-> );
Query OK, 0 rows affected (0.46 sec)
2단계: 샘플 데이터 삽입
INSERT 명령으로 첫 번째 테이블에 레코드를 추가합니다.
mysql> insert into AllRows values(1,'John');
Query OK, 1 row affected (0.12 sec)
mysql> insert into AllRows values(100,'Carol');
Query OK, 1 row affected (0.13 sec)
mysql> insert into AllRows values(300,'Sam');
Query OK, 1 row affected (0.15 sec)
mysql> insert into AllRows values(400,'Mike');
Query OK, 1 row affected (0.20 sec)
SELECT 문으로 테이블에 저장된 모든 레코드를 확인합니다.
mysql> select *from AllRows;
출력 결과
+------+-------+
| Id | Name |
+------+-------+
| 1 | John |
| 100 | Carol |
| 300 | Sam |
| 400 | Mike |
+------+-------+
4 rows in set (0.00 sec)
3단계: 두 번째 테이블 생성
반복 처리된 데이터가 저장될 두 번째 테이블을 생성합니다.
mysql> create table SecondTableRows
-> (
-> StudentId int,
-> StudentName varchar(100)
-> );
Query OK, 0 rows affected (0.54 sec)
4단계: 저장 프로시저 생성
이제 저장 프로시저를 만들어 첫 번째 테이블의 모든 행을 반복 처리합니다.
mysql> delimiter //
mysql> CREATE PROCEDURE Sp_AllRowsOfATable()
-> BEGIN
-> DECLARE lastRows INT DEFAULT 0;
-> DECLARE startRows INT DEFAULT 0;
-> SELECT COUNT(*) FROM AllRows INTO lastRows;
-> SET startRows=0;
-> WHILE startRows <lastRows DO
-> INSERT INTO SecondTableRows(StudentId) SELECT (Id) FROM AllRows LIMIT
startRows ,1;
-> SET startRows= startRows+1;
-> END WHILE;
-> End;
-> //
Query OK, 0 rows affected (0.22 sec)
mysql> delimiter ;
프로시저 내부를 살펴보면, lastRows 변수에 AllRows 테이블의 전체 행 수(4)가 저장되고, startRows 변수가 0부터 시작해 LIMIT 절의 오프셋 역할을 하면서 한 번에 한 행씩 SecondTableRows로 복사됩니다.
5단계: 저장 프로시저 호출
생성한 저장 프로시저는 CALL 명령으로 실행할 수 있습니다. 기본 문법은 다음과 같습니다.
CALL yourStoredProcedureName;
실제로 위에서 만든 프로시저를 호출해 보겠습니다.
mysql> call Sp_AllRowsOfATable();
Query OK, 1 row affected (0.61 sec)
6단계: 결과 확인
저장 프로시저 호출이 끝난 후 두 번째 테이블을 조회하면, 첫 번째 테이블의 모든 행이 성공적으로 복사된 것을 확인할 수 있습니다.
mysql> select StudentId from SecondTableRows;
출력 결과
+-----------+
| StudentId |
+-----------+
| 1 |
| 100 |
| 300 |
| 400 |
+-----------+
4 rows in set (0.00 sec)
마무리
이처럼 MySQL에서는 저장 프로시저와 WHILE 문, LIMIT 절을 조합하여 테이블의 모든 행을 손쉽게 반복 처리할 수 있습니다. 단, 행 단위 반복은 대용량 데이터에서는 성능 저하가 발생할 수 있으므로, 단순한 테이블 간 복사라면 INSERT INTO ... SELECT 문 하나로 처리하는 것이 더 효율적이라는 점도 함께 기억해 두세요.