[MySQL]MySQL数据库中从百万条数据表中随机10条数据的查询方式有哪些？

5285 7 1 发布于: 2018-01-17 读完约需2分钟

问题描述

如题，MySQL数据库中从百万条数据表中随机10条数据的查询方式有哪些？

方案一


        登录后复制
        
            
        
     
SELECT name
  FROM random AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

方案二


        登录后复制
        
            
        
     
SELECT *
FROM random, (
        SELECT id AS sid
        FROM random
        ORDER BY RAND( )
        LIMIT 10
    ) tmp
WHERE random.id = tmp.sid;

方案三


        登录后复制
        
            
        
     
SELECT * FROM tbl WHERE id IN 
    (SELECT id FROM (SELECT id FROM tbl ORDER BY RAND() LIMIT 10) t)