在mysql 數(shù)據(jù)庫如何添加隨機(jī)數(shù)字

在mysql中隨機(jī)生成一些范圍內(nèi)的整數(shù)，可以借助mysql提供的二個(gè)函數(shù)來實(shí)現(xiàn)。

雙清網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián),雙清網(wǎng)站設(shè)計(jì)制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為雙清1000多家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\外貿(mào)網(wǎng)站建設(shè)要多少錢，請(qǐng)找那個(gè)售后服務(wù)好的雙清做網(wǎng)站的公司定做！

它們是floor()與rand()函數(shù)，下面分別介紹下。

1、floor(f)

返回一個(gè)不大于f的最大整數(shù)

2、rand(),rand(n)

返回一個(gè)隨機(jī)浮點(diǎn)值 v ，范圍在 0 到1 之間 (即, 其范圍為 0 ≤ v ≤ 1.0)。若已指定一個(gè)整數(shù)參數(shù) N ，則它被用作種子值，用來產(chǎn)生重復(fù)序列。

若要在i ≤ R ≤ j 這個(gè)范圍得到一個(gè)隨機(jī)整數(shù)R ，需要用到表達(dá)式 FLOOR(i + RAND() * (j – i + 1))。

例如， 在7到 12 的范圍（包括7和12）內(nèi)得到一個(gè)隨機(jī)整數(shù)，使用語句：

SELECT FLOOR(7 + (RAND() * 6));

MySQL產(chǎn)生隨機(jī)數(shù)

MYSQL 取隨機(jī)數(shù)

2010年04月26日 星期一 09:48

mysql 取隨機(jī)數(shù)

--對(duì)一個(gè)表取任意隨機(jī)數(shù)

SELECT *

FROM TMP_XF_TEST

WHERE ID = (SELECT FLOOR(RAND() * (SELECT MAX(ID) FROM TMP_XF_TEST)))

order by id LIMIT 1;

--有條件性的取隨機(jī)數(shù)

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 | 玄風(fēng) | 9 | 123 | 2010-04-26 14:56:39 | 2010-04-26 14:56:39 | 玄風(fēng)測(cè)試使用的數(shù)據(jù) |

+-----+-----------+-----+---------+---------------------+---------------------+--------------------+

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 | 玄風(fēng) | 9 | 123 | 2010-04-28 15:47:19 | 2010-04-28 15:47:19 | 玄風(fēng)測(cè)試使用的數(shù)據(jù) | 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 | 玄風(fēng) | 9 | 123 | 2010-04-28 15:47:19 | 2010-04-28 15:47:19 | 玄風(fēng)測(cè)試使用的數(shù)據(jù) |

+------+-----------+-----+---------+---------------------+---------------------+--------------------+

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)

對(duì)應(yīng)的另外一種杯具寫法是：

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;

如何通過java向mysql輸入一個(gè)隨機(jī)數(shù)猜大小

1、首先通過控制臺(tái)一直輸入一個(gè)整數(shù)，比較該數(shù)與系統(tǒng)隨機(jī)生成的那個(gè)數(shù)。

2、其次如果大就輸出“猜大了。”，繼續(xù)輸入。如果小就輸出“猜小了。”。

3、最后繼續(xù)輸入；如果相等就輸出“恭喜，猜對(duì)了?！?，退出輸出。

mysql如何生成不重復(fù)的隨機(jī)數(shù)

mysql的rand函數(shù)可以生成一個(gè)0到1之間的隨機(jī)數(shù)，進(jìn)行一定的放大即可得到一個(gè)隨機(jī)數(shù)。再通過條件查詢來限制新隨機(jī)數(shù)沒有在表中出現(xiàn)過。如下所示：

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

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

LIMIT 1

MYSQL 獲取隨機(jī)數(shù)的問題?。。。。。。。。。。。。。?！

給你提供一個(gè)思路你看是否可行；

1.隨機(jī)數(shù)不用MYSQL生成，用程序（如PHP）生成，范圍在ID的最大和最小之間；

2.根據(jù)生成的隨機(jī)數(shù)去數(shù)據(jù)庫直接查詢，如果沒有就重復(fù)一次操作；