取的行数对时间的影响并不大。mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.00 sec)mysql> select * from t1 order by rand() limit 1; +-------+-------+ | id | col | +-------+-------+ | 80454 | 80454 | +-------+-------+ 1 row in set (0.06 sec)mysql> select * from t1 order by rand() limit 2; +-------+-------+ | id | col | +-------+-------+ | 26761 | 26761 | | 96949 | 96949 | +-------+-------+ 2 rows in set (0.06 sec)mysql> select * from t1 order by rand() limit 10; +-------+-------+ | id | col | +-------+-------+ | 98500 | 98500 | | 34974 | 34974 | | 13382 | 13382 | | 12110 | 12110 | | 39041 | 39041 | | 93148 | 93148 | | 52928 | 52928 | | 71628 | 71628 | | 90595 | 90595 | | 9149 | 9149 | +-------+-------+ 10 rows in set (0.08 sec)mysql> select * from t1 order by rand() limit 1000; +-------+-------+ | id | col | +-------+-------+ .... .... .... | 3868 | 3868 | | 67071 | 67071 | | 12535 | 12535 | | 57604 | 57604 | | 13389 | 13389 | | 33876 | 33876 | | 65096 | 65096 | | 51892 | 51892 | +-------+-------+ 1000 rows in set (0.06 sec)
或者你可以1. select a.* from t1 a,(select id from t1 order by rand() limit 10) b where a.id=b.id;2. 通过你的程序，生成一个 1000 个不重复的随机的ID号，然后 select * from t1 where id in (1,4,99,8143,34...);
通过你的程序，生成一个 1000 个不重复的随机的ID号，然后 select * from t1 where id in (1,4,99,8143,34...); ---------------------------------------------- 这方法不错
--不重复是什么意思： --是这个表中本身有很多重复的记录，但是你现在要取其中的任意N条不重复的记录， --是这个意思吗？ --是这样的话，那就好办了 select * from ( select distinct col1, col2, ... , coln ) t orader by rand() limint m;
数万数据中取N条不重复的数据的SQL语言---------------- 你这个不重复，不知道是不是指针对唯一键来说的，如果是的话，那通过索引覆盖很容易做到(假设唯一列名为rownum)：select * from tb_name a,(select rownum from tb_name order by rand() limit N) b where a.rownum=b.rownum;
+-------+-------+
| id | col |
+-------+-------+
| 23628 | 23628 |
| 75039 | 75039 |
| 30822 | 30822 |
| 63538 | 63538 |
| 70975 | 70975 |
| 67497 | 67497 |
| 79231 | 79231 |
| 78973 | 78973 |
| 98383 | 98383 |
| 87039 | 87039 |
+-------+-------+
10 rows in set (0.25 sec)mysql>
还有没有更优的~
建议你自己试一下，效率并不象你想象的那样。
mysql> select * from t1 order by rand() limit 100;
+-------+-------+
| id | col |
+-------+-------+
| 84657 | 84657 |
| 6555 | 6555 |
| 55472 | 55472 |
| 54947 | 54947 |
| 14070 | 14070 |
| 23510 | 23510 |
| 25233 | 25233 |
| 71179 | 71179 |
| 12166 | 12166 |
| 44937 | 44937 |
| 62460 | 62460 |
| 72147 | 72147 |
| 30125 | 30125 |
| 99478 | 99478 |
| 72657 | 72657 |
| 11536 | 11536 |
| 51618 | 51618 |
| 51020 | 51020 |
| 66033 | 66033 |
| 26626 | 26626 |
| 48541 | 48541 |
| 87082 | 87082 |
| 57887 | 57887 |
| 3102 | 3102 |
| 10933 | 10933 |
| 28481 | 28481 |
| 60150 | 60150 |
| 98052 | 98052 |
| 25788 | 25788 |
| 37884 | 37884 |
| 99483 | 99483 |
| 78563 | 78563 |
| 39900 | 39900 |
| 49793 | 49793 |
| 75354 | 75354 |
| 92075 | 92075 |
| 66752 | 66752 |
| 69764 | 69764 |
| 24083 | 24083 |
| 57289 | 57289 |
| 56105 | 56105 |
| 58027 | 58027 |
| 27211 | 27211 |
| 38082 | 38082 |
| 24148 | 24148 |
| 30155 | 30155 |
| 15611 | 15611 |
| 52078 | 52078 |
| 3994 | 3994 |
| 62898 | 62898 |
| 74889 | 74889 |
| 18401 | 18401 |
| 83604 | 83604 |
| 80441 | 80441 |
| 36961 | 36961 |
| 93259 | 93259 |
| 76917 | 76917 |
| 82835 | 82835 |
| 30264 | 30264 |
| 46449 | 46449 |
| 33721 | 33721 |
| 49348 | 49348 |
| 51571 | 51571 |
| 68946 | 68946 |
| 49335 | 49335 |
| 78169 | 78169 |
| 53684 | 53684 |
| 5767 | 5767 |
| 93072 | 93072 |
| 9040 | 9040 |
| 66473 | 66473 |
| 77296 | 77296 |
| 1181 | 1181 |
| 9513 | 9513 |
| 32412 | 32412 |
| 34921 | 34921 |
| 55591 | 55591 |
| 48989 | 48989 |
| 58140 | 58140 |
| 63574 | 63574 |
| 79301 | 79301 |
| 79791 | 79791 |
| 88547 | 88547 |
| 71895 | 71895 |
| 67480 | 67480 |
| 3193 | 3193 |
| 82183 | 82183 |
| 43800 | 43800 |
| 79330 | 79330 |
| 79438 | 79438 |
| 5809 | 5809 |
| 14607 | 14607 |
| 53574 | 53574 |
| 17542 | 17542 |
| 82136 | 82136 |
| 61740 | 61740 |
| 72574 | 72574 |
| 16823 | 16823 |
| 20718 | 20718 |
| 30842 | 30842 |
+-------+-------+
100 rows in set (0.13 sec)mysql>
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.00 sec)mysql> select * from t1 order by rand() limit 1;
+-------+-------+
| id | col |
+-------+-------+
| 80454 | 80454 |
+-------+-------+
1 row in set (0.06 sec)mysql> select * from t1 order by rand() limit 2;
+-------+-------+
| id | col |
+-------+-------+
| 26761 | 26761 |
| 96949 | 96949 |
+-------+-------+
2 rows in set (0.06 sec)mysql> select * from t1 order by rand() limit 10;
+-------+-------+
| id | col |
+-------+-------+
| 98500 | 98500 |
| 34974 | 34974 |
| 13382 | 13382 |
| 12110 | 12110 |
| 39041 | 39041 |
| 93148 | 93148 |
| 52928 | 52928 |
| 71628 | 71628 |
| 90595 | 90595 |
| 9149 | 9149 |
+-------+-------+
10 rows in set (0.08 sec)mysql> select * from t1 order by rand() limit 1000;
+-------+-------+
| id | col |
+-------+-------+
....
....
....
| 3868 | 3868 |
| 67071 | 67071 |
| 12535 | 12535 |
| 57604 | 57604 |
| 13389 | 13389 |
| 33876 | 33876 |
| 65096 | 65096 |
| 51892 | 51892 |
+-------+-------+
1000 rows in set (0.06 sec)
或者你可以1. select a.*
from t1 a,(select id from t1 order by rand() limit 10) b
where a.id=b.id;2. 通过你的程序，生成一个 1000 个不重复的随机的ID号，然后
select * from t1 where id in (1,4,99,8143,34...);
select * from t1 where id in (1,4,99,8143,34...);
----------------------------------------------
这方法不错
--是这个表中本身有很多重复的记录，但是你现在要取其中的任意N条不重复的记录，
--是这个意思吗？
--是这样的话，那就好办了
select * from (
select distinct col1, col2, ... , coln ) t
orader by rand() limint m;
你这个不重复，不知道是不是指针对唯一键来说的，如果是的话，那通过索引覆盖很容易做到(假设唯一列名为rownum)：select * from tb_name a,(select rownum from tb_name order by rand() limit N) b where a.rownum=b.rownum;