개요
MySQL에서 특정 행을 삭제한 후, 남은 행들의 ID(번호)를 올바르게 재정렬해야 하는 경우가 있습니다. 이 글에서는 DELETE 문으로 행을 삭제하고, UPDATE 문으로 나머지 행의 ID를 조정하여 순서를 재정렬하는 방법을 예제와 함께 단계별로 살펴보겠습니다.
1. 테이블 생성
개념을 이해하기 위해 먼저 테이블을 생성하겠습니다. 테이블 생성 쿼리는 다음과 같습니다.
mysql> create table ReorderSortDemo
-> (
-> UserId int
-> );
Query OK, 0 rows affected (0.57 sec)
2. 샘플 데이터 삽입
INSERT 명령을 사용하여 테이블에 레코드를 삽입합니다. 쿼리는 다음과 같습니다.
mysql> insert into ReorderSortDemo values(14);
Query OK, 1 row affected (0.13 sec)
mysql> insert into ReorderSortDemo values(4);
Query OK, 1 row affected (0.10 sec)
mysql> insert into ReorderSortDemo values(6);
Query OK, 1 row affected (0.11 sec)
mysql> insert into ReorderSortDemo values(3);
Query OK, 1 row affected (0.09 sec)
mysql> insert into ReorderSortDemo values(8);
Query OK, 1 row affected (0.11 sec)
mysql> insert into ReorderSortDemo values(18);
Query OK, 1 row affected (0.08 sec)
mysql> insert into ReorderSortDemo values(1);
Query OK, 1 row affected (0.12 sec)
mysql> insert into ReorderSortDemo values(11);
Query OK, 1 row affected (0.08 sec)
mysql> insert into ReorderSortDemo values(16);
Query OK, 1 row affected (0.09 sec)
3. 전체 레코드 조회
SELECT 문으로 테이블의 모든 레코드를 확인합니다.
mysql> select *from ReorderSortDemo;
실행 결과는 다음과 같습니다.
+--------+
| UserId |
+--------+
| 14 |
| 4 |
| 6 |
| 3 |
| 8 |
| 18 |
| 1 |
| 11 |
| 16 |
+--------+
9 rows in set (0.00 sec)
4. 행 삭제하기
먼저 테이블에서 한 개의 행을 삭제합니다. 여기서는 UserId가 8인 행을 삭제하겠습니다.
mysql> delete from ReorderSortDemo where UserId=8;
Query OK, 1 row affected (0.20 sec)
삭제 후 테이블의 레코드를 다시 확인해 보겠습니다.
mysql> select *from ReorderSortDemo;
출력 결과는 다음과 같습니다.
+--------+
| UserId |
+--------+
| 14 |
| 4 |
| 6 |
| 3 |
| 18 |
| 1 |
| 11 |
| 16 |
+--------+
8 rows in set (0.00 sec)
5. 나머지 행의 ID 재정렬하기
삭제된 행(ID 8)보다 큰 ID를 가진 행들의 값을 1씩 감소시키면 빈자리가 메워지면서 순서가 자연스럽게 재정렬됩니다. 이를 위한 UPDATE 쿼리는 다음과 같습니다.
mysql> update ReorderSortDemo
-> set UserId=UserId-1
-> where UserId > 8;
Query OK, 4 rows affected (0.22 sec)
Rows matched: 4 Changed: 4 Warnings: 0
테이블의 레코드를 다시 한번 확인해 보겠습니다.
mysql> select *from ReorderSortDemo;
출력 결과는 다음과 같습니다.
+--------+
| UserId |
+--------+
| 13 |
| 4 |
| 6 |
| 3 |
| 17 |
| 1 |
| 10 |
| 15 |
+--------+
8 rows in set (0.00 sec)
마무리
위 예제에서 확인할 수 있듯이, UserId가 8이었던 행을 삭제한 후 UPDATE ... SET UserId = UserId - 1 WHERE UserId > 8 쿼리를 실행하면 삭제된 행 뒤에 있던 행들의 ID가 각각 1씩 감소합니다(14→13, 18→17, 11→10, 16→15). 이처럼 DELETE 문과 UPDATE 문을 함께 활용하면 행 삭제 후 남은 데이터의 ID를 손쉽게 재정렬할 수 있습니다. 다만 실제 운영 환경에서는 ID 값이 다른 테이블의 외래 키로 참조되고 있을 수 있으므로, 이러한 재정렬 작업은 참조 무결성에 미치는 영향을 반드시 고려한 후 신중하게 적용하는 것이 좋습니다.