Summary: in this tutorial, you will learn various techniques to select random records from a database table in MySQL.
Sometimes, you have to select random records from a database table, for example:
Select some random posts in a blog and display them in the sidebar.
Select a random quote for displaying “quote of the day” widget.
Select random pictures in a gallery and use the as the featured pictures.
MySQL select random records using RAND function
MySQL does not have any built-in statement to select random records from a database table. In order to accomplish this, you use the RAND function. The following query select a random record from a database table:
SELECT * FROM table ORDER BY RAND() LIMIT 1
Let’s examine the query in more detail.
The ORDER BY clause sorts the result set in random order
and LIMIT 1 clause picks the first one in the random result set.
If you want to select N random records from a database table, you need to change the number in the LIMIT clause as follows:
SELECT * FROM table ORDER BY RAND() LIMIT N
For example to select 5 random products, you use the following query:
SELECT productName, productLine FROM products ORDER BY RAND() LIMIT 5;
You may get a different result set because it is random.
This technique works very well with a table that has few records. It will be very slow with the big table because MySQL has to sort the entire table to pick the random ones.
In case the table has ID column with the values that fall within a range 1.. N and there is no gap in the range, you can use the following technique:
First, you select random numbers in the range 1..N.
Second, you pick the records based on the random numbers.
The following statements help you accomplish this:
SET @ID = FLOOR(RAND( )* N) + 1; SELECT * FROM table WHERE ID >= @ID LIMIT 1
This technique works faster because:
It is using ID column which is in the index.
MySQL don’t have to sort the whole table.
In this tutorial, we have shown you several techniques to select random record from a database table.
原文链接：http://outofmemory.cn/mysql/tips/select-random-records-database-table