Help us understand the problem. What is going on with this article?

MySQLのAUTO_INCREMENTについて色々と調べてみた。

More than 3 years have passed since last update.

AUTO_INCREMENTとは

  • カラムに値が指定されなかった場合、MySQLが自動的に値を割り当てる。
  • データ型は整数。
  • 値は1ずつ増加して連番になる。

基本的なこと。

テストテーブル作成

mysql> CREATE TABLE fruit(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(50), price INT);
mysql> DESC fruit;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| price | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

ID, 名前, 価格の3カラムを持つfruitテーブルで実験。
バージョンは5.7です。

IDを指定しないで挿入する

mysql> INSERT INTO fruit (name, price) VALUES ("apple", 120), ("pineapple", 200);
mysql> SELECT * FROM fruit;
+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  1 | apple     |   120 |
|  2 | pineapple |   200 |
+----+-----------+-------+
2 rows in set (0.00 sec)

指定しなくても自動的にID1,2が割り当てられる。

IDを欠番にすると

ID=15のレコードを挿入して3~14を欠番にする。

mysql> INSERT INTO fruit VALUES(15, "grape", 300);
mysql> SELECT * FROM fruit;                                                  
+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  1 | apple     |   120 |
|  2 | pineapple |   200 |
| 15 | grape     |   300 |
+----+-----------+-------+
3 rows in set (0.00 sec)

この後にIDを指定しないで挿入すると

mysql> INSERT INTO fruit SET name="melon", price=1500;
mysql> SELECT * FROM fruit;
+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  1 | apple     |   120 |
|  2 | pineapple |   200 |
| 15 | grape     |   300 |
| 16 | melon     |  1500 |
+----+-----------+-------+
4 rows in set (0.00 sec)

欠番を飛ばして最大値からまた連番になる。

LAST_INSERT_ID( )

SQLの関数。

MySQLは内部的に最後にAUTO_INCREMENTで挿入したレコードのIDを保持している。
自分でIDを指定した場合、この値は上書かれない。
そして複数行同時に挿入した時は最初に挿入されたレコードのIDを返すという仕様。

ID=15のレコードを挿入した時点でこの関数を発行すると

mysql> SELECT LAST_INSERT_ID() FROM fruit;
+------------------+
| last_insert_id() |
+------------------+
|                1 |
|                1 |
|                1 |
+------------------+
3 rows in set (0.00 sec)

ID=1とID=2は同時に挿入しているし、ID=15はAUTO_INCREMENTで割り当てられた値ではないので、結果1が返ってくる。

ID=16を挿入した後は

mysql> SELECT LAST_INSERT_ID() FROM fruit;
+------------------+
| last_insert_id() |
+------------------+
|               16 |
|               16 |
|               16 |
|               16 |
+------------------+
4 rows in set (0.00 sec)

こうなる。

AUTO_INCREMENTで次に割り当てられる値

mysql> SHOW TABLE STATUS WHERE name = "fruit"\G
*************************** 1. row ***************************
           Name: fruit
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 17
    Create_time: 2016-12-24 02:38:05
    Update_time: 2016-12-24 02:48:47
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

これが次に割り当てられる値

Auto_increment: 17

LAST_INSERT_IDと違ってこの値は自分でIDを指定して挿入した時にも更新される。

エラーが起きた時の欠番

名前にUNIQUE属性を付ける。

mysql> ALTER TABLE fruit ADD UNIQUE(name);

現在のテーブルの状態

+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  1 | apple     |   120 |
|  2 | pineapple |   200 |
| 15 | grape     |   300 |
| 16 | melon     |  1500 |
+----+-----------+-------+

以下のクエリを発行

mysql> INSERT INTO fruit(name, price) VALUES("apple", 100), ("peach", 200);

ERROR 1062 (23000): Duplicate entry 'apple' for key 'name'

重複エラーでINSERTされない。
次に割り当てられる値を見てみると

Auto_increment: 19

テーブルの状態は

mysql> SELECT * FROM fruit;
+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  1 | apple     |   120 |
|  2 | pineapple |   200 |
| 15 | grape     |   300 |
| 16 | melon     |  1500 |
+----+-----------+-------+
4 rows in set (0.00 sec)

エラーでレコードが挿入できなくてもAUTO_INCREMENTの値は挿入しようとしたレコードの分だけ増え続けていく。次にレコードを挿入した時には欠番が発生することになる。これはInnoDBの仕様らしい。

mysql> INSERT INTO fruit(name, price) VALUES("peach", 200);
mysql> SELECT * FROM fruit;
+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  1 | apple     |   120 |
|  2 | pineapple |   200 |
| 15 | grape     |   300 |
| 16 | melon     |  1500 |
| 19 | peach     |   200 |
+----+-----------+-------+
5 rows in set (0.00 sec)

ちなみに挿入エラーが起きてもLAST_INSERT_ID( )で返される値は影響を受けない。先ほどの Auto_increment: 19 の時点で LAST_INSERT_ID( ) は16を返す。

最大値に達した時

