MySQL auto_increment 값 조정 /초기화 하려면?

MySQL에서 auto_increment 값을 조정하려면?

MySQL에서 id를 지정하고 auto_increment 옵션을 지정하면, 데이터를 추가할 때 마다 1씩 자동 증가한다.
그런데, 이 값을 다시 임의의 값으로 조정하거나 초기화 하려면 어떻게 해야 할까?

 

테스트 테이블 구조

다음과 같은 테이블로 테스트 해 보자.

CREATE TABLE IF NOT EXISTS `idtest` (
    id int(6) AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

따라서, 이 테이블에 insert를 하면, id가 1씩 증가하도록 사용할 수 있는 상태이다.

 

 

 

 

insert 하기

이 테이블에서 insert를 테스트 하면 아래와 같다.

mysql> insert into idtest(name) values('John');
Query OK, 1 row affected (0.16 sec)

insert into idtest(name) values('Jane');
Query OK, 1 row affected (0.03 sec)

mysql> select * from idtest;
+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Jane |
+----+------+
2 rows in set (0.00 sec)

 

delete 한 후, 다시 insert 하면?

만약, 2번 데이터를 삭제한 후 다시 insert를 해보면 id는 어떻게 될까?

mysql> delete from idtest where id = 2;
Query OK, 1 row affected (0.07 sec)

mysql> select * from idtest;
+----+------+
| id | name |
+----+------+
|  1 | John |
+----+------+
1 row in set (0.01 sec)

mysql> insert into idtest(name) values('Tom');
Query OK, 1 row affected (0.03 sec)

mysql> select * from idtest;
+----+------+
| id | name |
+----+------+
|  1 | John |
|  3 | Tom  |
+----+------+
2 rows in set (0.01 sec)

원래 2가 id의 최대값이었는데, 삭제 했다고 해서 그 값을 다시 채번해 주지는 않는 것을 확인할 수 있다.

 

임의의 큰 값으로 점프했다면?

이번에는 id를 지정하되, 임의의 값으로 점프해서 저장해 보자.

mysql> insert into idtest(id, name) values(1000, 'Julie');
Query OK, 1 row affected (0.07 sec)

mysql> select * from idtest;
+------+-------+
| id   | name  |
+------+-------+
|    1 | John  |
|    3 | Tom   |
| 1000 | Julie |
+------+-------+
3 rows in set (0.00 sec)

id를 임의의 값으로 지정하고 저장하니 잘 저장되었다.

이 상태에서, auto_increment를 활용하여 저장해 보자.

mysql> insert into idtest(name) values('June');
Query OK, 1 row affected (0.06 sec)

mysql> select * from idtest;
+------+-------+
| id   | name  |
+------+-------+
|    1 | John  |
|    3 | Tom   |
| 1000 | Julie |
| 1001 | June  |
+------+-------+
4 rows in set (0.01 sec)

임의로 지정한 값보다 큰 값으로 값이 점프했다.

id가 1000 이상인 데이터를 삭제하고 다시 데이터를 추가해보자.

mysql> delete from idtest where id >= 1000;
Query OK, 2 rows affected (0.05 sec)

mysql> select * from idtest;
+----+------+
| id | name |
+----+------+
|  1 | John |
|  3 | Tom  |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into idtest(name) values('Sunny');
Query OK, 1 row affected (0.01 sec)

mysql> select * from idtest;
+------+-------+
| id   | name  |
+------+-------+
|    1 | John  |
|    3 | Tom   |
| 1002 | Sunny |
+------+-------+
3 rows in set (0.00 sec)

 

auto_increment된 값을 다시 줄이려면?

테이블의 auto_increment를 줄이려면 아래의 구문을 활용하면 된다.

alter table 테이블명 auto_increment = 원하는 값;

그런데, 초기화를 하고자 하는 값보다 큰 값이 있으면 동작하지 않는다.
아래의 예를 살펴보자.

auto_increment = 4로 설정했다. 그런데, 이 테이블에는 id = 1003의 값이 이미 존재했다.

mysql> alter table idtest auto_increment = 4;
Query OK, 0 rows affected (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into idtest(name) values('Teddy');
Query OK, 1 row affected (0.01 sec)

mysql> select * from idtest;
+------+-------+
| id   | name  |
+------+-------+
|    1 | John  |
|    3 | Tom   |
| 1003 | Sunny |
| 1004 | Teddy |
+------+-------+
4 rows in set (0.00 sec)

이 상태에서 insert를 하면, id = 4가 아니라 1004로 추가된 것을 확인할 수 있다.

이번에는, 설정하고자 하는 값이 현 시점에서의 최대값이 되도록 설정해 보자.
현재 예제에서는 1000이상의 값을 지워준 후, auto_increment = 4로 설정한다.

mysql> delete from idtest where id > 1000;
Query OK, 2 rows affected (0.05 sec)

mysql> alter table idtest auto_increment = 4;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into idtest(name) values('Teddy');
Query OK, 1 row affected (0.01 sec)

mysql> select * from idtest;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  3 | Tom   |
|  4 | Teddy |
+----+-------+
3 rows in set (0.18 sec)

이제 데이터를 추가하면, id = 4로 새로 추가되는 것을 확인할 수 있다.

 

현재 auto_increment 확인하기

그러면, 특정 테이블의 auto_increment 값을 어떻게 확인할 수 있을까?

다음의 방법들을 활용하여, 특정 테이블의 auto_increment 값을 확인할 수 있다.

 

table status로 조회하기

show table status where name = '테이블명'

명령어를 실행하면 해당 테이블의 상태를 확인할 수 있다.

mysql> show table status where name = 'idtest';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| idtest | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |            0 |         0 |              5 | 2021-11-26 19:30:37 | 2021-11-26 19:30:42 | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+

출력 결과 중, auto_increment 값을 조회하면 된다.

 

information_schema를 활용하여 조회하기

select `auto_increment` from information_schema.tables where table_schema = '스키마명' and table_name = '테이블명'

쿼리를 실행하면, 해당 테이블의 auto_increment 값을 조회할 수 있다.

mysql> select `auto_increment` from information_schema.tables where table_schema = 'mydb' and table_name = 'idtest';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              5 |
+----------------+
1 row in set (0.12 sec)

 

last_insert_id() 함수를 활용하여 조회하기

last_insert_id() 값을 사용하여 확인하는 방법도 있다.

mysql> select  last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|             1001 |
+------------------+
1 row in set (0.03 sec)

내가 insert한 값의 마지막 id를 바탕으로, auto_increment를 유추할 수 있다.

 

테이블 스키마로부터 유추하기

show create table 테이블명

명령어를 실행시키면, 결과물 맨 끝부분에 auto_increment의 현재값이 노출된다.

이 값을 참고하여, 현재 값을 확인할 수 있다.

mysql> show create table idtest;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                       |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| idtest | CREATE TABLE `idtest` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

첨언

위의 예를 그대로 따라했다면 같은 DB 테이블을 두고도, 각각 출력되는 값이 조금씩 다른 것을 확인할 수 있다.

정상적인 상태에서 하나씩 insert를 했다면, 모두 같은 값을 가리켰을 것이다. (last_insert_id는 1이 작은 수)

통계데이터가 반영되는 시점까지 감안하여, 위 방법들 중 본인에게 적절한 방식으로 auto_increment 값을 확인하면 좋을 것이다.