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_incrementとauto_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)
以上です。ご査収ください。