MySQLでテーブルに連番を挿入する方法｜セッション変数を使った実装手順

MySQLで連番を挿入するには？

MySQLでは、セッション変数（ユーザー定義変数）を利用することで、既存のテーブルのカラムに1から始まる連番を簡単に挿入できます。AUTO_INCREMENT属性を持たないカラムに対して、後から連番を振りたい場合などに便利なテクニックです。

基本的な構文は以下のとおりです。

-- 変数の初期化
SELECT @任意の変数名 := 任意の整数値;

-- 連番を更新しながら代入
UPDATE テーブル名 SET カラム名 = @任意の変数名 := @任意の変数名 + 増分;

サンプルテーブルの作成

まず、動作を確認するためのテーブルを作成しましょう。

mysql> create table SequentialNumberDemo
    -> (
    -> SequentialNumber int not null
    -> );
Query OK, 0 rows affected (0.84 sec)

テストデータの挿入

INSERT文を使って、バラバラの数値を持つレコードを登録します。

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

mysql> insert into SequentialNumberDemo values(10);
Query OK, 1 row affected (0.22 sec)

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

mysql> insert into SequentialNumberDemo values(60);
Query OK, 1 row affected (0.11 sec)

mysql> insert into SequentialNumberDemo values(50);
Query OK, 1 row affected (0.14 sec)

mysql> insert into SequentialNumberDemo values(40);
Query OK, 1 row affected (0.13 sec)

現在のデータを確認

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

mysql> select * from SequentialNumberDemo;

実行結果は以下のとおりです。

+------------------+
| SequentialNumber |
+------------------+
|              100 |
|               10 |
|                9 |
|               60 |
|               50 |
|               40 |
+------------------+
6 rows in set (0.00 sec)

このように、現時点では数値がバラバラで連番になっていません。ここから、セッション変数を使って1から始まる連番に更新していきます。

セッション変数で連番を振り直す手順

STEP 1：変数を初期化する

まず、変数「@sequence」を0で初期化します。

mysql> select @sequence := 0;

実行結果：

+---------------+
| @sequence:= 0 |
+---------------+
|             0 |
+---------------+
1 row in set (0.03 sec)

STEP 2：UPDATE文で連番を代入する

次に、UPDATE文の中で変数を1ずつ加算しながらカラムに代入します。「@sequence := @sequence + 1」という式により、行が処理されるたびに値がインクリメントされていきます。

mysql> update SequentialNumberDemo 
    set SequentialNumber = @sequence := @sequence + 1;
Query OK, 6 rows affected (0.15 sec)
Rows matched: 6  Changed: 6  Warnings: 0

STEP 3：結果を確認する

再度テーブルの内容を確認しましょう。

mysql> select * from SequentialNumberDemo;

実行結果：

+------------------+
| SequentialNumber |
+------------------+
|                1 |
|                2 |
|                3 |
|                4 |
|                5 |
|                6 |
+------------------+
6 rows in set (0.00 sec)

これで、すべてのレコードに1から順番に連番が割り当てられました。

補足：MySQL 8.0以降ならウィンドウ関数も使える

MySQL 8.0以降を使用している場合は、標準SQLのウィンドウ関数ROW_NUMBER()を使う方法もおすすめです。セッション変数は非推奨とされる場面もあるため、新規開発では以下のようなクエリの方が安全かつ可読性が高いでしょう。

SELECT ROW_NUMBER() OVER (ORDER BY SequentialNumber) AS row_num,
       SequentialNumber
FROM SequentialNumberDemo;

状況に応じて、セッション変数方式とROW_NUMBER()方式を使い分けてください。