MySQL 8.0 Reference Manual - Tutorial - Creating and Using a Database4

3.6.9 Using AUTO_INCREMENT

 

AUTO_INCREMENT 속성은 새로운 row의 값의 유일성을 보장하기 위해 유니크한 값을 생성하는데 사용되어 진다.

 

mysql> CREATE TABLE animals (
    ->      id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->      name CHAR(30) NOT NULL,
    ->      PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO animals (name) VALUES
    ->     ('dog'),('cat'),('penguin'),
    ->     ('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

 

위에 보는 바와 같이 AUTO_INCREMENT 컬럼에 값을 넣지 않아도 MySQL이 자동으로 시퀀스 넘버를 넣어준다.

 

AUTO_INCREMENT 속성의 컬럼에 0 값을 지정할 수 있다.

SQL 모드를 NO_AUTO_VALUE_ON_ZERO로 설정하지 않고 0 값으로 insert 하게 되면 아래와 같은 결과를 확인할 수 있다.

 

mysql> INSERT INTO animals (id,name) VALUES(0,'groundhog');
Query OK, 1 row affected (0.01 sec)
mysql> select * from animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | dog       |
|  2 | cat       |
|  3 | penguin   |
|  4 | lax       |
|  5 | whale     |
|  6 | ostrich   |
|  7 | groundhog |
+----+-----------+
7 rows in set (0.00 sec)

 

즉, 자동으로 AUTO_INCREMENT된 값으로 insert 된다.

 

AUTO_INCREMENT 속성이 걸린 컬럼이 NOT NULL 속성을 가지고 있다면 아래와 같이 입력을 해도 증가된 시퀀스 값이 insert 된다.

 

mysql> INSERT INTO animals (id,name) VALUES(NULL,'squirrel');
Query OK, 1 row affected (0.02 sec)
mysql> select * from animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | dog       |
|  2 | cat       |
|  3 | penguin   |
|  4 | lax       |
|  5 | whale     |
|  6 | ostrich   |
|  7 | groundhog |
|  8 | squirrel  |
+----+-----------+
8 rows in set (0.00 sec)

 

하지만 겹치는 값을 넣게 되면 위 테이블 생성시 primary key를 세팅한 이유로 겹친다고 에러가 발생하게 된다.

 

mysql> INSERT INTO animals (id,name) VALUES(8,'kwan');
ERROR 1062 (23000): Duplicate entry '8' for key 'PRIMARY'
mysql>

 

만약 AUTO_INCREMENT 속성을 가진 컬럼에 primary key 설정을 하지 않는 다면 아래와 같이 에러가 발생한다.

 

mysql> CREATE TABLE animals_test (
    ->      id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->      name CHAR(30) NOT NULL
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>

 

AUTO_INCREMENT 속성의 컬럼에 값을 지정한체 insert 하게 되면 중복되지 않는 한 값이 들어가게 되고 시퀀스는 리셋되며 다음값은 컬럼의 값중 가장 큰 값 다음 값으로 설정이 된다.

 

mysql> INSERT INTO animals (id,name) VALUES(100,'rabbit');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO animals (id,name) VALUES(NULL,'mouse');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+
10 rows in set (0.00 sec)

 

데이터 입력에 AUTO_INCREMENT 속성에 대한 값을 꼬여서 들어가게 해도 위에 언급했던 

원칙대로 데이터가 입력이 된다.

 

mysql> INSERT INTO animals (id,name) VALUES(110,'k');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO animals (id,name) VALUES(NULL,'kk');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
| 110 | k         |
| 111 | kk        |
+-----+-----------+
12 rows in set (0.00 sec)
mysql> INSERT INTO animals (id,name) VALUES(105,'kkk');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO animals (id,name) VALUES(NULL,'kkkk');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
| 105 | kkk       |
| 110 | k         |
| 111 | kk        |
| 112 | kkkk      |
+-----+-----------+
14 rows in set (0.00 sec)

 

update를 통해 값을 변경해도 시퀀스에 사용된 최대 값이 별도로 저장되어 있기 때문에 다음 값을 지정하게 된다.

 

mysql> update animals set id = 106 where name = 'kkkk';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> INSERT INTO animals (id,name) VALUES(NULL,'kkkkk');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
| 105 | kkk       |
| 106 | kkkk      |
| 110 | k         |
| 111 | kk        |
| 113 | kkkkk     |
+-----+-----------+
15 rows in set (0.00 sec)

 

AUTO_INCREMENT 값을 1이 아닌 다른 값으로 설정하려면 아래의 명령어를 통해 변경할 수 있다.

 

ALTER TABLE tbl AUTO_INCREMENT = 100;