mysql implements a method of randomly fetching several pieces of data

admin · Posted on 7/19/2017 9:34:04 PM

There are several ways to write SQL statements
1: SELECT * FROM tablename ORDER BY RAND() LIMIT The number of pieces of data you want to get;
2: SELECT *FROM 'table' WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM 'table' ) ORDER BY ID LIMIT Number of data you want to get;
3：SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT The number of pieces of data you want to get;
4: SELECT * FROM 'table'WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM 'table'))) ORDER BY ID LIMIT The number of pieces of data you want to get;
5: SELECT * FROM 'table' WHERE id >= (SELECT floor( RAND() * (((SELECT MAX(id) FROM 'table')-(SELECT MIN(id) FROM 'table')) + (SELECT MIN(id) FROM 'table'))) ORDER BY id LIMIT The number of pieces of data you want to get;
6：SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT the number of pieces of data you want to get;
The query time of 1>> the query time of 2>> the query time of 5> the query time of 6> the query time of 4> the query time of 3, that is3 is the most efficient.

[Source] mysql implements a method of randomly fetching several pieces of data

Related Posts

Sections viewed