1. sakuraya

    Posted

    sakuraya
Changes in title
+MySQLのAUTO_INCREMENTについて色々と調べてみた。
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,415 @@
+# AUTO_INCREMENTとは
+- カラムに値が指定されなかった場合、MySQLが自動的に値を割り当てる。
+- データ型は整数。
+- 値は1ずつ増加して連番になる。
+
+基本的なこと。
+
+# テストテーブル作成
+
+```sql
+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を指定しないで挿入する
+
+```sql
+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を欠番にする。
+
+```sql
+mysql> INSERT INTO fruitVALUES(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を指定しないで挿入すると
+
+```sql
+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のレコードを挿入した時点でこの関数を発行すると
+
+```sql
+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を挿入した後は
+
+```sql
+mysql> SELECT LAST_INSERT_ID() FROM fruit;
++------------------+
+| last_insert_id() |
++------------------+
+| 16 |
+| 16 |
+| 16 |
+| 16 |
++------------------+
+4 rows in set (0.00 sec)
+```
+
+こうなる。
+
+# AUTO_INCREMENTで次に割り当てられる値
+
+```sql
+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)
+```
+これが次に割り当てられる値
+
+```sql
+Auto_increment: 17
+```
+
+LAST_INSERT_IDと違ってこの値は自分でIDを指定して挿入した時にも更新される。
+
+# エラーが起きた時の欠番
+
+名前にUNIQUE属性を付ける。
+
+```sql
+mysql> ALTER TABLE fruit ADD UNIQUE(name);
+```
+
+現在のテーブルの状態
+
+```sql
++----+-----------+-------+
+| id | name | price |
++----+-----------+-------+
+| 1 | apple | 120 |
+| 2 | pineapple | 200 |
+| 15 | grape | 300 |
+| 16 | melon | 1500 |
++----+-----------+-------+
+```
+
+以下のクエリを発行
+
+```sql
+mysql> INSERT INTO fruit(name, price) VALUES("apple", 100), ("peach", 200);
+
+ERROR 1062 (23000): Duplicate entry 'apple' for key 'name'
+```
+
+重複エラーでINSERTされない。
+次に割り当てられる値を見てみると
+
+```sql
+Auto_increment: 19
+```
+
+テーブルの状態は
+
+```sql
+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の仕様らしい。
+
+```sql
+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のレコードを挿入する。
+
+```sql
+mysql> INSERT INTO fruit(id, name, price) VALUES(2147483647, "orange", 500);
+```
+
+`SHOW TABLE STATUS`で次に割り当てられる値を確認すると
+
+```sql
+Auto_increment: 2147483647
+```
+
+さらにレコードを挿入しようとしてもPRIMARY KEYの重複エラーが出る。
+
+```sql
+mysql> INSERT INTO fruit(name, price) VALUES("kiwi", 110);
+
+ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
+```
+
+PRIMARY KEYを外してやってみたらどうか。
+
+```sql
+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回消してこの状態まで戻す。
+
+```sql
++----+-----------+-------+
+| id | name | price |
++----+-----------+-------+
+| 1 | apple | 120 |
+| 2 | pineapple | 200 |
+| 15 | grape | 300 |
+| 16 | melon | 1500 |
++----+-----------+-------+
+```
+
+ID=0を指定して挿入
+
+```sql
+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に対してこの仕組みが適用されないようにできる。
+
+```sql
+mysql> SET GLOBAL sql_mode = NO_AUTO_VALUE_ON_ZERO;
+```
+
+ただAUTO_INCREMENTを設定したカラムで0を使うのは推奨されてません。
+
+# -100から始まる自動採番
+
+まっさらなテーブルを再度用意。
+ID=-100のレコードを入れる。
+
+```sql
+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)
+```
+
+次に採番される番号は
+
+```sql
+mysql> SHOW TABLE STATUS WHERE name = "fruit"¥G;
+
+Auto_increment: 1
+```
+
+```sql
+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ずつとか増やしたい
+
+以下のクエリを発行して設定値を確認。
+
+```sql
+
+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ずつ増やしてみよう。
+
+```sql
+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`の設定値をいじる。
+今回はテーブル作成時に設定する方法を使う。
+
+```sql
+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埋めしたい
+
+すればいいと思う。普通に。
+
+```sql
+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)
+```
+
+以上です。ご査収ください。