mysqlのauto incrementを調べてみる

auto incrementは勝手に連番を振ってくれる機能。 これがあれば、採番テーブルはいらない？

作成するためには、create tableするときにauto incrementをつけてあげればOK。

mysql> create table test (
    -> id int(2) NULL auto_increment,
    -> name varchar(20),
    -> primary key (id)
    -> );

あとはinsertするたびに増えていく。

mysql> insert into test(name) values ('test1');
Query OK, 1 row affected (0.09 sec)

mysql>
mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | test1 |
+----+-------+
1 row in set (0.00 sec)

mysql>
mysql> insert into test(name) values ('test2');
Query OK, 1 row affected (0.10 sec)

mysql>
mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | test1 |
|  2 | test2 |
+----+-------+
2 rows in set (0.00 sec)

auto incrementの次の値はinformation_schemaでみることができる。

mysql> show columns from information_schema.tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       | ★
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

mysql> select AUTO_INCREMENT from information_schema.tables where table_name='test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

alterコマンドでauto incrementの次の値を変更可能

mysql> ALTER TABLE test AUTO_INCREMENT=10;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> select AUTO_INCREMENT from information_schema.tables where table_name='test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|             10 |
+----------------+
1 row in set (0.00 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | test1 |
|  2 | test2 |
+----+-------+
2 rows in set (0.00 sec)

mysql>
mysql> insert into test(name) values ('test3');
Query OK, 1 row affected (0.10 sec)

mysql>
mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | test1 |
|  2 | test2 |
| 10 | test3 |
+----+-------+
3 rows in set (0.00 sec)

auto incrementの値におかしな設定は入れることができない。

mysql> ALTER TABLE test AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> select AUTO_INCREMENT from information_schema.tables where table_name='test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|             11 |
+----------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> insert into test(name) values ('test4');
Query OK, 1 row affected (0.13 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | test1 |
|  2 | test2 |
| 10 | test3 |
| 11 | test4 |
+----+-------+
4 rows in set (0.00 sec)

mysql>
mysql> ALTER TABLE test AUTO_INCREMENT=5;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select AUTO_INCREMENT from information_schema.tables where table_name='test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|             12 |
+----------------+
1 row in set (0.00 sec)

mysql> insert into test(name) values ('test5');
Query OK, 1 row affected (0.09 sec)

mysql>
mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | test1 |
|  2 | test2 |
| 10 | test3 |
| 11 | test4 |
| 12 | test5 |
+----+-------+
5 rows in set (0.00 sec)