MySQL - AUTO_ICREMENT

AUTO_INCREMENT 사용

속성 AUTO_INCREMENT은 행에 대한 고유한 식별자를 생성하는 데 사용할 수 있습니다.

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

다음을 반환합니다.

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

AUTO_INCREMENT 열에 지정된 값이 없으므로 MySQL이 자동으로 시퀀스 번호를 할당합니다.

NO_AUTO_VALUE_ON_ZERO SQL 모드가 활성화되지 않은 경우 열에 명시적으로 0을 할당하여 시퀀스 번호를 생성할 수 있습니다 . 예를 들어:

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

컬럼이 NOT NULL로 선언된 경우에도 NULL을 컬럼에 할당하여 시퀀스 번호를 생성할 수도 있습니다.

예를 들어:

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

AUTO_INCREMENT 컬럼에 다른 값을 삽입하면 컬럼이 해당 값으로 설정되고 다음 자동 생성 값은 가장 큰 컬럼값부터 나오도록 시퀀스가 ​​설정됩니다. 예를 들어:

INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');
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     |
+-----+-----------+

기존 AUTO_INCREMENT 컬럼값을 업데이트하면 AUTO_INCREMENT 시퀀스도 재설정됩니다.

AUTO_INCREMENT 초기값을 1이 아닌 값으로 시작하려면 다음과 같이 CREATE TABLE또는 ALTER TABLE로 해당 값을 설정합니다.

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

 

 

MyISAM 노트

MyISAM 테이블의 경우 복합 인덱스의 첫번째 컬럼이 아닌 보조컬럼으로 지정할 수 있습니다 . 이 경우 AUTO_INCREMENT 컬럼에 대해 생성된 값은 MAX(auto_increment_column) + 1 WHERE prefix=given-prefix 으로 계산됩니다 . 데이터를 정렬된 그룹에 넣을 때 유용합니다. 

 

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

아래와 같이 grp컬럼별로 auto_increment 값이 리턴됩니다.

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

MyISAM 테이블의 경우( AUTO_INCREMENT 컬럼이 복합 인덱스의 일부인 경우) 그룹에서 가장 큰 값의 행을 삭제하면 AUTO_INCREMENT 값이 재사용됩니다. 즉 grp='mamman' 이고 id=3인 데이터를 삭제한 후 grp='mammal' 데이터를 입력하면 id는 다시 3을 재사용합니다.

 

 

AUTO_INCREMENT 컬럼이 선두컬럼인 인덱스가 있는 경우 MySQL은 단일 시퀀스 값을 생성합니다. 예를 들어, animals테이블에 인덱스 PRIMARY KEY (grp, id) 및 INDEX (id)가 있는 경우 MySQL은 grp별 시퀀스가 ​​아니라 단일 시퀀스를 생성합니다.

 

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