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
skyworker
V2EX  ›  MySQL

这条 SQL 如何解释?

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

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