首页   注册   登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
V2EX  ›  MySQL

这条 SQL 如何解释?

  •  
  •   skyworker · 2018-10-11 13:10:40 +08:00 · 2644 次点击
    这是一个创建于 398 天前的主题,其中的信息可能已经有所发展或是发生改变。
    如下:

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

    看起来是获取一条随机的 user 数据, 但是真实运行后, 会发现有时候获取 1 条数据; 有时候为空; 有时候会获取 2 条数据. 超出了我对 SQL 运行机制的理解...
    31 回复  |  直到 2018-10-12 08:29:25 +08:00
        1
    jasonyang9   2018-10-11 13:15:27 +08:00
    确定`users`表中各记录`id`字段的值是连续的?
        2
    owenliang   2018-10-11 13:17:48 +08:00 via Android
    风骚
        3
    skyworker   2018-10-11 13:20:06 +08:00
    @jasonyang9 是的,id 连续的, 并且还有出现 3 条数据的可能性
        4
    lichao   2018-10-11 13:23:47 +08:00
    SELECT * FROM users order by RAND() limit 1;
        5
    skyworker   2018-10-11 13:25:18 +08:00
    @lichao 这样操作会全表扫描, 效率大打折扣.
        6
    Youen   2018-10-11 13:30:54 +08:00
    ID 不是 PK? Floor 应该返回单个值的啊..
        7
    zhuawadao   2018-10-11 13:31:49 +08:00
    我运行了一下没发生多记录的情况,是我数据量不够?
        8
    skyworker   2018-10-11 13:32:15 +08:00
    @Youen id 是标准的主键, InnoDB 引擎
        9
    sxw11   2018-10-11 13:34:31 +08:00
    我遇到过一个情况,mysql --》查询条件为 varchar 字段=0 的时候,以 0 开头或者非数字开头的都会查出来。
        10
    skyworker   2018-10-11 13:35:58 +08:00
    @zhuawadao 你多执行几次, 出现多条数据的几率在 10%左右
        11
    rrfeng   2018-10-11 13:36:32 +08:00
    id 是主键也不行啊,会有删除的情况。
        12
    lichao   2018-10-11 13:37:20 +08:00   ♥ 3
    set @id = (SELECT floor(RAND() * (SELECT MAX(id) FROM users)));
    SELECT id, name, email FROM users WHERE id = @id;
        13
    zealzz   2018-10-11 13:37:39 +08:00 via Android   ♥ 1
    小心数据类型的隐式转换和精度丢失,这种情况可能会出现多条。
        14
    walkerliu   2018-10-11 13:38:15 +08:00
    RAND() 返回值为 0 或者返回值过小,乘以你的 MAX ID 最后 floor 取证也是会是 0,你的主键 id 不会为 0 吧 ?多值返回这个有点奇葩
        15
    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 开始且连续,查询结果的数目也是不确定的。
        16
    yesterdaysun   2018-10-11 13:38:24 +08:00
    实测可以重现, Mysql 5.7 随便找了 26w 条数据的一张表, 确定主键是唯一的, 0~4 条的情况都有, 目测最多的是 0 条,1 条和 2 条的情况, 但是无法解释为什么, 颠覆了我的认知, 我也想知道这个是 bug 还是 feature
        17
    kevin2ex   2018-10-11 13:41:14 +08:00
    试了一下,很🐂
        18
    zhuawadao   2018-10-11 13:41:43 +08:00
    @skyworker 对的,可以重现!坐等大神布道。
        19
    zealzz   2018-10-11 13:42:56 +08:00 via Android
    这是我以前关于数据类型隐式转换的提问,你可以参考对照一下看看是不是同样的问题。
    https://stackoverflow.com/questions/46235729/implicit-conversion-of-a-numeric-in-mysql
        20
    kenorizon   2018-10-11 13:44:39 +08:00
    @kenorizon #15
    再尝试试了几遍,貌似是 where 的条件里面有 select 的话会多次计算 (SELECT floor(RAND() * (SELECT MAX(id) FROM users)))
        21
    yesterdaysun   2018-10-11 13:45:49 +08:00
    刚看到 kenorizon 说的, 我觉得有道理, 应该是因为这个是非相关子查询, 但是因为里面含有 rand(), 所以这个子查询不会像普通的一样只跑一遍, 而是每一行都跑了, 所以实际查询的是所有记录里刚好可以和随机出来的那个 id 相等的记录, 所以条数不等, 而且数据量少估计难重现
        22
    kenorizon   2018-10-11 13:46:20 +08:00
    @kenorizon #20 这一层的请无视吧,刚刚测试次数太少 没测全= =
        23
    pabupa   2018-10-11 13:48:36 +08:00
    我这里也是,,测试数据库只有 40 条数据。。。
        24
    lichao   2018-10-11 13:49:15 +08:00
    @yesterdaysun 貌似是的,所以拆分成两条语句可以规避。。
        25
    pabupa   2018-10-11 13:51:54 +08:00
    @lichao #12 应该是 mysql 多次计算随机数了,,这样是可以的。
        26
    kevin2ex   2018-10-11 13:53:03 +08:00   ♥ 1
    改成这样,没有发现多条数据。
    SELECT b.id, a.* FROM users a, (SELECT floor(RAND() * (SELECT MAX(id) FROM users)) as id ) b WHERE a.id = b.id;
        27
    will0404   2018-10-11 13:59:50 +08:00   ♥ 3
    > "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
        28
    mossss21   2018-10-11 14:08:07 +08:00
    每行都做了一次 RAND(),理论上查询结果最多会有 COUNT(id)条,最少 0 条
        29
    chenqimiao   2018-10-11 16:35:53 +08:00
    神奇
        30
    winsky   2018-10-11 23:05:52 +08:00
    这么神奇,国庆前我身边刚好也有人遇到过这个问题。
    也可以看看这个 https://bugs.mysql.com/bug.php?id=86624
        31
    liuxey   2018-10-12 08:29:25 +08:00
    #12 + #28 结合起来就解释了楼主的疑问
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   3258 人在线   最高记录 5043   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 27ms · UTC 10:19 · PVG 18:19 · LAX 02:19 · JFK 05:19
    ♥ Do have faith in what you're doing.