1. sakuraya

    No comment

    sakuraya
Changes in body
Source | HTML | Preview
@@ -1,415 +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> 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を指定しないで挿入すると
```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)
```
以上です。ご査収ください。