MySQL

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

More than 1 year has 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)

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