MySQL에서 테이블 컬럼에 AUTO_INCREMENT을 설정 하는 경우가 많습니다.
AUTO_INCREMENT는 값을 지정하지 않아도 AUTO_INCREMENT을 설정한 컬럼의 값을 +1씩 순차적으로 값을 증가 시켜 등록합니다.
테이블의 기본키 또는 유니크키에 설정하는 경우가 많습니다.
AUTO_INCREMENT을 지정한 테이블에서 현재 AUTO_INCREMENT값이 어떠한지 알고 싶은 경우가 있습니다.
현재의 AUTO_INCREMENT 값과 AUTO_INCREMENT를 리셋하는 방법을 알아보겠습니다.
AUTO_INCREMENT 값 확인
테이블에 설정한 AUTO_INCREMENT의 현재 값을 확인하는 방법을 알아보겠습니다.
현재 값을 확인하기 위해서는 INFORMATION_SCHEMA.TABLES 정보에서 AUTO_INCREMENT을 확인하면 됩니다.
ANALYZE TABLE 테이블명;
SELECT AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘데이터 베이스명’
AND TABLE_NAME = ‘테이블명’;
AUTO_INCREMENT 확인하는 쿼리를 실행하기 전에 테이블의 최신 정보를 취득할 수 있도록 ANALYZE TABLE을 먼저 실행합니다.
ANALYZE TABLE실행 하지 않으면 정확한 정보를 취득하지 못할 수도 있습니다.
샘플 데이터를 이용해 AUTO_INCREMENT 현재 값을 취득해보겠습니다.
mysql> ANALYZE TABLE Member;
+-----------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| test_databases.Member | analyze | status | OK |
+-----------------------+---------+----------+----------+
1 row in set (0.15 sec)
mysql> SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test_databases' AND TABLE_NAME = 'Member';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 10 |
+----------------+
1 row in set (0.00 sec)
현재 AUTO_INCREMENT값을 취득되었습니다.
설정 되어 있는 값은 10입니다.
Member 테이블에 새로운 데이터가 등록될때 사용될 값이 10이 들어간다는 의미가 됩니다.
AUTO_INCREMENT 초기화
AUTO_INCREMENT값을 초기화 하는 방법을 알아보겠습니다.
값을 완전히 지우는 것이 아닌 AUTO_INCREMENT를 설정한 테이블에 최대값으로 변경하는 작업이 됩니다.
샘플 데이터를 보겠습니다.
mysql> SELECT * FROM Member;
+----+----------+-------+------+--------+---------+
| id | Memberid | name | age | gender | company |
+----+----------+-------+------+--------+---------+
| 1 | 1 | tom | 24 | F | A |
| 2 | 2 | cat | 36 | F | A |
| 3 | 3 | work | 29 | M | C |
| 4 | 4 | bench | 45 | F | A |
| 5 | 5 | post | 59 | F | B |
| 6 | 6 | gre | 21 | M | C |
| 7 | 8 | php | 23 | F | NULL |
| 8 | 9 | da | 61 | M | NULL |
| 9 | 7 | jang | 36 | M | E |
+----+----------+-------+------+--------+---------+
9 rows in set (0.00 sec)
Member테이블의 id컬럼에 AUTO_INCREMENT설정이 되어있습니다.
Member테이블의 id컬럼의 값은 9까지 등록 되어 있습니다.
Member테이블의 id컬럼에 설정한 AUTO_INCREMENT값을 확인해보겠습니다.
mysql> SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test_databases' AND TABLE_NAME = 'Member';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 10 |
+----------------+
1 row in set (0.01 sec)
설정 되어 있는 값은 10입니다.
만약 Member테이블의 값을 모두 삭제하거나 일부분을 삭제할 경우 AUTO_INCREMENT값은 그대로 10으로 남아있습니다.
데이터를 삭제후 다시 넣은 데이터의 id는 10으로 들어갑니다.
Member테이블의 id 9데이터를 삭제하고 다시 등록을 해보겠습니다.
mysql> -- 데이터 삭제
mysql> DELETE FROM Member WHERE id = 9;
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> -- 데이터 등록
mysql> INSERT INTO Member (Memberid, name, age, gender, company) VALUES ('7', 'jang', '36', 'M', 'E');
Query OK, 1 row affected (0.03 sec)
새로 등록한 데이터를 확인해보겠습니다.
mysql> SELECT * FROM Member;
+----+----------+-------+------+--------+---------+
| id | Memberid | name | age | gender | company |
+----+----------+-------+------+--------+---------+
| 1 | 1 | tom | 24 | F | A |
| 2 | 2 | cat | 36 | F | A |
| 3 | 3 | work | 29 | M | C |
| 4 | 4 | bench | 45 | F | A |
| 5 | 5 | post | 59 | F | B |
| 6 | 6 | gre | 21 | M | C |
| 7 | 8 | php | 23 | F | NULL |
| 8 | 9 | da | 61 | M | NULL |
| 10 | 7 | jang | 36 | M | E |
+----+----------+-------+------+--------+---------+
9 rows in set (0.01 sec)
새로 추가된 id는 10으로 등록되었습니다.
9는 비어있는 값처럼 보입니다.
만약 비어있는 값 없이 넣고 싶은 경우 또는 테스트를 위해 다시 1부터 넣어야 하는 경우에는 AUTO_INCREMENT를 초기화 해야합니다.
초기화를 하는 방법은 다음과 같습니다.
ALTER TABLE 테이블명 AUTO_INCREMENT = 1;
샘플 테이블에 새로 추가한 id 10값을 삭제하고, AUTO_INCREMENT를 초기화 한 뒤에 새로 데이터를 넣어보겠습니다.
mysql> -- 데이터 삭제
mysql> DELETE FROM Member WHERE id = 10;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> -- AUTO_INCREMENT 초기화
mysql> ALTER TABLE Member AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 데이터 등록
mysql> INSERT INTO Member (Memberid, name, age, gender, company) VALUES ('7', 'jang', '36', 'M', 'E');
Query OK, 1 row affected (0.03 sec)
새로 등록한 데이터를 확인해보겠습니다.
mysql> SELECT * FROM Member;
+----+----------+-------+------+--------+---------+
| id | Memberid | name | age | gender | company |
+----+----------+-------+------+--------+---------+
| 1 | 1 | tom | 24 | F | A |
| 2 | 2 | cat | 36 | F | A |
| 3 | 3 | work | 29 | M | C |
| 4 | 4 | bench | 45 | F | A |
| 5 | 5 | post | 59 | F | B |
| 6 | 6 | gre | 21 | M | C |
| 7 | 8 | php | 23 | F | NULL |
| 8 | 9 | da | 61 | M | NULL |
| 9 | 7 | jang | 36 | M | E |
+----+----------+-------+------+--------+---------+
9 rows in set (0.01 sec)
새로 추가된 id는 9로 등록되었습니다.
ALTER TABLE 테이블명 AUTO_INCREMENT = 1;를 실행 하게되면 AUTO_INCREMENT에는 현재 최대값 + 1으로 초기화가 됩니다.
정리
AUTO_INCREMENT의 현재값과 초기화를 하는 방법을 알아봤습니다.
INFORMATION_SCHEMA.TABLES 정보를 취득해 AUTO_INCREMENT의 현재 값을 알 수 있습니다.
그리고 ALTER TABLE 명령어를 사용해 AUTO_INCREMENT의 값을 초기화 할 수 있습니다.
댓글