MySQL 테이블에서 레코드 삭제 후 키(ID) 재정렬하는 방법

MySQL 테이블에서 특정 레코드를 삭제한 후 남은 데이터의 ID(키)를 연속된 순서로 다시 정렬하고 싶다면 UPDATE 문과 간단한 수학적 계산을 활용하면 됩니다. 레코드를 삭제할 때는 DELETE 명령어를 사용합니다.

1. 테이블 생성하기

먼저 예제에 사용할 테이블을 생성합니다.

mysql> create table DemoTable1476
    -> (
    -> Id int
    -> );
Query OK, 0 rows affected (0.81 sec)

2. 샘플 데이터 삽입하기

INSERT 명령어를 사용해 테이블에 여러 개의 레코드를 추가합니다.

mysql> insert into DemoTable1476 values(10);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1476 values(20);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1476 values(30);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1476 values(40);
Query OK, 1 row affected (0.12 sec)

3. 전체 레코드 조회하기

SELECT 문으로 테이블의 모든 레코드를 확인합니다.

mysql> select * from DemoTable1476;

실행 결과는 다음과 같습니다.

+------+
| Id   |
+------+
|   10 |
|   20 |
|   30 |
|   40 |
+------+
4 rows in set (0.00 sec)

4. 특정 레코드 삭제하기

다음 쿼리로 Id가 30인 레코드를 삭제합니다.

mysql> delete from DemoTable1476 where Id=30;
Query OK, 1 row affected (0.16 sec)

삭제 후 테이블 상태를 확인하면 다음과 같습니다.

mysql> select * from DemoTable1476;

실행 결과는 다음과 같습니다.

+------+
| Id   |
+------+
|   10 |
|   20 |
|   40 |
+------+
3 rows in set (0.00 sec)

5. 삭제 후 키 재정렬하기

레코드가 삭제되면 ID 사이에 빈 공간이 생깁니다. 이때 UPDATE 문을 사용해 삭제된 ID보다 큰 값들을 일괄적으로 조정하면 키를 다시 정렬할 수 있습니다.

mysql> update DemoTable1476 set Id=Id-10 where Id > 30;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1 Warnings: 0

위 쿼리는 Id가 30보다 큰 레코드에서 10을 빼는 방식으로, 삭제된 값만큼 이후 레코드들의 ID를 앞당겨 연속성을 유지합니다.

6. 최종 결과 확인하기

테이블 레코드를 다시 한번 확인해 보겠습니다.

mysql> select * from DemoTable1476;

실행 결과는 다음과 같습니다.

+------+
| Id   |
+------+
|   10 |
|   20 |
|   30 |
+------+
3 rows in set (0.00 sec)

이처럼 DELETE로 레코드를 삭제한 뒤, UPDATE 문에 조건부 수식을 적용하면 중간에 비어 있는 ID 없이 깔끔하게 재정렬된 결과를 얻을 수 있습니다.