这条 SQL 如何解释?

[skyworker]

如下:

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

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

[jasonyang9]

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

[owenliang]

风骚

[skyworker]

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

[lichao]

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

[skyworker]

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

[Youen]

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

[zhuawadao]

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

[skyworker]

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

[sxw11]

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

[skyworker]

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

[rrfeng]

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

[lichao]

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

[zealzz]

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

[walkerliu]

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

[kenorizon]

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

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

[yesterdaysun]

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

[kevin2ex]

试了一下，很🐂

[zhuawadao]

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

[zealzz]

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

[kenorizon]

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

[yesterdaysun]

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

[kenorizon]

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

[pabupa]

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

[lichao]

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

[pabupa]

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

[kevin2ex]

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

[will0404]

> "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]

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

[chenqimiao]

神奇

[winsky]

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

[auin]

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