[MySQL]ID指定でデータ投入した時、AUTO_INCREMENTの値はどうなる？

どうだったっけなって度々思ってしまうので備忘録として。

確認環境はMySQL 8.0.29 – MySQL Community Server – GPLです。

現在の設定値確認方法
1. テスト用テーブル
2. オートインクリメントの現在値の確認
ID指定でinsertした時にauto_incrementの値は移動する？

現在の設定値確認方法

テスト用テーブル

以下のテーブルをテスト用として用意しました。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `del` tinyint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

オートインクリメントの現在値の確認

-- フォーマット
SELECT TABLE_SCHEMA, TABLE_NAME, AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DB名' AND TABLE_NAME = 'テーブル名';

-- 具体例
SELECT TABLE_SCHEMA, TABLE_NAME, AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'autoincre_test' AND TABLE_NAME = 'user';

注意点があります。

上記のSQLで値は確認できるようなのですが・・・

MySQL8からは結果がキャッシュされるようになっているようです。

僕もこの記事を書く時に結果が更新されないことに悩みました。information_schema_stats_expiryという値がキャッシュ時間に関係しているとのことです。

参考記事：https://yoku0825.blogspot.com/2019/05/mysql-80show-table-status.html

information_schema_stats_expiryで検索すると参考記事が出てきます。

information_schema_stats_expiryを設定せずに結果を確認する方法としてshow create tableを実行する方法があります。

ためしに以下のSQLで1レコード投入すると

INSERT INTO `user` (`id`, `name`, `email`, `del`) VALUES (NULL, 'ユーザ1', 'user1@example.com', '0');

show create table でAUTO_INCREMENTの値を確認することができます。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `del` tinyint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

ID指定でinsertした時にauto_incrementの値は移動する？

すでにデータが入っている状態でAUTO_INCREMENTの値よりも大きいIDを指定してINSERTした場合、AUTO_INCREMENTの値は移動してくれるのでしょうか。

↑で使用したテーブルの状態で引き続きSQLを実行します。

INSERT INTO user (id, name, email, del) values (30, 'ユーザ30', 'user30@example.com', 0);

オートインクリメントの値も変わってくれました！

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `del` tinyint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_c