这条 SQL 如何解释?

如下:

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

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

select

SQL

users

from

31 条回复 • 2018-10-12 08:29:25 +08:00

jasonyang9

2018-10-11 13:15:27 +08:00

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

owenliang

2018-10-11 13:17:48 +08:00 via Android

风骚

skyworker

2018-10-11 13:20:06 +08:00

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

lichao

2018-10-11 13:23:47 +08:00

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

skyworker

2018-10-11 13:25:18 +08:00

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

Youen

2018-10-11 13:30:54 +08:00

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

zhuawadao

2018-10-11 13:31:49 +08:00

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

skyworker

2018-10-11 13:32:15 +08:00

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

sxw11

2018-10-11 13:34:31 +08:00

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

skyworker

2018-10-11 13:35:58 +08:00

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

rrfeng

2018-10-11 13:36:32 +08:00

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

lichao

2018-10-11 13:37:20 +08:00

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

zealzz

2018-10-11 13:37:39 +08:00 via Android

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

walkerliu

2018-10-11 13:38:15 +08:00

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

kenorizon

2018-10-11 13:38:19 +08:00

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

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

yesterdaysun

2018-10-11 13:38:24 +08:00

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

kevin2ex

2018-10-11 13:41:14 +08:00

试了一下，很🐂

zhuawadao

2018-10-11 13:41:43 +08:00

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

zealzz

2018-10-11 13:42:56 +08:00 via Android

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

kenorizon

2018-10-11 13:44:39 +08:00

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

yesterdaysun

2018-10-11 13:45:49 +08:00

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

kenorizon

2018-10-11 13:46:20 +08:00

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

pabupa

2018-10-11 13:48:36 +08:00

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

lichao

2018-10-11 13:49:15 +08:00

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

pabupa

2018-10-11 13:51:54 +08:00

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

kevin2ex

2018-10-11 13:53:03 +08:00

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

will0404

2018-10-11 13:59:50 +08:00

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

2018-10-11 14:08:07 +08:00

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

chenqimiao

2018-10-11 16:35:53 +08:00

神奇

winsky

2018-10-11 23:05:52 +08:00

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

liuxey

2018-10-12 08:29:25 +08:00

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