오늘은 mySQL AUTO_INCREMENT 값을 초기화 하는 방법을 소개하려고 합니다.
아래는 [heidiSQL] 화면인데요,
선택할 수 있는 기본값의 종류입니다.
그 중, 맨 아래에 있는 아이가 오늘 소개할 AUTO_INCREMENT 에요.
mySQL에서 PRIMARY KEY 에 AUTO_INCREMENT 속성을 주어서 키값을 자동으로 증가시킬 수 있습니다.
행의 값을 UNIQUE 하게 만들기 위한 것인데요,
이전 데이터가 삭제 된다고 해도 한 번 증가된 값은 다시 조정되지 않습니다.
예를 들어, 100개의 데이터를 넣은 다음, 1개 데이터를 삭제한 후, 다시 넣어보면 인덱스 값은 101이 됩니다.
이 AUTO_INCREMENT 값을 초기화 하려면, 아래와 같은 쿼리를 실행시켜주면 됩니다.
ALTER TABLE [TABLE명] AUTO_INCREMENT = [시작할 값]; | cs |
물론 이 경우에는, 테이블에 새로 시작할 값보다 높은 값이 있으면 안됩니다.
또 다른 방법은,
AUTO_INCREMENT 값을 초기화 후, 테이블 안의 모든 데이터의 ID값을 재조정하는 방법입니다.
ALTER TABLE [테이블명] AUTO_INCREMENT=1; SET @COUNT = 0; UPDATE [테이블명] SET [AUTO_INCREMENT 열 이름] = @COUNT:=@COUNT+1; | cs |
위 쿼리문은 현재 모든 데이터 ID값을 1부터 재조정하고,
다음 인덱스 값을 [마지막 행번호 + 1] 로 정하는 명령어입니다.
예를 들면, 테이블 `tb_board_item` 의 AUTO_INCREMENT 값을 초기화 후, 모든 데이터의 인덱스를 1부터 조정하려면, 다음과 같습니다.
ALTER TABLE `tb_board_item` AUTO_INCREMENT=1; SET @COUNT = 0; UPDATE `tb_board_item` SET board_item_key = @COUNT:=@COUNT+1; | cs |
전 테스트 하면서 데이터 넣고 지우고 반복하다 보면 한번씩 쓰게 되더라구요ㅎㅎ
다른 분들에게도 도움이 되면 좋겠습니다
1. AUTO_INCREMENT 조회
people 테이블
ex) CREATE TABLE people (number INT PRIMARY KEY AUTO_INCREMENT , name VARCHAR(255));
데이터 삽입
ex) INSERT INTO people VALUES (NULL, "name_A");
ex) INSERT INTO people VALUES (NULL, "name_B");
ex) INSERT INTO people VALUES (NULL, "name_C");
* number가 자동으로 1씩 증가하여 삽입
Auto_increment 확인
mysql> SHOW TABLE STATUS WHERE name = '테이블 이름';
ex) SHOW TABLE STATUS WHERE name = 'people';
또는
mysql> SHOW TABLE STATUS LIKE '테이블 이름';
ex) SHOW TABLE STATUS LIKE 'people';
* 다음에 삽입될 Auto_increment(number 컬럼)의 값 확인
2. AUTO_INCREMENT 초기화(변경)
mysql> ALTER TABLE 테이블 이름 AUTO_INCREMENT=변경할 숫자;
AUTO_INCREMENT를 초기화(변경)할 때는 변경할 AUTO_INCREMENT의 값이 해당 컬럼에서 가장 큰 값보다 커야 합니다.
현재 people 테이블에서 Auto_increment의 값은 4입니다.
AUTO_INCREMENT로 설정한 number 컬럼에서 가장 큰 값이 3이므로 Auto_increment를 3보다 큰 값으로 변경 가능합니다.
* 3보다 크면 아무 값이나 상관없음
예제
AUTO_INCREMENT를 5로 변경
ex) ALTER TABLE people AUTO_INCREMENT=5;
// people 테이블의 AUTO_INCREMENT 값을 5로 변경
* 변경된 것을 확인(SHOW TABLE STATUS LIKE 'people';)
1) 데이터 삽입
ex) INSERT INTO people VALUES (NULL, "name_D");
Auto_increment를 5로 변경했기 때문에 number에 5가 삽입됩니다.
삽입을 완료하면 Auto_increment는 6이 됩니다.
다시 AUTO_INCREMENT를 4로 변경하려고 하더라도 오류는 발생하지 않지만 number 컬럼의 가장 큰 값이 5이기 때문에 Auto_increment의 값은 4로 변경되지 않고 6으로 되어있습니다.
* 현재 5보다 큰 숫자로만 변경 가능
2) 데이터 삭제
ex) DELETE FROM people WHERE number=5;
number가 5인 데이터를 삭제하면 number 컬럼에서 가장 큰 값이 3이기 때문에 Auto_increment를 4로 변경 가능합니다.
* 현재 3보다 큰 숫자로 변경 가능
* 참고
Auto_increment를 1로 초기화하려면 테이블의 데이터가 없어야 합니다.
3. AUTO_INCREMENT 1부터 순서대로 정렬
레코드 삭제와 업데이트 등과 같은 이유로 AUTO_INCREMENT로 설정된 컬럼의 숫자가 난잡하게 될 수 있습니다. 이때 보기 좋게 1부터 순서대로 정렬할 수 있습니다.
예제
현재 people 테이블에서는 number 컬럼의 데이터가 1부터 순서대로 정렬되어있지 않으며 Auto_increment는 6입니다.
SET 명령으로 변수 COUNT를 0으로 지정합니다.
mysql> UPDATE 테이블 이름 SET 컬럼 이름=@count:=@count+1;
UPDATE 명령으로 AUTO_INCREMENT의 컬럼을 1부터 순서대로 정렬합니다.
ex) UPDATE people SET number=@count:=@count+1;
mysql> ALTER TABLE 테이블 이름 AUTO_INCREMENT=변경할 숫자;
ALTER 명령으로 AUTO_INCREMENT의 값을 변경합니다.
ex) ALTER TABLE people AUTO_INCREMENT=4;