MySQLのSELECT結果に行番号を振る。

MySQLにはOracleなんかと違ってROW_NUMBER関数がないので、ユーザ変数を使って振ってやる必要がある。
便利なのでメモ。

普通にSELECTした結果。

mysql> SELECT TABLE_NAME FROM information_schema.TABLES LIMIT 10;
+---------------------------------------+
| TABLE_NAME                            |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
+---------------------------------------+
10 rows in set (0.00 sec)

mysql> 

行番号つけた結果。

mysql> SET @rownum=0; SELECT @rownum:=@rownum+1 as ROW_NUM, TABLE_NAME FROM information_schema.TABLES LIMIT 10;
Query OK, 0 rows affected (0.00 sec)

+---------+---------------------------------------+
| ROW_NUM | TABLE_NAME                            |
+---------+---------------------------------------+
|       1 | CHARACTER_SETS                        |
|       2 | COLLATIONS                            |
|       3 | COLLATION_CHARACTER_SET_APPLICABILITY |
|       4 | COLUMNS                               |
|       5 | COLUMN_PRIVILEGES                     |
|       6 | ENGINES                               |
|       7 | EVENTS                                |
|       8 | FILES                                 |
|       9 | GLOBAL_STATUS                         |
|      10 | GLOBAL_VARIABLES                      |
+---------+---------------------------------------+
10 rows in set (0.00 sec)

mysql>