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;
