mysql怎么打随机数 mysql 随机取数据

MySQL产生随机数

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;

mysql如何生成不重复的随机数

mysql的rand函数可以生成一个0到1之间的随机数，进行一定的放大即可得到一个随机数。再通过条件查询来限制新随机数没有在表中出现过。如下所示：

SELECT FLOOR(RAND() * 99999) AS random_numFROM numbers

WHERE "random_num" NOT IN (SELECT my_number FROM numbers)

LIMIT 1

mysql怎么生成不重复的随机数

MySql数据库随机函授Rand()可以产生0~1之间带小数的随机数，只要将括号中的参数留空，所产生的数是完全随机的，出现重复的几率不大，但是不可避免会有重复的可能，如果需要完全避免重复，可以先用随机函数产生一堆数字，然后用剔除其中的重复数这个办法来实现。

例如先用循环产生一批随机数并存入数据表中，再用

select distinct colName from tableName;

选出无重复的随机数予以应用

如何通过java向mysql输入一个随机数猜大小

1、首先通过控制台一直输入一个整数，比较该数与系统随机生成的那个数。

2、其次如果大就输出“猜大了。”，继续输入。如果小就输出“猜小了。”。

3、最后继续输入；如果相等就输出“恭喜，猜对了。”，退出输出。