SELECT+INSERTでAUTO_INCREMENTの値が増える (飛ぶ)

## 概要
MariaDB 10.6.3を使って開発をしていて気になったので、取り敢えずここにまとめる。

## AUTO_INCREMENTの値がおかしい...
SELECTしてINSERTしたとき、idの値が飛んだのでなんだろうと思って調べてみる。

## テスト環境を用意する
test1テーブル(マスタ情報) と test2テーブル を作成する。

**・test1テーブル作成 & データを登録する (4件)**

```sql
CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '名前',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='テスト1';
```
```sql
INSERT INTO `test1` (`name`) VALUES ('太郎'), ('次郎'), ('三郎'), ('四郎');
```
![table-test1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142279/030a9832-8d5a-1cd6-4f10-d8d989a27571.png)

**・test2テーブル作成**

```sql
CREATE TABLE `test2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '名前',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='テスト2';
```
![table-test2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142279/65bbd426-8fd1-46d4-eed7-dd8048b169ab.png)
これで２つのテーブルができました。

## 再現してみる
test2テーブルにtest1のデータをSELECTしてINSERTしてみる。

```sql
INSERT INTO `test2` (`name`) SELECT `test1`.`name` FROM `test1`;
INSERT INTO `test2` (`name`) VALUES ('五郎'), ('六郎');
```
![data-test1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142279/52184109-9528-cf03-28ae-4fb04f12b977.png)
※ 五郎と六郎のIDが飛んでいる...
![ddl-test1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142279/a648c109-4bb7-f19a-dc24-dfe414c20c0a.png)
※ AUTO_INCREMENTは、10にっている...

普通にINSERTしただけなのに、IDが飛んでいるなぜ？

## innodb_autoinc_lock_modeについて
どうやら、ちゃんと連番にしたかったら、「innodb_autoinc_lock_mode = 0」にする必要があるみたい。
しかし、パフォーマンスが悪くなるからデフォルトは「innodb_autoinc_lock_mode = 1」らしい。

確認してみる。

```sql
SELECT @@innodb_autoinc_lock_mode;
```
![innodb_autoinc_lock_mode.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142279/9315642e-32d8-eb00-7bdd-dc88b2128047.png)
デフォルトのまま「innodb_autoinc_lock_mode = 1」のようです。

innodb_autoinc_lock_modeの設定を変更してみる。

```conf:mysql.cnf
[mysqld]
innodb_autoinc_lock_mode=0
```
![innodb_autoinc_lock_mode=0.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142279/a7e64e49-50a1-4a68-916c-5646cfd8f93d.png)

再度、test2テーブルにSELECT INSERTしてみる。

```sql
TRUNCATE `test2`;
INSERT INTO `test2` (`name`) SELECT `test1`.`name` FROM `test1`;
INSERT INTO `test2` (`name`) VALUES ('五郎'), ('六郎');
```
![data-test2-0.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142279/97abd805-148b-56e2-0ed4-9cb3a6f82311.png)
※ 五郎、六郎のIDが正常に連番で登録される
![ddl-test2-0.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142279/eeccaa89-9347-b451-035b-1e635a2a25bf.png)
※ AUTO_INCREMENTも飛ばず、「７」になっている

以前からINSERTでエラーが発生したら、IDが飛ぶのは知っていました。
しかし、SELECT ＋ INSERTしたらIDが飛ぶのは認識していませんでした。

この方法で、IDは飛ばなくなったのですが、パフォーマンスが気になる。
「innodb_autoinc_lock_mode=1」に戻して、他に方法がないか検討してみる。

```conf:mysql.cnf
[mysqld]
innodb_autoinc_lock_mode=1
```
## IDを指定してみる
SELECTしてINSERTする際、IDを指定してみる。

```sql
TRUNCATE `test2`;

BEGIN;
SET @num = -1;
SET @current = (SELECT `auto_increment` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE table_name = 'test2');
INSERT INTO `test2` (`id`, `name`)
SELECT
  @current + (@num := @num + 1) AS `id`,
  `test1`.`name` 
FROM 
  `test1` 
WHERE 
  `name` IS NOT NULL
;
COMMIT;

INSERT INTO `test2` (`name`) VALUES ('五郎'), ('六郎');
```
![data-test2-1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142279/f8e32965-4341-da59-ba41-9936dc0b5d21.png)
※ 五郎、六郎のIDは連番になりました
![ddl-test2-1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/142279/f71749a9-4e4a-1992-23cd-ce029f7acf35.png)
※ AUTO_INCREMENTも正常な値で飛んでいないようです

IDカラム(auto_incrementのカラム)を直接指定したら、発生しないようです。

PostgreSQLのnextval()関数のようなもので事前に取得できればいいのに...
MySQLにはそのような関数は無さそうなので残念...

## INSERT後にAUTO_INCREMENTを更新する
値がおかしくなるのであれば、INSERT後にAUTO_INCREMENTを再設定してみたらどうなるだろうと思い試す。

```sql
TRUNCATE `test2`;

BEGIN;
INSERT INTO `test2` (`name`) SELECT `test1`.`name` FROM `test1`;

SET @s = CONCAT('ALTER TABLE `test2` AUTO_INCREMENT=', (SELECT MAX(id) + 1 FROM `test2`));
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
COMMIT;

INSERT INTO `test2` (`name`) VALUES ('五郎'), ('六郎');
```

結果は、IDを指定したときと同じ結果になりました。

この方法でもIDが飛ぶのを解消はできそう...

## まとめ
IDが飛ばないように試行錯誤してみましたが、あまりいい方法では無い気がする。
どうしてもIDを連番にしてパフォーマンスが落ちてもいいのであれば、素直に「innodb_autoinc_lock_mode=0」に設定するのがいいのかな...

何かいい方法が見つかればいいけど...
連番にすることが必要であれば、要検討ですね。

## 参考サイト
* [【MySQL】AUTO_INCREMENTの値が増える、飛ぶ、欠番が発生する](https://www.softel.co.jp/blogs/tech/archives/2651)
* [【MySQL】INSERT IGNOREでAUTO_INCREMENTの値が飛ぶ問題。連番にする方法](https://loop-never-ends.com/mysql-auto-increment-sequential-id/)
* [第49回　MySQLのAUTO_INCREMENTについて](https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0049)
* [How can I reset an MySQL AutoIncrement using a MAX value from another table?](https://stackoverflow.com/questions/2410689/how-can-i-reset-an-mysql-autoincrement-using-a-max-value-from-another-table)
* [MySQLで連続した値を扱う方法](https://qiita.com/mimitaro/items/a175234fca9992a82533)