MySQL에서 AUTO_INCREMENT로 설정된 기본 키(PK) 컬럼의 값도 UPDATE 문을 사용하면 충분히 변경할 수 있습니다. 이번 글에서는 기존 행들의 ID 값을 한 번에 이동시키는 방법을 예제와 함께 살펴보겠습니다.
1. 테이블 생성하기
먼저 예제에 사용할 테이블을 생성합니다.
mysql> create table DemoTable
-> (
-> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> StudentName varchar(20)
-> );
Query OK, 0 rows affected (1.07 sec)
StudentId 컬럼은 자동 증가(AUTO_INCREMENT)되는 기본 키로 설정했습니다.
2. 샘플 데이터 삽입하기
INSERT 문을 사용해 몇 개의 레코드를 추가합니다.
mysql> insert into DemoTable(StudentName) values('Chris');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable(StudentName) values('Robert');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable(StudentName) values('David');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable(StudentName) values('Mike');
Query OK, 1 row affected (0.10 sec)3. 현재 데이터 확인하기
SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 1 | Chris |
| 2 | Robert |
| 3 | David |
| 4 | Mike |
+-----------+-------------+
4 rows in set (0.00 sec)
4. ID 값 일괄 이동하기
이제 기존 행들의 ID 값을 한꺼번에 이동시키는 핵심 쿼리입니다. 컬럼 값을 자기 자신에 더하는 방식으로 UPDATE를 수행합니다.
mysql> update DemoTable set StudentId=StudentId+1000;
Query OK, 4 rows affected (0.18 sec)
Rows matched: 4 Changed: 4 Warnings: 0
위 쿼리는 모든 행의 StudentId에 1000을 더합니다. MySQL은 UPDATE 시 각 행을 순차적으로 처리하므로, 기본 키의 중복 오류 없이 값이 정상적으로 이동됩니다.
5. 변경 결과 확인하기
다시 한번 테이블을 조회해 결과를 확인해 보겠습니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 1001 | Chris |
| 1002 | Robert |
| 1003 | David |
| 1004 | Mike |
+-----------+-------------+
4 rows in set (0.00 sec)
정리 및 주의사항
이처럼 UPDATE 테이블명 SET PK컬럼 = PK컬럼 + 이동값 형태의 쿼리 하나만으로 기존 행들의 ID 값을 손쉽게 이동할 수 있습니다.
다만 실무에서는 다음 사항에 유의해야 합니다.
- 해당 ID를 참조하는 외래 키(Foreign Key)가 있다면 ON UPDATE CASCADE 설정 여부를 먼저 확인해야 합니다.
- AUTO_INCREMENT 카운터는 자동으로 조정되지 않으므로, 필요하다면 ALTER TABLE로 다음 시작 값을 지정해 주는 것이 좋습니다.
- 대용량 테이블에서는 잠금(lock)과 성능 이슈가 발생할 수 있으므로 트랜잭션 내에서 신중하게 실행하는 것이 안전합니다.