MySQL 테이블 자동 증가 AUTO_INCREMENT 속성의 값 초기화와 재 설정

자동증가 속성이 부여된 열은 새 행이 추가될 때마다 값이 1씩 자동으로 증가합니다.

자동 증가 값은 테이블 행의 삭제와 무관하게 새 행이 추가되면 값이 계속 증가합니다.

테이블 행을 모두 소거한 후 자동 증가 값을 1부터 다시 시작해야 하거나, 빈번한 행 삭제로 인해 자동 증가 열의 값이 불규칙해서 순서대로 값을 다시 부여할 때도 자동 증가 값은 초기화를 통해 적당히 원하는 값으로 재 설정을 할 필요가 있습니다.

자동 증가 속성이 설정된 테이블의 자동 증가 값을 초기화하는 쿼리문은 다음과 같습니다.

자동 증가 값은 테이블에 1개만 있을 수 있기 때문에 현재 자동 증가 값을 재설정 할 때 별도의 열 표시는 할 필요가 없습니다. 현재 자동 증가 값을 재 설정할 때 표시하는 값은 상수 값만 가능합니다.

자동 증가 값 초기화

ALTER TABLE 테이블명 AUTO_INCREMENT = 정수 값;

자동 증가 값을 순서대로 재 생성

주의해야 할 점이 있는데, 값을 초기화한 후 새 행을 추가하면 자동 증가 값의 중복이 나타날 수 있습니다. 이런 경우 자동 증가 속성을 지정한 컬럼의 값 충돌이 발생해서 행 추가가 안됩니다.

SET @num := 0;  /* 100을 지정하면 100부터 순서대로 자동 증가 값이 재지정 */
UPDATE 테이블명 SET 컬럼명 = @num := (@num+1);

양의 정수만 들어가는 자동증가 컬럼으로 변경

테이블의 자동 증가 컬럼 속성을 양의 정수만 들어가도록 다음과 같이 변경할 수 있습니다.

ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 INT(10) UNSIGNED AUTO_INCREMENT;

자동 증가 컬럼 값을 초기화해서 자동 증가 컬럼 값을 순서대로 재적용하고 싶으면 자동 증가 컬럼을 삭제한 후 재생성 하면 됩니다. 자동 증가 컬럼을 재생성하면 컬럼 값이 순차적으로 자동 입력되고, 현재 자동 증가 최대 값까지 모두 자동으로 설정됩니다.

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
ALTER TABLE 테이블명 ADD 컬럼명 INT(10) NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (컬럼명);

테이블 스키마만 남기고 모두 초기화

테이블의 데이터를 일괄로 지우는 쿼리문인 "TRUNCATE"를 사용하면 테이블 내용을 지우는 것과 함께 자동 증가 값도 자동으로 초기화 됩니다.

테이블 스키마를 제외하고 테이블 전체의 내용을 지우는 것이므로 주의해서 사용해야 합니다.

TRUNCATE TABLE 테이블명;

자동 증가 값을 최대값 + 1로 재설정

자동 증가 컬럼의 값 중 가장 큰 값보다 1 큰 값으로 현재 자동 증가 값을 재설정 하려면 다음과 같이 쿼리문을 작성합니다. 최대 값은 당연히 쿼리문으로 집계함수 max()로 따로 얻어야 합니다.

ALTER TABLE 테이블명 AUTO_INCREMENT = 자동증가 컬럼의 최대값+1;

쿼리문에 적용하는 값은 상수만 사용할 수 있습니다.

다음과 같은 그럴 듯한 쿼리문은 에러가 발생합니다.

ALTER TABLE 테이블명 AUTO_INCREMENT = (SELECT MAX(컬럼명)+1 FROM 테이블명);

실제로는 다음과 같은 다소 복잡한 과정을 거쳐서 쿼리문을 실행해야 자동 증가 현재 값을 "최대값+1"로 할당할 수 있습니다.

set @max = (SELECT MAX(자동증가컬럼명)+ 1 FROM 테이블명); /* 자동 증가 컬럼의 최대값+1 값을 @max 변수에 할당*/
set @str = CONCAT('ALTER TABLE 테이블명 AUTO_INCREMENT = ', @max); /* @str에 'ALTER TABLE 테이블명 AUTO_INCREMENT = 최대값' 문자열 할당 */
PREPARE qry FROM @str; /* @str 변수의 문자열을 실행 가능한 쿼리문으로 qry 객체에 할당*/
EXECUTE qry; /* qry 쿼리문 객체 실행*/
DEALLOCATE PREPARE qry; /* qry 쿼리문 객체 해제 */

자주 사용하는 경우, 조금 더 세련되게 스토어드 프로시저로 작성해서 호출해서 사용할 수도 있습니다.

DELIMITER //
CREATE PROCEDURE reset_autoincrement(IN 테이블명 varchar(200))
BEGIN

    set @max = (SELECT MAX(자동증가컬럼명)+ 1 FROM 테이블명); 
    set @str = CONCAT('ALTER TABLE 테이블명 AUTO_INCREMENT = ', @max);
    PREPARE qry FROM @str;
    EXECUTE qry;
    DEALLOCATE PREPARE qry;

END //
DELIMITER ;

call reset_autoincrement('테이블명'); /* 테이블명을 파라메터로 넘겨서 자동 증가 컬럼 값들을 순차 값으로 재설정 */