OracleやMySQLで
よく見かけるSQLの間違いをまとめていきます。
#MySQLで分析関数のRow_Number関数と同じ結果を取得
話を簡単にするために、ソートキーは重複値なしとします。
#####テーブル定義とデータ準備
create table IDTable(
ID int unsigned,
primary key(ID));
insert into IDTable values
(1),(3),(5),(10),(20),(30),(40);
#####分析関数を使うSQL(MySQL5.6では動作しません)
select ID,Row_Number() over(order by ID) as rn
from IDTable
order by ID;
#間違ったSQL
ユーザ変数を使って、OrderBy句でのソート順に
カウントアップしていくというもの
select ID,@rn := @rn+1 as Rn
from IDTable,(select @rn := 0) as Init
order by ID;
+----+------+
| ID | Rn |
+----+------+
| 1 | 1 |
| 3 | 2 |
| 5 | 3 |
| 10 | 4 |
| 20 | 5 |
| 30 | 6 |
| 40 | 7 |
+----+------+
#間違いの理由
MySQLのマニュアルに、ユーザー変数の評価順序が未定義と書いてあるから。
上記のSelect文だと
@rn := 0 が1番目に評価されて、
その後で、order by IDの行の順番で、@rn := @rn+1が評価されることが保証されない。
マニュアル(日本語)より引用
http://download.nust.na/pub6/mysql/doc/refman/5.1/ja/user-variables.html
MySQL 5.1 リファレンスマニュアル :: 8.4 ユーザによって定義された変数
ユーザ変数の評価順序は定義されておらず、与えられたクエリ内の要素に基づいて変更されることがあります。
SELECT @a, @a := @a+1 ...では、MySQLは@aを先に評価し次に割り当てが実行されるように見えますが、
クエリの変更（例えばGROUP BY、HAVINGまたはORDER BY節による変更）は評価順序を変更する可能性があります。基本的なルールは、
ステートメントの一部でユーザ変数値を割り当てないことおよび
同一ステートメント内の他部分で同じ変数を使用しないことです。
期待通りの結果を得られるかもしれませんが、
これは確約されていません。
英語の原文
MySQL 5.1 Reference Manual :: 8.4 User-Defined Variables
The order of evaluation for user variables is undefined and
may change based on the elements contained within a given query.In SELECT @a, @a := @a+1 ...,
you might think that MySQL will evaluate @a first and then do an assignment second,
but changing the query (for example, by adding a GROUP BY, HAVING, or ORDER BY clause)
may change the order of evaluation.The general rule is never to assign a value to a user variable in one part of a statement
and use the same variable in some other part of the same statement.
You might get the results you expect, but this is not guaranteed.
なお、上記のマニュアルのステートメントという表現は、
Select文、Update文、Insert文、Delete文において、その文全体を指して、1つのステートメントという意味です。
(Now関数の結果が一致するため)
Now() は、ステートメントが実行を開始する時刻を示す定数時間を返します。
https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_now
NOW関数は デフォルトでは 「そのステートメントの開始時刻」を返します。
https://yoku0825.blogspot.com/2016/12/mysqlnow.html
select ID,@rn := @rn+1 as Rn,Now(),SLEEP(1)
from IDTable,(select @rn := 0) as Init
order by ID;
+----+------+---------------------+----------+
| ID | Rn | Now() | SLEEP(1) |
+----+------+---------------------+----------+
| 1 | 1 | 2019-02-10 09:33:05 | 0 |
| 3 | 2 | 2019-02-10 09:33:05 | 0 |
| 5 | 3 | 2019-02-10 09:33:05 | 0 |
| 10 | 4 | 2019-02-10 09:33:05 | 0 |
| 20 | 5 | 2019-02-10 09:33:05 | 0 |
| 30 | 6 | 2019-02-10 09:33:05 | 0 |
| 40 | 7 | 2019-02-10 09:33:05 | 0 |
+----+------+---------------------+----------+
##MySQL5.7以降で削除予定の機能になりました
さらには、MySQL5.7以降で、Set文以外(例えばSelect文)
でのユーザ変数への値の代入は、削除予定の機能になりました。
It is also possible to assign a value to a user variable in statements other than SET.
(This functionality is deprecated in MySQL 8.0 and subject to removal in a subsequent release.)
##MySQLが賢くなったら、インラインビューでのユーザ変数の初期化を、スキップ
さらに、MySQL開発者のGuilhem Bichotさんのブログによると
将来、MySQLが賢くなったら
インラインビューでのユーザ変数の初期化を、スキップするそうです。
https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/
SELECT @rownum:=(@rownum+1) AS num, name, birthdate
FROM (SELECT @rownum:=0) AS initialization, people
ORDER BY birthdate;
It works. It relies on the fact that MySQL evaluates the content of the derived table named initialization.
But in the future, MySQL may become smarter and say that
"this derived table isn't used by the statement so I can just skip its evaluation".
And it would thus skip the initialization.
#正しいSQL
select ID,
(select count(*)+1
from IDTable b
where b.ID < a.ID) as Rn
from IDTable a
order by ID;
#参考にしたサイト
MySQL開発者のGuilhem Bichotさんのブログ
https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/
MySQLユーザ会のメーリングリスト
http://www.mysql.gr.jp/mysqlml/mysql/msg/16487