MySQL に格納したデータから、ランダムにデータを取得したりソートする必要に迫られた。ランダムに並べ替える方法は有名で、比較的簡単にやり方を見つけられたのだが、ランダムにデータを取得してる「ふり」をする方法がなかなか思いつかなかったので備忘録としてまとめてみた。

ランダムにソートする

MySQL には、数学関数としてRANDが用意されており、ORDER BY に RAND() を与えることで簡単に実現できる。

SELECT * FROM table ORDER BY RAND();

ただし、よい方法ではない。RAND 関数の仕様は、マニュアルによると、

・RAND(), RAND(N)

0 <= v < 1.0 の範囲にあるランダムな浮動小数点値 v を戻します。定数整数引数 N が指定されている場合は、カラム値の反復可能なシークエンスを生成するシード値として使用されます。

となっている。

単純に数字が戻るだけなので、SQL を実行したらテーブルの行数分の乱数を発行するのではないだろうか。また、並べ替えのためにインデックスを利用できないだろう。試しに、

DESCRIBE SELECT * FROM table ORDER BY RAND();

とやってみると、Extra には、「Using temporary; Using filesort」とでた。テーブルが大きい場合、この方法をとるのはやめた方がよさそうだ。

古い記事だが、“Do not use ORDER BY RAND()” or “How to get random rows from table?”という記事には解決策が書かれており、プログラムでランダムな値を生成、LIMIT の最初の引数として与える。ランダムな位置から、取得したい数だけ行を取得するという方法だ。あらかじめテーブルの行数を取得する必要があるが、巨大なテーブルの場合は効果が大きいようだ。IN 句を使う方法も記述されているが、ここでは割愛する。

SELECT * FROM table LIMIT <生成した値>, 1
SELECT * FROM table LIMIT <生成した値>, N

ランダムにデータを取得する

ランダムにデータを取得すると言っても、全くでたらめにデータを取得するわけではないので、背景についても備忘録として記述しておく。対象となるテーブルは2つあり、1つはマスターテーブル、もう1つはデータを格納したテーブルだ。マスターテーブルは id と name があり、実データを格納しているテーブルにはマスターテーブルの id のみ格納している(ごく一般的なテーブルの仕様だ)。実データのテーブルから、指定した(マスターテーブルの) id を持つ行を取得したいのだが、id はアクセスするたびに異なるものにしなければならなかった。

一番最初に思いついたのは、RAND 関数を利用すること。マニュアルには、

<= R < j の範囲のランダムな整数 R を取得するには、式 FLOOR(i + RAND() * (j – i) を使用します。

と書かれているので、マスターテーブルの id の MAX 値を使えば簡単にできると思ったが、マスターテーブルに削除フラグがあるのを忘れていた。削除フラグの立った値が式から戻ってきてしまうと、データが取得できなくなってしまう。最初に思いついた方法が悪かったのではまってしまったが、よく考えてみると単純に、

SELECT
    dt.*
FROM
    data_table dt,
    (SELECT id FROM master_table ORDER BY RAND() LIMIT 1) r
WHERE
    dt.id = r.id;

で取得できた。ORDER BY に RAND を使っているので、負荷が高くなる場合は上記で述べた方法に変更する必要がある。

なお、WHERE 句に RAND 関数を使うと WHERE が評価されるたびに乱数を生成するので負荷がかかるだけでなく、データが取得できたりできなかったりという不思議な結果になる。