0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

SQL - ランダムに行を取得したい

Posted at

主にデバッグで。

参考URL

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

ただしこの方法は大きいテーブルだと重くなるらしい。
行が計算を必要とする乱数と連携するためにシーケンシャルなスキャンが発生するから、らしい。

これを一定の速度で行うならば、インデックスされた数値を納めたカラムを使って以下の様に書く。

SELECT * FROM table WHERE num_value >= RAND() * 
    ( SELECT MAX (num_value ) FROM table ) 
ORDER BY num_value LIMIT 1
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?