首页   注册   登录
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 · 4 天前 · 1903 次点击
    如下:

    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   4 天前
    确定`users`表中各记录`id`字段的值是连续的?
        2
    owenliang   4 天前 via Android
    风骚
        3
    skyworker   4 天前
    @jasonyang9 是的,id 连续的, 并且还有出现 3 条数据的可能性
        4
    lichao   4 天前
    SELECT * FROM users order by RAND() limit 1;
        5
    skyworker   4 天前
    @lichao 这样操作会全表扫描, 效率大打折扣.
        6
    Youen   4 天前
    ID 不是 PK? Floor 应该返回单个值的啊..
        7
    zhuawadao   4 天前
    我运行了一下没发生多记录的情况,是我数据量不够?
        8
    skyworker   4 天前
    @Youen id 是标准的主键, InnoDB 引擎
        9
    sxw11   4 天前
    我遇到过一个情况,mysql --》查询条件为 varchar 字段=0 的时候,以 0 开头或者非数字开头的都会查出来。
        10
    skyworker   4 天前
    @zhuawadao 你多执行几次, 出现多条数据的几率在 10%左右
        11
    rrfeng   4 天前
    id 是主键也不行啊,会有删除的情况。
        12
    lichao   4 天前   ♥ 3
    set @id = (SELECT floor(RAND() * (SELECT MAX(id) FROM users)));
    SELECT id, name, email FROM users WHERE id = @id;
        13
    zealzz   4 天前 via Android   ♥ 1
    小心数据类型的隐式转换和精度丢失,这种情况可能会出现多条。
        14
    walkerliu   4 天前
    RAND() 返回值为 0 或者返回值过小,乘以你的 MAX ID 最后 floor 取证也是会是 0,你的主键 id 不会为 0 吧 ?多值返回这个有点奇葩
        15
    kenorizon   4 天前
    https://stackoverflow.com/questions/45656145/rand-in-where-clause-in-mysql

    应该是进行查询的时候,对数据库中的每一行都会重新计算一遍 floor(RAND() * (SELECT MAX(id) FROM users))
    所以即使 id 字段的值从 0 开始且连续,查询结果的数目也是不确定的。
        16
    yesterdaysun   4 天前
    实测可以重现, Mysql 5.7 随便找了 26w 条数据的一张表, 确定主键是唯一的, 0~4 条的情况都有, 目测最多的是 0 条,1 条和 2 条的情况, 但是无法解释为什么, 颠覆了我的认知, 我也想知道这个是 bug 还是 feature
        17
    kevin2ex   4 天前
    试了一下,很🐂
        18
    zhuawadao   4 天前
    @skyworker 对的,可以重现!坐等大神布道。
        19
    zealzz   4 天前 via Android
    这是我以前关于数据类型隐式转换的提问,你可以参考对照一下看看是不是同样的问题。
    https://stackoverflow.com/questions/46235729/implicit-conversion-of-a-numeric-in-mysql
        20
    kenorizon   4 天前
    @kenorizon #15
    再尝试试了几遍,貌似是 where 的条件里面有 select 的话会多次计算 (SELECT floor(RAND() * (SELECT MAX(id) FROM users)))
        21
    yesterdaysun   4 天前
    刚看到 kenorizon 说的, 我觉得有道理, 应该是因为这个是非相关子查询, 但是因为里面含有 rand(), 所以这个子查询不会像普通的一样只跑一遍, 而是每一行都跑了, 所以实际查询的是所有记录里刚好可以和随机出来的那个 id 相等的记录, 所以条数不等, 而且数据量少估计难重现
        22
    kenorizon   4 天前
    @kenorizon #20 这一层的请无视吧,刚刚测试次数太少 没测全= =
        23
    pabupa   4 天前
    我这里也是,,测试数据库只有 40 条数据。。。
        24
    lichao   4 天前
    @yesterdaysun 貌似是的,所以拆分成两条语句可以规避。。
        25
    pabupa   4 天前
    @lichao #12 应该是 mysql 多次计算随机数了,,这样是可以的。
        26
    kevin2ex   4 天前   ♥ 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   4 天前   ♥ 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   4 天前
    每行都做了一次 RAND(),理论上查询结果最多会有 COUNT(id)条,最少 0 条
        29
    chenqimiao   4 天前
    神奇
        30
    winsky   4 天前
    这么神奇,国庆前我身边刚好也有人遇到过这个问题。
    也可以看看这个 https://bugs.mysql.com/bug.php?id=86624
        31
    liuxey   3 天前
    #12 + #28 结合起来就解释了楼主的疑问
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1066 人在线   最高记录 3762   ·  
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.1 · 20ms · UTC 23:40 · PVG 07:40 · LAX 16:40 · JFK 19:40
    ♥ Do have faith in what you're doing.
    沪ICP备16043287号-1