エマノンの雑記帳

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もどきは、使用する時に注意が必要ですな。