5.6.9 Using AUTO_INCREMENT

5.6.9 Using AUTO_INCREMENT

AUTO_INCREMENT attribute는 새 row에 대해 고유한 아이덴티티를 생성하는 데 사용할 수 있습니다:

1CREATE TABLE animals (
2     id MEDIUMINT NOT NULL AUTO_INCREMENT,
3     name CHAR(30) NOT NULL,
4     PRIMARY KEY (id)
5);
6
7INSERT INTO animals (name) VALUES
8    ('dog'),('cat'),('penguin'),
9    ('lax'),('whale'),('ostrich');
10
11SELECT * FROM animals;

이는 다음을 반환합니다:

1+----+---------+
2| id | name    |
3+----+---------+
4|  1 | dog     |
5|  2 | cat     |
6|  3 | penguin |
7|  4 | lax     |
8|  5 | whale   |
9|  6 | ostrich |
10+----+---------+

AUTO_INCREMENT column에 대해 값이 지정되지 않았으므로, MySQL은 자동으로 시퀀스 번호를 할당했습니다. 또한 NO_AUTO_VALUE_ON_ZERO SQL mode가 활성화되어 있지 않은 한, 시퀀스 번호를 생성하기 위해 column에 0을 명시적으로 할당할 수도 있습니다. 예를 들면:

1INSERT INTO animals (id,name) VALUES(0,'groundhog');

column이 NOT NULL로 선언된 경우, 시퀀스 번호를 생성하기 위해 column에 NULL을 할당하는 것도 가능합니다. 예를 들면:

1INSERT INTO animals (id,name) VALUES(NULL,'squirrel');

다른 어떤 값을 AUTO_INCREMENT column에 insert할 때, column은 해당 값으로 설정되며 시퀀스는 리셋되어, 다음에 자동으로 생성되는 값이 가장 큰 column 값에 이어서 순차적으로 할당됩니다. 예를 들면:

1INSERT INTO animals (id,name) VALUES(100,'rabbit');
2INSERT INTO animals (id,name) VALUES(NULL,'mouse');
3SELECT * FROM animals;
4+-----+-----------+
5| id  | name      |
6+-----+-----------+
7|   1 | dog       |
8|   2 | cat       |
9|   3 | penguin   |
10|   4 | lax       |
11|   5 | whale     |
12|   6 | ostrich   |
13|   7 | groundhog |
14|   8 | squirrel  |
15| 100 | rabbit    |
16| 101 | mouse     |
17+-----+-----------+

기존의 AUTO_INCREMENT column 값을 update하는 경우에도 AUTO_INCREMENT 시퀀스가 리셋됩니다.

가장 최근에 자동으로 생성된 AUTO_INCREMENT 값을 조회하려면, LAST_INSERT_ID() SQL 함수나 mysql_insert_id() C API 함수를 사용할 수 있습니다. 이들 함수는 커넥션별이므로, insert 작업을 수행하고 있는 다른 커넥션에 의해 반환 값이 영향을 받지 않습니다.

필요로 하는 최대 시퀀스 값을 저장할 수 있을 정도로 충분히 크면서도 가능한 한 가장 작은 정수 데이터 타입을 AUTO_INCREMENT column에 사용하십시오. column이 데이터 타입의 상한에 도달하면, 시퀀스 번호를 생성하기 위한 다음 시도는 실패합니다.

가능하다면 UNSIGNED attribute를 사용하여 더 넓은 범위를 허용하십시오. 예를 들어 TINYINT를 사용하는 경우 허용되는 최대 시퀀스 번호는 127입니다. TINYINT UNSIGNED의 경우 최대값은 255입니다.

모든 정수 타입의 범위에 대해서는
Section 13.1.2, “Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT”를 참조하십시오.

참고

multiple-row insert의 경우, LAST_INSERT_ID()와 mysql_insert_id()는 실제로 insert된 row 중 첫 번째 row의 AUTO_INCREMENT key를 반환합니다. 이는 replication 구성이 된 다른 server에서 multiple-row insert를 올바르게 재현할 수 있도록 해 줍니다.

