mysql随机数怎么写_MYSQL写入随机数语句

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;