別のテーブルのMAX値を使ってMySQLのAUTO_INCREMENTをリセットする方法

MySQLでは、プリペアドステートメント（PREPARE文）を活用することで、別のテーブルのMAX値を取得し、その値をもとにAUTO_INCREMENTをリセットできます。

基本構文

以下がその構文です。

set @anyVariableName1=(select MAX(yourColumnName) from yourTableName1);
SET @anyVariableName2 = CONCAT('ALTER TABLE yourTableName2
AUTO_INCREMENT=', @anyVariableName1);
PREPARE yourStatementName FROM @anyVariableName2;
execute yourStatementName;

この構文を使うと、別のテーブルから取得した最大値を基に、MySQLのAUTO_INCREMENTをリセットできます。理解を深めるために、実際に2つのテーブルを作成してみましょう。1つ目のテーブルにはレコードを格納し、2つ目のテーブルでは1つ目のテーブルの最大値をAUTO_INCREMENTの開始値として設定します。

手順1：1つ目のテーブルを作成する

まず、テーブルを作成するクエリは以下のとおりです。

mysql> create table FirstTableMaxValue
    -> (
    -> MaxNumber int
    -> );
Query OK, 0 rows affected (0.64 sec)

次に、INSERTコマンドを使ってレコードを挿入します。

mysql> insert into FirstTableMaxValue values(100);
Query OK, 1 row affected (0.15 sec)

mysql> insert into FirstTableMaxValue values(1000);
Query OK, 1 row affected (0.19 sec)

mysql> insert into FirstTableMaxValue values(2000);
Query OK, 1 row affected (0.12 sec)

mysql> insert into FirstTableMaxValue values(90);
Query OK, 1 row affected (0.15 sec)

mysql> insert into FirstTableMaxValue values(2500);
Query OK, 1 row affected (0.17 sec)

mysql> insert into FirstTableMaxValue values(2300);
Query OK, 1 row affected (0.12 sec)

SELECT文ですべてのレコードを表示してみましょう。

mysql> select *from FirstTableMaxValue;

出力結果

+-----------+
| MaxNumber |
+-----------+
|       100 |
|      1000 |
|      2000 |
|        90 |
|      2500 |
|      2300 |
+-----------+
6 rows in set (0.05 sec)

手順2：2つ目のテーブルを作成する

続いて、AUTO_INCREMENTを持つ2つ目のテーブルを作成します。

mysql> create table AutoIncrementWithMaxValueFromTable
   -> (
   -> ProductId int not null auto_increment,
   -> Primary key(ProductId)
   -> );
Query OK, 0 rows affected (1.01 sec)

手順3：MAX値を取得してAUTO_INCREMENTに設定する

ここで、1つ目のテーブルから最大値を取得し、その値を2つ目のテーブルのAUTO_INCREMENTに設定する一連のステートメントを実行します。

mysql> set @v=(select MAX(MaxNumber) from FirstTableMaxValue);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @Value2 = CONCAT('ALTER TABLE AutoIncrementWithMaxValueFromTable
AUTO_INCREMENT=', @v);
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE myStatement FROM @value2;
Query OK, 0 rows affected (0.29 sec)
Statement prepared

mysql> execute myStatement;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0

これで、1つ目のテーブルの最大値である2500が2つ目のテーブルに反映されました。以降、このテーブルに挿入されるレコードは2500、2501、2502…と連番で採番されていきます。

手順4：レコードを挿入して確認する

実際に2つ目のテーブルへレコードを挿入してみましょう。

mysql> insert into AutoIncrementWithMaxValueFromTable values();
Query OK, 1 row affected (0.24 sec)

mysql> insert into AutoIncrementWithMaxValueFromTable values();
Query OK, 1 row affected (0.10 sec)

SELECTコマンドですべてのレコードを確認します。

mysql> select *from AutoIncrementWithMaxValueFromTable;

出力結果

+-----------+
| ProductId |
+-----------+
|      2500 |
|      2501 |
+-----------+
2 rows in set (0.00 sec)

出力結果のとおり、AUTO_INCREMENTが最大値の2500から正しく開始されていることが確認できます。この手法は、テーブルのデータを移行・統合した際などに、IDの重複を避けたい場合に特に役立ちます。