MySQL从表中随机返回n行数据

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:

scott建表及录入数据sql脚本

问题:

从表中随机返回n条记录。可以修改下面的语句，要求下次执行时产生不同的结果集。

mysql> select ename,job from emp;
+--------+-----------+
| ename  | job       |
+--------+-----------+
| SMITH  | CLERK     |
| ALLEN  | SALESMAN  |
| WARD   | SALESMAN  |
| JONES  | MANAGER   |
| MARTIN | SALESMAN  |
| BLAKE  | MANAGER   |
| CLARK  | MANAGER   |
| SCOTT  | ANALYST   |
| KING   | PRESIDENT |
| TURNER | SALESMAN  |
| ADAMS  | CLERK     |
| JAMES  | CLERK     |
| FORD   | ANALYST   |
| MILLER | CLERK     |
+--------+-----------+
14 rows in set (0.00 sec)
           

解决方案:

同时使用内置的rand函数、limit和order by；

mysql> select ename,job from emp order by rand() limit 5;
+-------+-----------+
| ename | job       |
+-------+-----------+
| JAMES | CLERK     |
| FORD  | ANALYST   |
| KING  | PRESIDENT |
| CLARK | MANAGER   |
| ADAMS | CLERK     |
+-------+-----------+
5 rows in set (0.00 sec)
-- 第二次执行与第一次结果集不同
mysql> select ename,job from emp order by rand() limit 5;
+--------+----------+
| ename  | job      |
+--------+----------+
| SMITH  | CLERK    |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| JAMES  | CLERK    |
+--------+----------+
5 rows in set (0.00 sec)