[MySQL] AUTO_INCREMENT 사용법

테이블 생성시 auto_increment 설정

CREATE TABLE people 
(
    number INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

 

AUTO_INCREMENT 확인

다음에 삽입될 Auto_increment(number 컬럼)의 값 확인

SHOW TABLE STATUS WHERE name = '테이블 이름';
SHOW TABLE STATUS LIKE '테이블 이름';

mysql> show table status where name = 'people' \G;
*************************** 1. row ***************************
           Name: _people
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 82
    Create_time: 2021-12-31 15:02:42
    Update_time: 2021-12-31 15:10:03
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

AUTO_INCREMENT  초기값 변경

AUTO_INCREMENT를 초기화(변경)할 때는 변경할 AUTO_INCREMENT의 값이 해당 컬럼에서 가장 큰 값보다 커야 합니다

ALTER TABLE 테이블 이름 AUTO_INCREMENT=변경할 숫자;

mysql> alter table _people auto_increment=5
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status where name = 'people' \G;
*************************** 1. row ***************************
           Name: _people
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 5
    Create_time: 2021-12-31 15:57:51
    Update_time: 2021-12-31 15:10:03
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

AUTO_INCREMENT  1부터 순서대로 정렬

레코드 삭제와 업데이트 등과 같은 이유로 AUTO_INCREMENT로 설정된 컬럼의 숫자가 난잡하게 될 수 있습니다. 이때 보기 좋게 1부터 순서대로 정렬할 수 있습니다.

정렬 전 데이터

SET @count=0;
UPDATE 테이블 이름 SET 컬럼 이름=@count:=@count+1;

mysql> SET @count=0;
mysql> UPDATE people SET number=@count:=@count+1;

정렬 후 데이터

# 데이터 정렬 후 AUTO_INCREMENT 값을 변경
ALTER TABLE 테이블 이름 AUTO_INCREMENT=변경할 숫자;

mysql> alter table people AUTO_INCREMENT=4;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

예제

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로 초기화하려면 테이블의 데이터가 없어야 합니다.

 

참고

https://m.blog.naver.com/dldudcks1779/222006115309