无法从 MySQL 表中选择单个随机行 [英] Trouble selecting a single random row from MySQL table

问题描述

为了生成样本数据，我试图从一个非常简单的表中随机选择一个用户.

I am trying to select a user at random from a very simple table for the purposes of generating sample data.

该表只有两列，整数主键 users_id 包含从 1 到 46 的所有值，uname 是一个 varchar(60).

The table has just two columns, the integer primary key users_id which has all the values from 1 to 46 inclusive, and uname which is a varchar(60).

查询

select relusers.uname from relusers where relusers.users_id=floor(rand()*46+1);

正在返回多行.也许我已经盯着这个看太久了，但我看不出上面的查询是如何返回多于一行的.floor() 返回一个与主键列进行比较的整数.在选择中包含 users_id 表示选择了多个不同的 ID.结果是零行我可以理解，但有多行?有什么想法吗?

is returning multiple rows. Perhaps I've been staring at this for too long but I fail to see how the above query could ever return more than one row. floor() returns a single integer which is being compared to the primary key column. Including users_id in the selection shows multiple different IDs being selected. Zero rows as a result I can understand, but multiple? Any ideas?

推荐答案

您的代码返回多行，因为 rand() 对每一行求值.所以，你有多个匹配的变化.并且根本没有匹配的可能性.

Your code is returning multiple rows because rand() is evaluated on each row. So, you have the change of multiple matches. And a chance of no matches at all.

您可以使用您的想法，但可以这样尝试:

You can use your idea, but try it this way:

select relusers.uname
from relusers cross join
     (selext @rand := rand()) const
where relusers.users_id = floor(@rand*46+1);

这仅生成一个随机值，因此仅生成一行.但是，只有 46 行，order by 方法应该表现得足够好:

This generates just one random value and hence just one row. But, with only 46 rows, the order by method should perform well enough:

select relusers.uname
from relusers
order by rand()
limit 1;

这篇关于无法从 MySQL 表中选择单个随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！