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
