MySQLでROW_NUMBER()の代替を実現する方法（ユーザー変数を活用）

MySQLにおけるROW_NUMBER()の代替手法

MySQL（8.0より前のバージョン）には、SQL ServerやPostgreSQLのようなROW_NUMBER()ウィンドウ関数が組み込みで存在しません。しかし、ユーザー定義変数を活用することで、ROW_NUMBER()と同等の連番を簡単に生成できます。

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

SELECT
(@yourVariableName:=@yourVariableName + 1) AS `anyAliasName`,
yourColumnName1, yourColumnName2, ...N
FROM
yourTableName, (SELECT @yourVariableName:=0) AS anyAliasName;

この構文のポイントは、クロス結合で初期値0の変数を定義し、行が読み込まれるたびに変数を1ずつインクリメントしている点です。これにより、各レコードに連番が付与されます。

サンプルテーブルの作成

実際に動作を確認するため、テーブルを作成してみましょう。

mysql> create table RowNumberDemo
   -> (
   -> UserId int,
   -> UserName varchar(20)
   -> );
Query OK, 0 rows affected (0.74 sec)

テストデータの挿入

INSERTコマンドを使って、いくつかのレコードを挿入します。

mysql> insert into RowNumberDemo values(10,'John');
Query OK, 1 row affected (0.14 sec)
mysql> insert into RowNumberDemo values(50,'Carol');
Query OK, 1 row affected (0.20 sec)
mysql> insert into RowNumberDemo values(100,'Sam');
Query OK, 1 row affected (0.20 sec)
mysql> insert into RowNumberDemo values(150,'Mike');
Query OK, 1 row affected (0.54 sec)
mysql> insert into RowNumberDemo values(210,'Bob');
Query OK, 1 row affected (0.58 sec)

テーブルの内容を確認

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

mysql> select *from RowNumberDemo;

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

+--------+----------+
| UserId | UserName |
+--------+----------+
|     10 | John     |
|     50 | Carol    |
|    100 | Sam      |
|    150 | Mike     |
|    210 | Bob      |
+--------+----------+
5 rows in set (0.00 sec)

ROW_NUMBER()相当のクエリを実行

それでは、ユーザー変数を使ってROW_NUMBER()と同等の連番を取得するクエリを見てみましょう。

mysql> SELECT
   -> (@ROW_NUMBER:=@ROW_NUMBER + 1) AS `ROW_NUMBER`,
   -> UserId, UserName
   -> FROM
   -> RowNumberDemo, (SELECT @ROW_NUMBER:=0) AS t;

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

+------------+--------+----------+
| ROW_NUMBER | UserId | UserName |
+------------+--------+----------+
|          1 |     10 | John     |
|          2 |     50 | Carol    |
|          3 |    100 | Sam      |
|          4 |    150 | Mike     |
|          5 |    210 | Bob      |
+------------+--------+----------+
5 rows in set (0.03 sec)

このように、UserIdやUserNameの値に関係なく、行ごとに1から順に番号が割り振られていることが確認できます。

補足：MySQL 8.0以降の場合

MySQL 8.0以降では、ROW_NUMBER()がウィンドウ関数として標準サポートされているため、ユーザー変数を使わずに以下のように記述できます。

SELECT ROW_NUMBER() OVER (ORDER BY UserId) AS `ROW_NUMBER`,
       UserId, UserName
FROM RowNumberDemo;

古いバージョンのMySQLを利用している場合はユーザー変数の手法を、8.0以降では標準のウィンドウ関数を使うのがおすすめです。なお、ユーザー変数方式は評価順序に依存するため、ORDER BYと組み合わせる場合は挙動が不安定になることがある点に注意しましょう。