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

请帮忙看看这两条 MySQL 语句问题出在何处?

  •  
  •   wangyu1314 · 2016-07-26 22:02:44 +08:00 · 3388 次点击
    这是一个创建于 3044 天前的主题,其中的信息可能已经有所发展或是发生改变。
    UPDATE user,thread SET money=0.5*money WHERE user.userid = thread.postuserid AND ((SELECT dateline FROM thread ORDER BY dateline DESC LIMIT 0,1) BETWEEN UNIX_TIMESTAMP(DATE_SUB(DATE(SYSDATE()), INTERVAL 1 MONTH)) AND UNIX_TIMESTAMP(DATE_SUB(DATE(SYSDATE()), INTERVAL 4 MONTH))) AND thread.forumid IN (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59) AND user.membergroupids !=27 AND user.money >0;
    上条语句的意义,查询在技术区发的新主题的时间,如果晚于当前时间 1 - 4 个月的,会员金币缩减 50%
    这条语句能运行, 但是没有效果,问题出在这里, user.userid = thread.postuserid AND ((SELECT dateline FROM thread ORDER BY dateline DESC LIMIT 0,1) 我搞不定了,水平有限,请帮忙,这个条件是取出 dataline 最大的值参与比较运算。

    UPDATE user,thread SET money=0.05*money WHERE user.userid = thread.postuserid AND ((SELECT count(threadid) FROM thread ) = 0) AND thread.forumid IN (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59) AND user.membergroupids !=27 AND user.money >0;
    第二条是从未在技术区发过主题的,金币扣 95%
    这条是统计在技术区的主题为 0 个的。也是没有结果。
    17 条回复    2016-07-28 07:20:50 +08:00
    Layne
        1
    Layne  
       2016-07-27 00:38:48 +08:00
    不知道数据量有多少,用 exists 和 not exists 会不会在效率上有巨大差异,

    另外,没有环境验证,不确定能不能执行,我只是个数据渣…

    update user u
    set u.money = 0.5 * u.money
    where u.membergroupids != 27
    and u.money > 0
    and exists (
    select 1 from thread t where t.postuserid = u.userid
    and t.dateline between date_add(now(), interval -4 month) and date_add(now(), interval -4 month)
    and t.forumid in (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
    );


    update user u
    set u.money = 0.5 * u.money
    where u.membergroupids != 27
    and u.money > 0
    and not exists (
    select 1 from thread t where t.postuserid = u.userid
    and t.forumid in (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
    );
    oclock
        2
    oclock  
       2016-07-27 08:55:21 +08:00
    查询条件这么复杂,建议把逻辑拆出来放进 with CTE 或者临时表
    former
        3
    former  
       2016-07-27 09:43:26 +08:00
    取出 dataline 最大的值,直接 select MAX(dataline) from table where 条件 就可以
    adv007
        4
    adv007  
       2016-07-27 09:56:48 +08:00 via iPhone
    不要在 sql 中做复杂逻辑,抽到程序里面,你会发现整个世界都是清晰的
    wangyu1314
        5
    wangyu1314  
    OP
       2016-07-27 10:18:39 +08:00
    @Layne
    感谢,两条都可以执行,但是第一条没有结果。第二条有。
    1 queries executed, 1 success, 0 errors, 0 warnings

    查询: update user u set u.money = 0.5 * u.money where u.membergroupids != 27 and u.money > 0 and exists ( select 1 from thread t where...

    共 0 行受到影响

    执行耗时 : 2.945 sec
    传送时间 : 0 sec
    总耗时 : 2.946 sec

    1 queries executed, 1 success, 0 errors, 0 warnings

    update user u
    set u.money = 0.5 * u.money
    where u.membergroupids != 27
    and u.money > 0
    and not exists (
    select 1 from thread t where t.postuserid = u.userid
    and t.forumid in (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
    );

    查询: update user u set u.money = 0.5 * u.money where u.membergroupids != 27 and u.money > 0 and not exists ( select 1 from thread t w...

    共 23176 行受到影响

    执行耗时 : 3.634 sec
    传送时间 : 0 sec
    总耗时 : 3.634 sec
    wangyu1314
        6
    wangyu1314  
    OP
       2016-07-27 10:21:28 +08:00
    @oclock 谢谢你,你说的更高深,还学到那步来。
    @former 这种 方法我试过,但是语句没写成功。
    @adv007 现在主要是苦恼不懂 PHP 呀。
    Layne
        7
    Layne  
       2016-07-27 11:02:38 +08:00
    @wangyu1314 写 update 时,可以先把查询逻辑部分写好,然后更新需要处理的字段,以我写的第一句来看,查询逻辑为:

    select *
    from
    /*update*/ user u
    /* set u.money = 0.5 * u.money */
    where u.membergroupids != 27
    and u.money > 0
    and exists (
    select 1 from thread t where t.postuserid = u.userid
    and t.dateline between date_add(now(), interval -4 month) and date_add(now(), interval -4 month)
    and t.forumid in (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
    );

    调整这个查询逻辑,直到能查询出你需要更新的数据,然后再 update 对应字段即可。

    写 update 语句时我一般习惯不用表关联,如果查询逻辑本身就特别复杂,就用存储过程来处理,或者用程序来处理了。
    wangyu1314
        8
    wangyu1314  
    OP
       2016-07-27 12:11:40 +08:00
    @Layne 改出来了,但是这个思路是错的,这个语句会导致用户如果每个月都发了新主题的,会被重复扣钱。我们有 3 条规则, 1 - 3 个月扣多少, 3 - 6 扣多少, 6 个月以上扣多少。
    wangyu1314
        9
    wangyu1314  
    OP
       2016-07-27 12:17:58 +08:00
    这个语句只能找在技术区的所发主题的最大 dateline ,只对这个用户操作一次。
    Layne
        10
    Layne  
       2016-07-27 13:15:42 +08:00
    @wangyu1314

    试试这样:

    select *
    from
    /*update*/ user u
    /* set u.money = 0.5 * u.money */
    where u.membergroupids != 27
    and u.money > 0
    and exists (
    select max(t.dateline) from thread t where t.postuserid = u.userid
    and t.forumid in (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
    ) between date_add(now(), interval -4 month) and date_add(now(), interval -4 month)
    ;
    wangyu1314
        11
    wangyu1314  
    OP
       2016-07-27 14:19:45 +08:00
    @Layne
    SELECT *
    FROM
    /*update*/ USER u
    /* set u.money = 0.5 * u.money */
    WHERE u.membergroupids != 27
    AND u.money > 0
    AND EXISTS (
    SELECT MAX(t.dateline) FROM thread t WHERE t.postuserid = u.userid
    AND t.forumid IN (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
    ) BETWEEN DATE_ADD(NOW(), INTERVAL 4 MONTH) AND DATE_ADD(NOW(), INTERVAL 1 MONTH) ;
    运行了以后没结果。
    这种最大化取值 MAX(dateline)我试过了,只能取出一个全局最大值,不能取出每个人的最大值,而是这种取出值 不能参与运算。
    Layne
        12
    Layne  
       2016-07-27 14:27:53 +08:00
    把 exists 去掉呢?

    自查询限制了 postuserid = userid ,取得的 max(dateline) 应该就是对应用户的最大值
    wangyu1314
        13
    wangyu1314  
    OP
       2016-07-27 14:48:18 +08:00
    @Layne 去掉 exists 仍然没有效果。。。
    Layne
        14
    Layne  
       2016-07-27 14:53:54 +08:00
    @wangyu1314 看一下 between 语句后面的月份加减对不对,我用的是 date_add 函数,然后前溯的话应该是负数参数,或者用你习惯的日期函数来写
    wangyu1314
        15
    wangyu1314  
    OP
       2016-07-27 14:56:46 +08:00
    我用另外一种 方法取出了 thread 表每个用户的最大 dateline.

    SELECT postuserid,FROM_UNIXTIME(dateline) FROM thread
    WHERE
    forumid IN (56,57,60,62,67,65,70,71,73,178,75,76,77,109,189,186,81,83,88,89,74,175,181,85,74,175,79,80,165,193,172,59)
    GROUP BY postuserid ORDER BY dateline DESC
    adv007
        16
    adv007  
       2016-07-27 17:37:40 +08:00 via iPhone
    @wangyu1314 学学呗,你想想你这条语句遇到大并发的执行效率
    msg7086
        17
    msg7086  
       2016-07-28 07:20:50 +08:00
    用关系数据库的 SQL 来跑业务逻辑只会更慢吧。
    要不写存储过程?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1045 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 19:40 · PVG 03:40 · LAX 11:40 · JFK 14:40
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.