다른 테이블의 MAX 값을 이용해 MySQL AUTO_INCREMENT 재설정하는 방법

MySQL에서 다른 테이블에 저장된 최대(MAX) 값을 기준으로 AUTO_INCREMENT 값을 재설정해야 하는 경우가 있습니다. 이럴 때 PREPARE 문을 활용하면 동적으로 SQL문을 구성하여 손쉽게 처리할 수 있습니다.

기본 문법

다음은 다른 테이블의 MAX 값을 가져와 AUTO_INCREMENT를 재설정하는 기본 문법입니다.

set @anyVariableName1=(select MAX(yourColumnName) from yourTableName1);
SET @anyVariableName2 = CONCAT('ALTER TABLE yourTableName2
AUTO_INCREMENT=', @anyVariableName1);
PREPARE yourStatementName FROM @anyVariableName2;
execute yourStatementName;

위 문법은 첫 번째 변수에 대상 테이블의 최대값을 저장한 뒤, CONCAT 함수로 ALTER TABLE 문을 문자열로 조합하고, PREPARE로 준비한 후 EXECUTE로 실행하는 방식으로 동작합니다.

예제를 위한 첫 번째 테이블 생성

문법을 이해하기 위해 두 개의 테이블을 만들어 보겠습니다. 첫 번째 테이블에는 숫자 데이터가 저장되고, 두 번째 테이블은 첫 번째 테이블의 최대값을 AUTO_INCREMENT 시작값으로 사용합니다.

먼저 첫 번째 테이블을 생성하는 쿼리는 다음과 같습니다.

mysql> create table FirstTableMaxValue
   -> (
   -> MaxNumber int
   -> );
Query OK, 0 rows affected (0.64 sec)

레코드 삽입

INSERT 명령으로 레코드를 삽입합니다.

mysql> insert into FirstTableMaxValue values(100);
Query OK, 1 row affected (0.15 sec)

mysql> insert into FirstTableMaxValue values(1000);
Query OK, 1 row affected (0.19 sec)

mysql> insert into FirstTableMaxValue values(2000);
Query OK, 1 row affected (0.12 sec)

mysql> insert into FirstTableMaxValue values(90);
Query OK, 1 row affected (0.15 sec)

mysql> insert into FirstTableMaxValue values(2500);
Query OK, 1 row affected (0.17 sec)

mysql> insert into FirstTableMaxValue values(2300);
Query OK, 1 row affected (0.12 sec)

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

mysql> select *from FirstTableMaxValue;

출력 결과

+-----------+
| MaxNumber |
+-----------+
|       100 |
|      1000 |
|      2000 |
|        90 |
|      2500 |
|      2300 |
+-----------+
6 rows in set (0.05 sec)

테이블에는 총 6개의 값이 들어가 있으며, 그중 최대값은 2500입니다.

AUTO_INCREMENT가 적용된 두 번째 테이블 생성

이제 AUTO_INCREMENT 속성을 가진 두 번째 테이블을 생성합니다.

mysql> create table AutoIncrementWithMaxValueFromTable
   -> (
   -> ProductId int not null auto_increment,
   -> Primary key(ProductId)
   -> );
Query OK, 0 rows affected (1.01 sec)

PREPARE 문으로 AUTO_INCREMENT 재설정

다음으로 첫 번째 테이블에서 최대값을 조회한 후, 해당 값을 두 번째 테이블의 AUTO_INCREMENT 시작값으로 설정하는 문장을 실행합니다.

mysql> set @v=(select MAX(MaxNumber) from FirstTableMaxValue);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @Value2 = CONCAT('ALTER TABLE AutoIncrementWithMaxValueFromTable
AUTO_INCREMENT=', @v);
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE myStatement FROM @value2;
Query OK, 0 rows affected (0.29 sec)
Statement prepared

mysql> execute myStatement;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0

이 과정을 통해 첫 번째 테이블의 최대값인 2500이 두 번째 테이블의 AUTO_INCREMENT 시작값으로 설정되었습니다. 이제 새로운 레코드를 삽입하면 2500부터 차례대로 번호가 부여됩니다.

두 번째 테이블에 레코드 삽입 및 확인

두 번째 테이블에 레코드를 삽입하는 쿼리는 다음과 같습니다.

mysql> insert into AutoIncrementWithMaxValueFromTable values();
Query OK, 1 row affected (0.24 sec)

mysql> insert into AutoIncrementWithMaxValueFromTable values();
Query OK, 1 row affected (0.10 sec)

SELECT 명령으로 삽입된 레코드를 확인해 보겠습니다.

mysql> select *from AutoIncrementWithMaxValueFromTable;

출력 결과

+-----------+
| ProductId |
+-----------+
|      2500 |
|      2501 |
+-----------+
2 rows in set (0.00 sec)

출력 결과에서 확인할 수 있듯이, AUTO_INCREMENT 값이 첫 번째 테이블의 최대값인 2500부터 시작하여 2500, 2501 순으로 자동 증가하는 것을 볼 수 있습니다. 이처럼 PREPARE 문과 CONCAT 함수를 조합하면 다른 테이블의 값을 기준으로 AUTO_INCREMENT를 유연하게 재설정할 수 있습니다.