MySQLのINSERTでMAX()+1を実装する方法｜COALESCE関数を使った連番採番テクニック

MySQLで新しいレコードを挿入する際、特定の条件における既存カラムの最大値（MAX）に1を加えた値を自動的に設定したいケースはよくあります。例えば、グループごとに連番を振りたい場合などが該当します。このような処理を実現するには、COALESCE()関数を使用します。

COALESCE()関数を使った基本構文

COALESCE()関数は、引数の中から最初にNULLではない値を返す関数です。これにより、テーブルにレコードが存在しない場合（MAXの結果がNULLの場合）でも、初期値として0を扱うことができます。基本的な構文は以下の通りです。

INSERT INTO yourTableName(yourColumnName1,yourColumnName2)
SELECT 1 + COALESCE((SELECT MAX(yourColumnName1) FROM yourTableName WHERE yourColumnName2='yourValue'), 0), 'yourValue';

動作確認用テーブルの作成

それでは、実際に構文の動きを理解するためにテーブルを作成してみましょう。まずはテーブルを作成するクエリです。

mysql> create table InsertMaxPlus1Demo
   -> (
   -> Id int,
   -> Name varchar(20)
   -> );
Query OK, 0 rows affected (1.27 sec)

サンプルデータの挿入

次に、INSERTコマンドを使っていくつかのレコードを登録します。ここでは意図的にIdが重複したデータも含めています。

mysql> insert into InsertMaxPlus1Demo(Id,Name) values(1,'John');
Query OK, 1 row affected (0.12 sec)
mysql> insert into InsertMaxPlus1Demo(Id,Name) values(1,'Mike');
Query OK, 1 row affected (0.21 sec)
mysql> insert into InsertMaxPlus1Demo(Id,Name) values(2,'John');
Query OK, 1 row affected (0.12 sec)
mysql> insert into InsertMaxPlus1Demo(Id,Name) values(1,'Larry');
Query OK, 1 row affected (0.20 sec)
mysql> insert into InsertMaxPlus1Demo(Id,Name) values(3,'John');
Query OK, 1 row affected (0.18 sec)
mysql> insert into InsertMaxPlus1Demo(Id,Name) values(2,'David');
Query OK, 1 row affected (0.17 sec)

登録データの確認

SELECT文ですべてのレコードを表示して、現在のテーブルの状態を確認します。

mysql> select *from InsertMaxPlus1Demo;

以下が出力結果です。

+------+-------+
| Id   | Name  |
+------+-------+
|    1 | John  |
|    1 | Mike  |
|    2 | John  |
|    1 | Larry |
|    3 | John  |
|    2 | David |
+------+-------+
6 rows in set (0.00 sec)

MAX()+1によるINSERTの実行

いよいよ、本題であるMAX()+1を実現するINSERTクエリを実行します。ここでは「John」という名前のレコードに対して処理を行います。

mysql> INSERT INTO InsertMaxPlus1Demo (Id, Name)
   -> SELECT 1 + coalesce((SELECT max(Id) FROM InsertMaxPlus1Demo WHERE Name='John'), 0), 'John';
Query OK, 1 row affected (0.21 sec)
Records: 1 Duplicates: 0 Warnings: 0

このクエリでは、まずサブクエリによってNameが「John」であるレコードの中からIdの最大値を取得しています。現時点でJohnの最大Idは3なので、新しく挿入されるレコードには4が割り当てられます。仮に該当するレコードが一件も存在しなかった場合でも、COALESCE()がNULLを0に置き換えるため、Id=1から採番が始まる点がポイントです。

実行結果の検証

再度SELECT文を実行して、テーブルの状態を確認しましょう。

mysql> select *from InsertMaxPlus1Demo;

以下が出力結果です。

+------+-------+
| Id   | Name  |
+------+-------+
|    1 | John  |
|    1 | Mike  |
|    2 | John  |
|    1 | Larry |
|    3 | John  |
|    2 | David |
|    4 | John  |
+------+-------+
7 rows in set (0.00 sec)

まとめ

このように、COALESCE()関数とサブクエリ内のMAX()を組み合わせることで、条件に応じた最大値+1の値を持つレコードを簡単に挿入できます。グループ別の連番管理や、AUTO_INCREMENT以外の独自採番が必要な場面で活用できるテクニックなので、ぜひ覚えておきましょう。