MySQL产生随机数 mysql中随机生成整数的建表语句

MySQL产生随机数

MYSQL 取随机数
2010年04月26日 星期一 09:4mysql 取随机数

--对一个表取任意随机数
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 = 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 tWHERE t1.id >= t2.id AND t1.GID = 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 1G
*************************** 1. row ***************************
id: select_type: PRIMARY
table: tmp_xf_test
type: index
possible_keys: NULL
key: PRIMARY
key_len: ref: NULL
rows: Extra: Using where
*************************** 2. row ***************************
id: select_type: UNCACHEABLE SUBQUERY
table: tmp_xf_test
type: index
possible_keys: NULL
key: idx_tmp_xf_test_gid
key_len: ref: NULL
rows: 33195Extra: 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 t-> 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 1G
*************************** 1. row ***************************
id: select_type: PRIMARY
table: tmp_xf_test
type: index
possible_keys: NULL
key: PRIMARY
key_len: ref: NULL
rows: Extra: Using where
*************************** 2. row ***************************
id: 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中随机生成整数的建表语句

建立一个单字段的表：
create table test select round(round(rand(),4)*1000)
或者在更新字段的时候：
update test set b=round(round(rand(),4)*1000)

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
选出无重复的随机数予以应用

数据库怎么让一列生成随机数

可以用存储过程啊
就可以实现啊
语句也行啊
MYSQL语法

SELECT FLOOR(7   (RAND() * 6))你先建立一个表
然后在插入数据
UPDATE `news_conta_all_20141104` SET memo4=CEIL(RAND()*199)

mysql在类中每行填入一个随机数

在mysql调用rand函数生成随机数，sql代码示例：
UPDATE `表名` SET `字段名`=ceiling(rand()*500000 500000) WHERE (条件)