正文
mysql随机数怎么写 mysql 随机字符串
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
MYSQL写入随机数语句
declare @i int
select @i=count(*) from A
while @i0
begin
Update A set B=ceiling(rand()*150+50) where id = @i
set @i = @i-1
end
id 是表A里mysql随机数怎么写的自增长列mysql随机数怎么写,不清楚mysql随机数怎么写你的表里有没有mysql随机数怎么写,若是没有的话mysql随机数怎么写,可以自己造个临时表,插入数据。
我想问下,用MySQL怎么将一段随机数放进表中,比如玩游戏的属性值,升
你可以用rand(0,100)这样生成随机数mysql随机数怎么写,然后连接数据库inter添加进数据库就行了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;
请问php+mysql中如何从数据表student中随机抽取若干条(例如是五条数据)数据?
直接用select * from student order by rand() limit 5不是一个很好的习惯,这个要考虑到数据库的大小问题,比如,数据量为10W以上,这样查询的效率是非常低的,资源也消耗挺多!你可以考虑在php与mysql结合来实现,比如:先用mysql 的count()计算出student的总条数$sum_num,赋值到php中的变量中,后在php中更具这个总条数进行生成随机数(这里涉及到你的5条数据是否为连续的5条随机数,还是打乱的随机五条),这里如果生成连续的数据就只需要生成一个小于$sum_num-5的随机数就可以了!根据这个随机数进行limit取出5条就可以;如果这里需要生成打乱的随机数,就需要生成5个不相同的并且小于$sum_num随机数,最后根据这些随机数在数据库中查询,第一种情况用到limit 随机数,5;;第二种情况用到in(随机数1,随机数2,随机数3,随机数4,随机数5);在数据量大的情况下,这样的效率和资源消耗的情况下都要比select * from student order by rand() limit 5好得多,当然如果数据量不多的话就用这个也可以!只是给你普及个知识!祝你学习愉快!(这里暂且认为你会用php生成随机数并且这里的php生成的随机数必须为大于0的整型哦,不会的话可以先百度、google,最后可以继续给我留言)!
这里因为你的id会有缺失所以有的方法不适用!就不一一列举了!
MYSQL 获取随机数的问题!!!!!!!!!!!!!!!
给你提供一个思路你看是否可行;
1.随机数不用MYSQL生成,用程序(如PHP)生成,范围在ID的最大和最小之间;
2.根据生成的随机数去数据库直接查询,如果没有就重复一次操作;
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 随机字符串、mysql随机数怎么写的信息别忘了在本站进行查找喔。