这条 SQL 如何解释?

如下:

SELECT * FROM users WHERE id = (SELECT floor(RAND() * (SELECT MAX(id) FROM users)))

看起来是获取一条随机的 user 数据, 但是真实运行后, 会发现有时候获取 1 条数据; 有时候为空; 有时候会获取 2 条数据. 超出了我对 SQL 运行机制的理解...

select

SQL

users

from

31 replies • 2018-10-12 08:29:25 +08:00

jasonyang9

Oct 11, 2018

确定`users`表中各记录`id`字段的值是连续的？

owenliang

Oct 11, 2018 via Android

风骚

skyworker

Oct 11, 2018

@jasonyang9 是的,id 连续的, 并且还有出现 3 条数据的可能性

lichao

Oct 11, 2018

SELECT * FROM users order by RAND() limit 1;

skyworker

Oct 11, 2018

@lichao 这样操作会全表扫描, 效率大打折扣.

Youen

Oct 11, 2018

ID 不是 PK? Floor 应该返回单个值的啊..

zhuawadao

Oct 11, 2018

我运行了一下没发生多记录的情况，是我数据量不够？

skyworker

Oct 11, 2018

@Youen id 是标准的主键, InnoDB 引擎

sxw11

Oct 11, 2018

我遇到过一个情况，mysql --》查询条件为 varchar 字段=0 的时候，以 0 开头或者非数字开头的都会查出来。

skyworker

Oct 11, 2018

@zhuawadao 你多执行几次, 出现多条数据的几率在 10%左右

rrfeng

Oct 11, 2018

id 是主键也不行啊，会有删除的情况。

lichao

Oct 11, 2018

set @id = (SELECT floor(RAND() * (SELECT MAX(id) FROM users)));
SELECT id, name, email FROM users WHERE id = @id;

zealzz

Oct 11, 2018 via Android

小心数据类型的隐式转换和精度丢失，这种情况可能会出现多条。

walkerliu

Oct 11, 2018

RAND() 返回值为 0 或者返回值过小，乘以你的 MAX ID 最后 floor 取证也是会是 0，你的主键 id 不会为 0 吧？多值返回这个有点奇葩

kenorizon

Oct 11, 2018

https://stackoverflow.com/questions/45656145/rand-in-where-clause-in-mysql

应该是进行查询的时候，对数据库中的每一行都会重新计算一遍 floor(RAND() * (SELECT MAX(id) FROM users))
所以即使 id 字段的值从 0 开始且连续，查询结果的数目也是不确定的。

yesterdaysun

Oct 11, 2018

实测可以重现, Mysql 5.7 随便找了 26w 条数据的一张表, 确定主键是唯一的, 0~4 条的情况都有, 目测最多的是 0 条,1 条和 2 条的情况, 但是无法解释为什么, 颠覆了我的认知, 我也想知道这个是 bug 还是 feature

kevin2ex

Oct 11, 2018

试了一下，很🐂

zhuawadao

Oct 11, 2018

@skyworker 对的，可以重现！坐等大神布道。

zealzz

Oct 11, 2018 via Android

这是我以前关于数据类型隐式转换的提问，你可以参考对照一下看看是不是同样的问题。
https://stackoverflow.com/questions/46235729/implicit-conversion-of-a-numeric-in-mysql

kenorizon

Oct 11, 2018

@kenorizon #15
再尝试试了几遍，貌似是 where 的条件里面有 select 的话会多次计算 (SELECT floor(RAND() * (SELECT MAX(id) FROM users)))

yesterdaysun

Oct 11, 2018

刚看到 kenorizon 说的, 我觉得有道理, 应该是因为这个是非相关子查询, 但是因为里面含有 rand(), 所以这个子查询不会像普通的一样只跑一遍, 而是每一行都跑了, 所以实际查询的是所有记录里刚好可以和随机出来的那个 id 相等的记录, 所以条数不等, 而且数据量少估计难重现

kenorizon

Oct 11, 2018

@kenorizon #20 这一层的请无视吧，刚刚测试次数太少没测全= =

pabupa

Oct 11, 2018

我这里也是，，测试数据库只有 40 条数据。。。

lichao

Oct 11, 2018

@yesterdaysun 貌似是的，所以拆分成两条语句可以规避。。

pabupa

Oct 11, 2018

@lichao #12 应该是 mysql 多次计算随机数了，，这样是可以的。

kevin2ex

Oct 11, 2018

改成这样，没有发现多条数据。
SELECT b.id, a.* FROM users a, (SELECT floor(RAND() * (SELECT MAX(id) FROM users)) as id ) b WHERE a.id = b.id;

will0404

Oct 11, 2018

> "RAND() in a WHERE clause is evaluated for every row (when selecting from one table) or combination of rows (when selecting from a multiple-table join). Thus, for optimizer purposes, RAND() is not a constant value and cannot be used for index optimizations"

See: https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_rand

mossss21

Oct 11, 2018

每行都做了一次 RAND()，理论上查询结果最多会有 COUNT(id)条，最少 0 条

chenqimiao

Oct 11, 2018

神奇

winsky

Oct 11, 2018

这么神奇，国庆前我身边刚好也有人遇到过这个问题。
也可以看看这个 https://bugs.mysql.com/bug.php?id=86624

auin

Oct 12, 2018

#12 + #28 结合起来就解释了楼主的疑问