1이 아닌 다른 값에서 AUTO_INCREMENT를 시작하려면, 다음과 같이 CREATE TABLE 또는 ALTER TABLE을 사용하여 해당 값을 설정하십시오:

1mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

InnoDB Notes

InnoDB에 특화된 AUTO_INCREMENT 사용 정보는
Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”를 참조하십시오.

MyISAM Notes

• MyISAM table의 경우, multiple-column index의 secondary column에 AUTO_INCREMENT를 지정할 수 있습니다. 이 경우, AUTO_INCREMENT column에 대해 생성되는 값은
  MAX(auto_increment_column) + 1 WHERE prefix=given-prefix로 계산됩니다. 이는 데이터를 정렬된 그룹에 저장하고자 할 때 유용합니다.

1CREATE TABLE animals (
2      grp ENUM('fish','mammal','bird') NOT NULL,
3      id MEDIUMINT NOT NULL AUTO_INCREMENT,
4      name CHAR(30) NOT NULL,
5      PRIMARY KEY (grp,id)
6) ENGINE=MyISAM;
7
8INSERT INTO animals (grp,name) VALUES
9      ('mammal','dog'),('mammal','cat'),
10      ('bird','penguin'),('fish','lax'),('mammal','whale'),
11      ('bird','ostrich');
12
13SELECT * FROM animals ORDER BY grp,id;

이는 다음을 반환합니다:

1+--------+----+---------+
2| grp    | id | name    |
3+--------+----+---------+
4| fish   |  1 | lax     |
5| mammal |  1 | dog     |
6| mammal |  2 | cat     |
7| mammal |  3 | whale   |
8| bird   |  1 | penguin |
9| bird   |  2 | ostrich |
10+--------+----+---------+

이 경우 (AUTO_INCREMENT column이 multiple-column index의 일부인 경우), 어떤 그룹에서든 가장 큰 AUTO_INCREMENT 값을 가진 row를 delete하면 AUTO_INCREMENT 값이 재사용됩니다. 이는 AUTO_INCREMENT 값이 일반적으로 재사용되지 않는 MyISAM table에서도 발생합니다.

• AUTO_INCREMENT column이 여러 index의 일부인 경우, MySQL은 존재한다면 AUTO_INCREMENT column으로 시작하는 index를 사용하여 시퀀스 값을 생성합니다. 예를 들어, animals table에 PRIMARY KEY (grp, id)와 INDEX (id) index가 모두 포함되어 있다면, MySQL은 시퀀스 값을 생성할 때 PRIMARY KEY를 무시합니다. 그 결과, table에는 grp 값별 시퀀스가 아니라 단일 시퀀스만 존재하게 됩니다.

Further Reading

AUTO_INCREMENT에 대한 추가 정보는 다음에서 확인할 수 있습니다:

• column에 AUTO_INCREMENT attribute를 할당하는 방법:
  Section 15.1.24, “CREATE TABLE Statement” 및
  Section 15.1.11, “ALTER TABLE Statement”.

• SQL mode NO_AUTO_VALUE_ON_ZERO에 따른 AUTO_INCREMENT 동작 방식:
  Section 7.1.11, “Server SQL Modes”.

• 가장 최근의 AUTO_INCREMENT 값을 포함하는 row를 찾기 위해 LAST_INSERT_ID() 함수를 사용하는 방법:
  Section 14.15, “Information Functions”.

• 사용될 AUTO_INCREMENT 값을 설정하는 방법:
  Section 7.1.8, “Server System Variables”.

• Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”

• AUTO_INCREMENT와 replication:
  Section 19.5.1.1, “Replication and AUTO_INCREMENT”.

• replication에 사용할 수 있는, AUTO_INCREMENT와 관련된 server-system variable (auto_increment_increment와 auto_increment_offset):
  Section 7.1.8, “Server System Variables”.