mysql随机数怎么设置 mysql生成随机字符串 _SELECT

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: 165888Duplicates: 0Warnings: 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 | 玄风测试使用的数据 |

mysql随机数怎么设置 mysql生成随机字符串

推荐阅读

竞技二打一的基本杀法破釜沉舟杀法

两岁宝宝可以吃香椿吗

小恐龙简笔画画法小恐龙简笔画教程

黄金现货价格最新

关于元宵节的祝福

红枣茯苓粥——清热除烦润泽肌肤

都说物以类聚,你会反对自家孩子结交坏学生吗？

榴莲打开肉是硬的还能放软吗榴莲有酒味是怎么回事还能吃吗!

几何画板证明勾股定理的详细方法几何画板证明勾股定理的详细方法

ipad用什么软件写代码，有可以在ipad上打php编程的软件吗有的请推荐一下

高铁能带水果刀吗乘坐高铁能不能带水果刀

口水鸡配料计口水鸡配料

靠谱的互联网金融平台有哪些推荐？

少年吃辣条＂中毒＂，抢救14小时仍未清醒！

雪花丸子怎么做最好吃窍门雪花丸子怎么做

丽江小倩现在还在丽江吗小倩丽江

word文档转xps格式，docx怎么转换成wps文档

bklal00是什么型号

尼康2470一代二代三代对比尼康2470二代

薇雅属于什么类型的电商公司薇雅属于什么类型的电商，薇雅属于什么类型的电商平台