2019-01-10 回答
mysql 取随机数
2010年04月26日 星期一 09:48
mysql 取随机数
--对一个表取任意随机数
select *
from tmp_xf_test
where id >= (select floor(rand() * (select max(id) from tmp_xf_test)))
order by id limit 1;
--有条件性的取随机数
select *
from tmp_xf_test
where id >= (select floor(rand() *
((select max(id) from tmp_xf_test where gid = 9) -
(select min(id) from tmp_xf_test where gid = 9))) +
(select min(id) from tmp_xf_test where gid = 9))
and gid = 9
order by id limit 1;
--gid上存在索引
或者
select *
from tmp_xf_test as t1 join
(select round(rand() * ((select max(id) from tmp_xf_test where gid = 9)-(select min(id) from tmp_xf_test where gid = 9))
+(select min(id) from tmp_xf_test where gid = 9)) as id) as t2
where t1.id >= t2.id and t1.gid = 9
order by t1.id limit 1;
#########
不要用下面的杯具写法
mysql> insert into tmp_xf_test(user_nick,gid,item_id,gmt_create,gmt_modified,memo)
-> select user_nick,gid,item_id,gmt_create,gmt_modified,memo from tmp_xf_test;
query ok, 165888 rows affected (9.65 sec)
records: 165888 duplicates: 0 warnings: 0
mysql> select *
-> from `tmp_xf_test`
-> where id >= (select floor( max(id) * rand()) from `tmp_xf_test` )
-> order by id limit 1;
+-----+-----------+-----+---------+---------------------+---------------------+--------------------+
| id | user_nick | gid | item_id | gmt_create | gmt_modified | memo |
+-----+-----------+-----+---------+---------------------+---------------------+--------------------+
| 467 | 玄风 | 9 | 123 | 2010-04-26 14:56:39 | 2010-04-26 14:56:39 | 玄风测试使用的数据 |
+-----+-----------+-----+---------+---------------------+---------------------+--------------------+
1 row in set (51.12 sec)
mysql> explain select *
-> from `tmp_xf_test`
-> where id >= (select floor( max(id) * rand()) from `tmp_xf_test` )
-> order by id limit 1\g
*************************** 1. row ***************************
id: 1
select_type: primary
table: tmp_xf_test
type: index
possible_keys: null
key: primary
key_len: 8
ref: null
rows: 1
extra: using where
*************************** 2. row ***************************
id: 2
select_type: uncacheable subquery
table: tmp_xf_test
type: index
possible_keys: null
key: idx_tmp_xf_test_gid
key_len: 4
ref: null
rows: 331954
extra: using index
2 rows in set (0.01 sec)
---
mysql> select * from `tmp_xf_test` t1 join
-> (select floor( max(id) * rand()) as id from `tmp_xf_test` ) as t2
-> where t1.id >=t2.id
-> order by t1.id limit 1;
+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+
| id | user_nick | gid | item_id | gmt_create | gmt_modified | memo | id |
+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+
| 40311 | 玄风 | 9 | 123 | 2010-04-28 15:47:19 | 2010-04-28 15:47:19 | 玄风测试使用的数据 | 40311 |
+-------+-----------+-----+---------+---------------------+---------------------+--------------------+-------+
1 row in set (0.14 sec)
##############
mysql> select * from `tmp_xf_test`
-> where id >= (select floor(rand() * (select max(id) from `tmp_xf_test`)))
-> order by id limit 1;
+------+-----------+-----+---------+---------------------+---------------------+--------------------+
| id | user_nick | gid | item_id | gmt_create | gmt_modified | memo |
+------+-----------+-----+---------+---------------------+---------------------+--------------------+
| 1352 | 玄风 | 9 | 123 | 2010-04-28 15:47:19 | 2010-04-28 15:47:19 | 玄风测试使用的数据 |
+------+-----------+-----+---------+---------------------+---------------------+--------------------+
1 row in set (0.00 sec)
mysql> explain select * from `tmp_xf_test`
-> where id >= (select floor(rand() * (select max(id) from `tmp_xf_test`)))
-> order by id limit 1\g
*************************** 1. row ***************************
id: 1
select_type: primary
table: tmp_xf_test
type: index
possible_keys: null
key: primary
key_len: 8
ref: null
rows: 1
extra: using where
*************************** 2. row ***************************
id: 3
select_type: subquery
table: null
type: null
possible_keys: null
key: null
key_len: null
ref: null
rows: null
extra: select tables optimized away
2 rows in set, 1 warning (0.00 sec)
对应的另外一种杯具写法是:
select *
from tmp_xf_test
where id >= (select floor(rand() * (max(id) - min(id))) + min(id) mid
from tmp_xf_test
where gid = 9)
and gid = 9 limit 1;