テストテーブルのIDカラムはsigned int(11)なので最大値は2147483647。

ID=2147483647のレコードを挿入する。

mysql> INSERT INTO fruit(id, name, price) VALUES(2147483647, "orange", 500); 

SHOW TABLE STATUSで次に割り当てられる値を確認すると

Auto_increment: 2147483647

さらにレコードを挿入しようとしてもPRIMARY KEYの重複エラーが出る。

mysql> INSERT INTO fruit(name, price) VALUES("kiwi", 110);

ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

PRIMARY KEYを外してやってみたらどうか。

mysql> ALTER TABLE fruit DROP PRIMARY KEY;

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

AUTO_INCREMENTを設定したカラムはPRIMARY KEYでないといけないらしい。

最大値に達したらもうそれ以上レコードは挿入できない。
のでunsignedとかBIGINTを使って上限を増やしておくと安心。

ID=0は入るか

試す。

テーブルを1回消してこの状態まで戻す。

+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  1 | apple     |   120 |
|  2 | pineapple |   200 |
| 15 | grape     |   300 |
| 16 | melon     |  1500 |
+----+-----------+-------+

ID=0を指定して挿入

mysql> INSERT INTO fruit VALUES(0, "banana", 180);
mysql> SELECT * FROM fruit;

+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  1 | apple     |   120 |
|  2 | pineapple |   200 |
| 15 | grape     |   300 |
| 16 | melon     |  1500 |
| 17 | banana    |   180 |
+----+-----------+-------+
5 rows in set (0.00 sec)

0指定したのに連番振られてる。

これはAUTO_INCREMENTが対象のカラムに0またはNULLが指定された時に自動で値を割り振る仕組みのため。

以下の設定をすれば0に対してこの仕組みが適用されないようにできる。

mysql> SET GLOBAL sql_mode = NO_AUTO_VALUE_ON_ZERO;

ただAUTO_INCREMENTを設定したカラムで0を使うのは推奨されてません。

-100から始まる自動採番

まっさらなテーブルを再度用意。
ID=-100のレコードを入れる。

mysql> INSERT INTO fruit VALUES(-100, "apple", 120);
mysql> SELECT * FROM fruit;
+------+-------+-------+
| id   | name  | price |
+------+-------+-------+
| -100 | apple |   120 |
+------+-------+-------+
1 row in set (0.00 sec)

次に採番される番号は

mysql> SHOW TABLE STATUS WHERE name = "fruit"¥G;

Auto_increment: 1
mysql> INSERT INTO fruit(name, price) VALUES("pineapple", 300);
mysql> SELECT * FROM fruit;
+------+-----------+-------+
| id   | name      | price |
+------+-----------+-------+
| -100 | apple     |   120 |
|    1 | pineapple |   300 |
+------+-----------+-------+
2 rows in set (0.00 sec)

負の数はバッサリ飛ばされました。

2ずつとか増やしたい

以下のクエリを発行して設定値を確認。

mysql> SHOW variables LIKE "%auto_increment%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

auto_increment_increment は増加する数。
auto_increment_offset は初期値。

2ずつ増やしてみよう。

mysql> SET SESSION auto_increment_increment = 2;
mysql> INSERT INTO fruit(name, price) VALUES("apple", 120), ("pineapple", 300), ("orange", 150);
mysql> SELECT * FROM fruit;
+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  1 | apple     |   120 |
|  3 | pineapple |   300 |
|  5 | orange    |   150 |
+----+-----------+-------+
3 rows in set (0.00 sec)

10001から採番を始めたい

今度はauto_increment_offsetの設定値をいじる。
今回はテーブル作成時に設定する方法を使う。

mysql> CREATE TABLE fruit(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(50) UNIQUE, price INT) AUTO_INCREMENT=10001;
mysql> INSERT INTO fruit(name, price) VALUES("apple", 120), ("pineapple", 300), ("orange", 150);
mysql> SELECT * FROM fruit;
+-------+-----------+-------+
| id    | name      | price |
+-------+-----------+-------+
| 10001 | apple     |   120 |
| 10002 | pineapple |   300 |
| 10003 | orange    |   150 |
+-------+-----------+-------+
3 rows in set (0.00 sec)

auto_increment_incrementauto_increment_offsetに負の値は設定できなかった。クエリは通っても値は1のままだった。incrementだから当たり前っちゃ当たり前だけど。

0埋めしたい

すればいいと思う。普通に。

mysql> CREATE TABLE fruit(id INT ZEROFILL AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(50) UNIQUE, price INT);
mysql> INSERT INTO fruit(name, price) VALUES("apple", 120), ("pineapple", 300), ("orange", 150);
mysql> SELECT * FROM fruit;
+------------+-----------+-------+
| id         | name      | price |
+------------+-----------+-------+
| 0000000001 | apple     |   120 |
| 0000000002 | pineapple |   300 |
| 0000000003 | orange    |   150 |
+------------+-----------+-------+
3 rows in set (0.00 sec)

以上です。ご査収ください。

sakuraya
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away