mysql随机删除若干行_mysql select随机行

Summary: in this tutorial, you will learn various techniques toselect random recordsfrom 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*FROMtableORDERBYRAND()LIMIT1

Let’s examine the query in more detail.The ORDER BY clause sorts the result set in random order

andLIMIT1 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*FROMtableORDERBYRAND()LIMITN

For example to select 5 random products, you use the following query:SELECTproductName,productLineFROMproductsORDERBYRAND()LIMIT5;

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*FROMtableWHEREID>=@IDLIMIT1

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