Question

mysql从数据表中随机提取多条不同记录

Answer 1

mysql 使用 ORDER BY rand() 实现随机获取

mysql> select * FROM sale_report LIMIT 0, 10;
+---------------------+-----------+------------+
| SALE_DATE | SALE_ITEM | SALE_MONEY |
+---------------------+-----------+------------+
| 2009-01-01 00:00:00 | A | 2009.00 |
| 2009-01-01 00:00:00 | B | 1.00 |
| 2009-01-01 00:00:00 | C | 1.00 |
| 2009-01-02 00:00:00 | A | 2009.00 |
| 2009-01-02 00:00:00 | B | 1.00 |
| 2009-01-02 00:00:00 | C | 2.00 |
| 2009-01-03 00:00:00 | A | 2009.00 |
| 2009-01-03 00:00:00 | B | 1.00 |
| 2009-01-03 00:00:00 | C | 3.00 |
| 2009-01-04 00:00:00 | A | 2009.00 |
+---------------------+-----------+------------+
10 rows in set (0.00 sec)

mysql> select * FROM sale_report ORDER BY rand() LIMIT 0, 10;
+---------------------+-----------+------------+
| SALE_DATE | SALE_ITEM | SALE_MONEY |
+---------------------+-----------+------------+
| 2009-11-17 00:00:00 | A | 2009.00 |
| 2009-07-16 00:00:00 | A | 2009.00 |
| 2009-05-20 00:00:00 | C | 20.00 |
| 2009-10-14 00:00:00 | C | 14.00 |
| 2009-01-27 00:00:00 | A | 2009.00 |
| 2009-02-01 00:00:00 | B | 2.00 |
| 2009-07-29 00:00:00 | A | 2009.00 |
| 2009-11-20 00:00:00 | C | 20.00 |
| 2009-05-15 00:00:00 | A | 2009.00 |
| 2009-07-27 00:00:00 | B | 7.00 |
+---------------------+-----------+------------+
10 rows in set (0.00 sec)

Answer 2

根据id主键生成随机函数最为where条件。