Mysql auto_increment

auto_increment의 사용

auto_increment는 새로운 행(row)에 unique한 값을 생성하도록 하는데 유용하게 쓰인다.

【예제】

mysql> create table animals (

-> id mediumint not null auto_increment,

-> name char(30) not null,

-> primary key (id));

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into animals (name) values

-> ('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich');

Query OK, 6 rows affected (0.00 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)

 

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

| 1 |

+------------------+

1 row in set (0.00 sec)

 

mysql>

AUTO_INCREMENT에 의해서 마지막에 넣은 값은 last_insert_id() 함수나 mysql_insert_id() API 함수를 사용해서 가장 최근에 넣어진 값을 확인할 수 있다.
여러 행을 동시에 입력한 경우에는 last_insert_id()와 mysql_insert_id() 함수를 사용해서 삽입된 첫 행의 auto_increment 값을 반환한다.

위 예에서 보는 바와 같이 하나의 컬럼을 인덱스로 사용하는 경우(이 경우는 primary key로 선언된 id 컬럼이 인덱스가 자동으로 지정됨), auto_increment는 순차적으로 증가 된다.

그러나 다음의 예에서 보는 바와 같이 두개 이상의 컬럼을 인덱스로 지정하여 두번째 인덱스를 auto_increment로 지정하여 그룹별로 저장하는 경우의 auto_increment는 그룹별로 증가된다.

【예제】

mysql> 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));

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into animals (grp,name) values

-> ('mammal','dog'),('mammal','cat'),('bird','penguin'),('fish','lax'),('mammal','whale'),('bird','ostrich');

Query OK, 6 rows affected (0.00 sec)

Records: 6 Duplicates: 0 Warnings: 0

 

mysql> select * from animals order by grp,id;

+--------+----+---------+

| grp | id | name |

+--------+----+---------+

| fish | 1 | lax |

| mammal | 1 | dog |

| mammal | 2 | cat |

| mammal | 3 | whale |

| bird | 1 | penguin |

| bird | 2 | ostrich |

+--------+----+---------+

6 rows in set (0.00 sec)

 

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

| 1 |

+------------------+

1 row in set (0.00 sec)

 

mysql> show index from animals;

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| animals | 0 | PRIMARY | 1 | grp | A | NULL | NULL | NULL | | BTREE | |

| animals | 0 | PRIMARY | 2 | id | A | 6 | NULL | NULL | | BTREE | |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set (0.00 sec)

 

mysql>

여기서 보는 바와 같이 MyISAM, BDB 테이블에서 다중컬럼 인덱스를 지정하면, AUTO_INCREMENT은 MAX(auto_increment_column) + 1 WHERE prefix=given-prefix가 된다.
그러므로 이러한 방법은 ordered group에 데이터를 넣을때 유용하다.

위 예에서 들어간 데이터 순서와 실제 배정된 id를 표에 나타내면 다음과 같다.

grp	데이터  들어간 순서	name	mammal 그룹의 id	fish 그룹의 id	bird 그룹의 id
mammal	1	dog	1
mammal	2	cat	2
bird	3	penguin			1
fish	4	lax		1
mammal	5	whale	3
bird	6	ostrich			2

그런데 PRIMARY KEY(grp,id)이고 INDEX (id)처럼 선언하는 경우, PRIMARY KEY 에 의해서 주어지는 시퀀스 번호는 무시된다. 다음의 예를 보자.

【예제】

mysql> 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),

-> INDEX (id));

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into animals (grp,name) values

-> ('mammal','dog'),('mammal','cat'),('bird','penguin'),('fish','lax'),('mammal','whale'),('bird','ostrich');

Query OK, 6 rows affected (0.00 sec)

Records: 6 Duplicates: 0 Warnings: 0

 

mysql> select * from animals order by grp,id;

+--------+----+---------+

| grp | id | name |

+--------+----+---------+

| fish | 4 | lax |

| mammal | 1 | dog |

| mammal | 2 | cat |

| mammal | 5 | whale |

| bird | 3 | penguin |

| bird | 6 | ostrich |

+--------+----+---------+

6 rows in set (0.00 sec)

 

mysql> select * from animals;

+--------+----+---------+

| grp | id | name |

+--------+----+---------+

| mammal | 1 | dog |

| mammal | 2 | cat |

| bird | 3 | penguin |

| fish | 4 | lax |

| mammal | 5 | whale |

| bird | 6 | ostrich |

+--------+----+---------+

6 rows in set (0.00 sec)

 

mysql> show index from animals;

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| animals | 0 | PRIMARY | 1 | grp | A | NULL | NULL | NULL | | BTREE | |

| animals | 0 | PRIMARY | 2 | id | A | 6 | NULL | NULL | | BTREE | |

| animals | 1 | id | 1 | id | A | NULL | NULL | NULL | | BTREE | |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

3 rows in set (0.00 sec)

 

mysql>

AUTO_INCREMENT에 의해서 증가가 1보다 큰 값으로 시작하려면 CREATE TABLE 문이나 ALTER TABLE 문을 사용해서 다음과 같이 하면 된다.

mysql> ALTER TABLE table_name AUTO_INCREMENT = 100;

【예제】

mysql> create table test (id int not null auto_increment,

-> name char(10),

-> primary key(id)

-> ) auto_increment=10;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test (name) values ('arirang'),('COREA');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

 

mysql> select * from test;

+----+---------+

| id | name |

+----+---------+

| 10 | arirang |

| 11 | COREA |

+----+---------+

2 rows in set (0.00 sec)

 

mysql> alter table test auto_increment=20;

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

 

mysql> insert into test (name) values ('Seoul'),('Inchon');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

 

mysql> select * from test;

+----+---------+

| id | name |

+----+---------+

| 10 | arirang |

| 11 | COREA |

| 20 | Seoul |

| 21 | Inchon |

+----+---------+

4 rows in set (0.00 sec)

 

mysql>

또한 자동 증가분의 디폴트 1을 변경하여 증가분을 지정할 수 있다.

【예제】

mysql> show variables like 'auto_increment%';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| auto_increment_increment | 1 |

| auto_increment_offset | 1 |

+--------------------------+-------+

2 rows in set (0.00 sec)

 

mysql> set auto_increment_increment=2;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show variables like 'auto_increment%';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| auto_increment_increment | 2 |

| auto_increment_offset | 1 |

+--------------------------+-------+

2 rows in set (0.00 sec)

 

mysql> insert into test (name) values('kunsan'),('arirang');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

 

mysql> select * from test;

+----+---------+

| id | name |

+----+---------+

| 10 | arirang |

| 11 | COREA |

| 20 | Seoul |

| 21 | Inchon |

| 23 | kunsan |

| 25 | arirang |

+----+---------+

6 rows in set (0.00 sec)

 

mysql>

 

출처 - http://radiocom.kunsan.ac.kr/lecture/mysql/auto_increment.html