MySQLでOracleのrownumのような行番号を付加する方法として、Web上でよく見かけるのは以下のようなユーザー変数を使ったSQL文で、2つのSQL文を発行しなければならない。
set @i:=0; select @i:=@i+1 as rownum,user_id from user;
しかし、kotori::logで紹介されていた方法を用いれば、１回のSQL文で付加することができる。メモメモ。
select @i:=@i+1 as rownum,user_id from (select @i:=0) as dummy,user;
なるほど。サブクエリでユーザー変数を初期化しているのね。
で、ふとorder by節で並び替えた時の挙動が気になったので、早速試してみた。
全部で４つ試していて、始めの２つがテーブルのidカラムで並び替え(昇順、降順)、残りの２つ(３つ目と４つ目)が生成したrownumで並び替えたものである。
１つ目。
mysql> select @i := @i + 1 rownum, id -> from (select @i := 0) dummy, tablename -> order by id asc; +--------+----+ | rownum | id | +--------+----+ | 1 | 2 | | 2 | 3 | | 3 | 5 | | 4 | 7 | | 5 | 11 | +--------+----+ 5 rows in set (0.00 sec)
２つ目。
mysql> select @i := @i + 1 rownum, id -> from (select @i:= 0) dummy, tablename -> order by id desc; +--------+----+ | rownum | id | +--------+----+ | 1 | 11 | | 2 | 7 | | 3 | 5 | | 4 | 3 | | 5 | 2 | +--------+----+ 5 rows in set (0.00 sec)
３つ目
mysql> select @i := @i + 1 rownum, id -> from (select @i := 0) dummy, tablename -> order by rownum asc; +--------+----+ | rownum | id | +--------+----+ | 1 | 2 | | 2 | 3 | | 3 | 5 | | 4 | 7 | | 5 | 11 | +--------+----+ 5 rows in set (0.00 sec)
４つ目
mysql> select @i := @i + 1 rownum, id -> from (select @i := 0) dummy, tablename -> order by rownum desc; +--------+----+ | rownum | id | +--------+----+ | 5 | 11 | | 4 | 7 | | 3 | 5 | | 2 | 3 | | 1 | 2 | +--------+----+ 5 rows in set (0.00 sec)
う～ん。２つ目のSQL文の結果が予想していた結果と異なっている。予想していた結果は４つ目のSQL文と同様の結果だったのだけれど……。
Oracleで以下のようなSQL文を書いて確認してみたが、どちらも４つ目と同じ結果。
select rownum, id from tablename order by id desc; select rownum, id from tablename order by rownum desc;
となると２つ目のSQL文は、４つ目のSQLと比べて
@i := @i + 1 rownum
を評価するタイミングが違うということになる。
２つ目のSQL文では、idカラムで並び替えた後に評価されているように見える。
というわけで調べてみたところ、MySQLのサイトの8.4. ユーザによって定義された変数のページで以下のような説明が。
ユーザ変数の評価順序は定義されておらず、与えられたクエリ内の要素に基づいて変更されることがあります。
SELECT @a, @a := @a+1 ...では、MySQLは@aを先に評価し次に割り当てが実行されるように見えますが、クエリの変更（例えばGROUP BY、HAVINGまたはORDER BY節による変更）は評価順序を変更する可能性があります。
このエントリで紹介したrownumもどきは、使用する時に注意が必要ですな。