MySQLでROW_NUMBER関数を使用してグループ毎に連番を設定する方法

以前にMySQLでオートインクリメントを使用せずにグループ毎に連番を設定する方法をメモしたが、MySQL8系からはROW_NUMBER関数を用いることでもっと簡単に採番できるようになったらしい。以下に利用方法をメモ。

リファレンス

ROW_NUMBER関数

https://dev.mysql.com/doc/refman/8.0/ja/window-function-descriptions.html#function_row-number

テーブル内容について

以前に取得した全国の市区町村のデータを使用する。

ソースコード

東京都(ken_id=13)のデータ且つidでソートしたい場合以下のようなSQL文になる。

SELECT `id`, ROW_NUMBER() OVER (ORDER BY `ken_id`) AS row_num, `ken_name`, `city_name`
FROM `ad_address` 
WHERE `ken_id` = 13 
LIMIT 10;

結果は以下の通り。

+-----------+---------+-----------+-----------+
| id        | row_num | ken_name  | city_name |
+-----------+---------+-----------+-----------+
| 150614700 |       1 | 東京都    | 渋谷区    |
| 150619000 |       2 | 東京都    | 渋谷区    |
| 150800100 |       3 | 東京都    | 渋谷区    |
| 150801000 |       4 | 東京都    | 渋谷区    |
| 150801500 |       5 | 東京都    | 渋谷区    |
| 150801900 |       6 | 東京都    | 渋谷区    |
| 150808100 |       7 | 東京都    | 渋谷区    |
| 150830100 |       8 | 東京都    | 渋谷区    |
| 150830300 |       9 | 東京都    | 渋谷区    |
| 150830600 |      10 | 東京都    | 渋谷区    |
+-----------+---------+-----------+-----------+
10 rows in set (0.93 sec)

ページングに対応させたい場合、通常通りOFFSETをつける。

SELECT `id`, ROW_NUMBER() OVER (ORDER BY `id`) AS row_num, `ken_name`, `city_name`
FROM `ad_address` 
WHERE `ken_id` = 13 
LIMIT 10, 10;

結果は以下の通り。

+-----------+---------+-----------+--------------+
| id        | row_num | ken_name  | city_name    |
+-----------+---------+-----------+--------------+
| 100001400 |      11 | 東京都    | 千代田区     |
| 100010000 |      12 | 東京都    | 大島町       |
| 100010100 |      13 | 東京都    | 大島町       |
| 100010200 |      14 | 東京都    | 大島町       |
| 100010300 |      15 | 東京都    | 大島町       |
| 100010400 |      16 | 東京都    | 大島町       |
| 100021100 |      17 | 東京都    | 大島町       |
| 100021200 |      18 | 東京都    | 大島町       |
| 100030100 |      19 | 東京都    | 利島村       |
| 100040000 |      20 | 東京都    | 新島村       |
+-----------+---------+-----------+--------------+
10 rows in set (1.09 sec)

所感

MySQLの変数を使う方法より分かりやすいように思った。今後はこちらの方法を使用していきたいところ。