mysql 返回随机行_MySQL从表中随机返回n行数据

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

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

scott建表及录入数据sql脚本

问题:

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

select ename,job from emp;

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；

select ename,job from emp order by rand() limit 5;

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)