【MySQL】テーブルに連番付けを行う

row_number関数を使う。over句に何も指定しなければテーブルに登録されている順に連番付けされる。以下は既存のtemptbというテーブルを利用した動作例。

mysql> select * from temptb;
+------+
| no   |
+------+
|   20 |
|   20 |
|   30 |
|   10 |
|   40 |
+------+
5 rows in set (0.00 sec)
mysql> select no, row_number() over () as rownumber from temptb;
+------+-----------+
| no   | rownumber |
+------+-----------+
|   20 |         1 |
|   20 |         2 |
|   30 |         3 |
|   10 |         4 |
|   40 |         5 |
+------+-----------+
5 rows in set (0.00 sec)
mysql> select no, row_number() over (order by no) as rownumber from temptb;
+------+-----------+
| no   | rownumber |
+------+-----------+
|   10 |         1 |
|   20 |         2 |
|   20 |         3 |
|   30 |         4 |
|   40 |         5 |
+------+-----------+
5 rows in set (0.00 sec)