「MySQLあるある」の1つ、**「MySQLを再起動するとInnoDBテーブルのAUTO_INCREMENT値がレコードの最大値+1に巻き戻る件」**が、MySQL 8.0(DMR)で解消されているということで、今更ですが実際に試してみました。
※本当は「透過的データ暗号化のその後」として、REDO/UNDOログの暗号化のほうを試そうかと思っていたのですが…MySQL 8.0.1以降で実装された機能はまだリリースされていませんでした。残念。Percona Live 2017の前に次のDMRがリリースされると良いのですが。
1. MySQL 5.7までの挙動
まず、テスト用のスキーマ／テーブル（いずれも名前は「test_ai」）を作成し、PK列としてAUTO_INCREMENT値が入るようにします。
# mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE test_ai;
Query OK, 1 row affected (0.00 sec)
mysql> USE test_ai;
Database changed
mysql> CREATE TABLE test_ai (id int(10) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100)) ENGINE innodb;
Query OK, 0 rows affected (0.01 sec)
次に、このテーブルに続けて3行INSERTします。
mysql> INSERT INTO test_ai SET name = 'first';
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test_ai SET name = 'second';
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test_ai SET name = 'third';
Query OK, 1 row affected (0.00 sec)
こんなふうにレコードが入っています。
mysql> SELECT * FROM test_ai;
+----+--------+
| id | name |
+----+--------+
| 1 | first |
| 2 | second |
| 3 | third |
+----+--------+
3 rows in set (0.00 sec)
3行目をDELETEしてみます。
mysql> DELETE FROM test_ai WHERE id = 3;
Query OK, 1 row affected (0.02 sec)
続いて、4行目をINSERTしてみます。
mysql> INSERT INTO test_ai SET name = 'fourth';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test_ai;
+----+--------+
| id | name |
+----+--------+
| 1 | first |
| 2 | second |
| 4 | fourth |
+----+--------+
3 rows in set (0.00 sec)
「id=3」は飛ばされて、「id=4」でINSERTされたことがわかります。
ここで、「id=4」の行を削除し、テーブル定義を確認してみます。
mysql> DELETE FROM test_ai WHERE id = 4;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE TABLE test_ai\G
*************************** 1. row ***************************
Table: test_ai
Create Table: CREATE TABLE `test_ai` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
次に割り当てられるAUTO_INCREMENT値が「5」であることがわかります。
MySQLを再起動してみます。
mysql> QUIT;
Bye
# systemctl restart mysqld.service
再起動後、新しい行をINSERTすると、「id=3」でINSERTされてしまうことがわかります。
# mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE test_ai;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> INSERT INTO test_ai SET name = 'fifth';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test_ai;
+----+--------+
| id | name |
+----+--------+
| 1 | first |
| 2 | second |
| 3 | fifth |
+----+--------+
3 rows in set (0.00 sec)
mysql>
これがMySQL 5.7までの挙動です。
外部キー制約を使わない（シャーディング・パーティショニング等のために使えない）場合、テーブルの最終行を削除したところでMySQLを再起動すると、他テーブルとのレコード間の紐づきがおかしくなる危険性があります。
2. MySQL 8.0では
同じように進めてみます。
# mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.0-dmr MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE test_ai;
Query OK, 1 row affected (0.00 sec)
mysql> USE test_ai;
Database changed
mysql> CREATE TABLE test_ai (id int(10) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100)) ENGINE innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO test_ai SET name = 'first';
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test_ai SET name = 'second';
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test_ai SET name = 'third';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test_ai;
+----+--------+
| id | name |
+----+--------+
| 1 | first |
| 2 | second |
| 3 | third |
+----+--------+
3 rows in set (0.00 sec)
mysql> DELETE FROM test_ai WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test_ai SET name = 'fourth';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test_ai;
+----+--------+
| id | name |
+----+--------+
| 1 | first |
| 2 | second |
| 4 | fourth |
+----+--------+
3 rows in set (0.00 sec)
mysql> DELETE FROM test_ai WHERE id = 4;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE TABLE test_ai\G
*************************** 1. row ***************************
Table: test_ai
Create Table: CREATE TABLE `test_ai` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> QUIT;
Bye
# systemctl restart mysqld.service
# mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.0-dmr MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE test_ai;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW CREATE TABLE test_ai\G
*************************** 1. row ***************************
Table: test_ai
Create Table: CREATE TABLE `test_ai` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
再起動しても、次のAUTO_INCREMENT値は「5」のままです。
mysql> INSERT INTO test_ai SET name = 'fifth';
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM test_ai;
+----+--------+
| id | name |
+----+--------+
| 1 | first |
| 2 | second |
| 5 | fifth |
+----+--------+
3 rows in set (0.00 sec)
mysql>
再起動前に削除した「id=3, 4」が飛ばされて「id=5」でINSERTされました。
3. 注意
実は、1. の続きでMySQL 8.0にバージョンアップ（途中手順ミスあり）して同じことを試したところ、なぜかMySQL 5.7までと同じ挙動になってしまいました。
正確には、
- MySQL 5.7で 1. の手順を実行後、「test_ai」テーブル・スキーマを削除
- MySQL Community Server用のYUMリポジトリの設定を書き換えて、MySQL 5.7を無効化（enable=0）、MySQL 8.0を有効化（enable=1）
- MySQL 5.7を止めずに「yum update mysql-community-server」
- 誤って「mysql_secure_installation」したら最後で「システムテーブルがおかしい」と怒られる
- MySQLを再起動し、「mysql_upgrade」してからあらためて「mysql_secure_installation」
- この状態で 2. の手順を実行
という流れで進めたところ、再起動後にINSERTした行が「id=3」になってしまいました。
MySQL 8.0のGA版ではどうなるかわかりませんが、5.7からのバージョンアップで8.0を使う場合は、手順を間違えないよう注意したほうが良さそうです。
【おまけ】
MySQL 8.0関連投稿記事へのリンクを集めました。