how to fetch random numbers of rows from mysql database?

I have around 300 records in some table in开发者_如何学JAVA mysql database. And I have a requirement to fetch 40 random records with one query. How to write the query? need help thanks.

For a small table likes yours it should suffice with this:

SELECT * FROM table ORDER BY RAND() LIMIT 40;

Note that this is not suitable for large tables since MySQL will have to do a table scan and order all rows in the table due to the usage of ORDER BY RAND(). For large tables you will have to implement this mostly in application code, keeping track of which rows you've already got and generating random ids to fetch.

You should use rand() with order by like this:

SELECT field1, field2
FROM tableName
ORDER BY RAND()
LIMIT 40

order by rand() may cause performance issue, instead try to do in following way:

 // what NOT to do:  
 $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");  

 // much better:  

 $r = mysql_query("SELECT count(*) FROM user");  
 $d = mysql_fetch_row($r);  
 $rand = mt_rand(0,$d[0] - 1);  

  $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");    

Note that this is not a fast solution, but it works fine for just 300 records

SELECT [rows]
FROM [table]
ORDER BY RAND()
LIMIT 40

you should user RAND() in the WHERE clause

but you first make fragment 40 / number of rows in table

ex:

SELECT * FROM [TABLE_NAME] WHERE RAND()< 0.0005;

SELECT * FROM table ORDER BY RAND() LIMIT 40