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)