MySQL 8.0 では InnoDB関連が強化されていますが、ここでは AUTO_INCREMENT に対象を絞って検証してみたいと思います。
MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0
MySQL 8.0 からは AUTO_INCREMENT のカウンター値はREDOログに書き込まれるように変更になりました。
そのため、いくつかの挙動でMySQL 5.7と異なる動きをします。
サーバーを再起動しても AUTO_INCREMENT の値がリセットされなくなりました。
MySQL 5.7 まで存在していたテーブル定義の AUTO_INCREMENT の値がサーバーの再起動でリセットされてしまうバグが解消されました。
検証
MySQL 5.7とMySQL 8.0 で 0 件のテーブルで AUTO_INCREMENT の値だけ1000に変更します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> create table t1 (id int(11) not null auto_increment, primary key (id)) auto_increment=1000; mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) |
MySQL Sandbox 環境の MySQLを再起動します。
|
1 2 |
$ ./sandboxes/msb_8_0_11/restart $ ./sandboxes/msb_5_7_22/restart |
MySQL 5.7 の場合 AUTO_INCREMENT の値がリセットされて 1 が挿入されました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> insert into t1 values(null); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) |
MySQL 8.0 の場合、再起動後も AUTO_INCREMENT の値が保持され、1000 が挿入されました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> insert into t1 values(null); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; +------+ | id | +------+ | 1000 | +------+ 1 row in set (0.00 sec) |
このバグ（仕様？）は、2003年5月に MySQL bugs に起票されてからMySQL 5.7 に至るまでずっとそのままだったものが、ついに MySQL 8.0 で解消されることになりました。
- 199 – bye bye bug #199, MySQL auto_increment is fixed !
- Bug #199 Innodb autoincrement stats los on restart
AUTO_INCREMENT の最大値より大きい値で更新した場合
AUTO_INCREMENT カラムの値を AUTO_INCREMENT の最大値より大きい値で変更した場合の挙動も MySQL 5.7 と MySQL 8.0 で異なるようになりました。
検証
MySQL 5.7、MySQL 8.0 で以下のテーブル作成します。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> create table t2 (id int(11) not null auto_increment, created_at datetime default current_timestamp, primary key(id)); mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `created_at` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) |
以下のように数件のレコードを作成します。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> insert into t2 (id) values (null); -- 3回繰り返す mysql> select id from t2 order by created_at; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec) |
AUTO_INCREMENT の最大値より大きい値で更新します。
|
1 |
mysql> update t2 set id = 10 where id = 2; |
MySQL 8.0 の場合
AUTO_INCREMENT が 11 に更新されていることがわかります。
|
1 2 3 4 5 6 7 8 9 |
mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `created_at` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) |
そのため、次にデータを挿入すると 11 が入ります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> insert into t2 (id) values(null); Query OK, 1 row affected (0.03 sec) mysql> select id from t2 order by created_at; +----+ | id | +----+ | 1 | | 10 | | 3 | | 11 | +----+ 4 rows in set (0.00 sec) |
MySQL 5.7 の場合
AUTO_INCREMENT は 4 のままです。
|
1 2 3 4 5 6 7 8 9 |
mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `created_at` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) |
そのため、次に挿入すると 4 が入ります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> insert into t2 (id) values(null); Query OK, 1 row affected (0.00 sec) mysql> select id from t2 order by created_at; +----+ | id | +----+ | 1 | | 10 | | 3 | | 4 | +----+ 4 rows in set (0.01 sec) |
まとめ
MySQL5.7とMySQL8.0ではいくつかのケースで AUTO_INCREMENT の挙動が変わっていることがわかりました。
アプリケーション側がこういった AUTO_INCREMENT の挙動を期待して実装している場合、思わぬ不具合が発生する可能性があるためアップグレード前に十分な検証を行うなどの注意が必要です。
