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

求助 SQL 语句,关于 INSERT INTO xxx ON DUPLICATE KEY UPDATE...

  •  
  •   uti6770werty · 2022-02-17 18:36:38 +08:00 · 2900 次点击
    这是一个创建于 994 天前的主题,其中的信息可能已经有所发展或是发生改变。

    INSERT INTO mcrun (starttime,loop,chckport,mcid,UpdateTime) VALUES ('2022-02-15 09:20:11','9','R','217',NOW()) ON DUPLICATE KEY UPDATE xxx

    mcrun 表只有mcid约束唯一的键
    现在 INSERT INTO '217'这个语句,
    有可能表里已经有 217 数据了,
    想完成这个事情,的 UPDATE 做一些判断修改表的方法应该如何实现?

    1 、如果指定的starttimeloop这两个在表的数据,和新插入有不同,单一个值有变化,也算条件成立,就更新最新的值,并把 UpdateTime 修改成 NOW()
    2 、如果指定的starttimeloop这两个在表的数据,和新插入的一样,什么都不用动
    3 、chckport的字段,不用理会

    能不能单条语句完成?

    如果一条语句实在办不到,自己能想到的是,解除约束健设置,直接新加入地写入,周期地删除重复 mcid ,只保留最新的 mcid 条目。。。,效率低,查询又要增加 order by UpdateTime LIMIT 1 ,很麻烦。。。

    谢谢各位大佬能解答这个问题,谢谢!

    16 条回复    2022-02-19 15:25:32 +08:00
    zhoudaiyu
        1
    zhoudaiyu  
       2022-02-17 21:56:41 +08:00 via iPhone
    这个 SQL 默认逻辑不就是这样吗?如果 uniquekey 不存在,插入本次的数据,如果存在则更新除了 uniquekey 以外的数据
    uti6770werty
        2
    uti6770werty  
    OP
       2022-02-17 22:24:29 +08:00
    @zhoudaiyu [如果存在则更新除了 uniquekey 以外的数据] ,这个 SQL 是会把 UpdateTime 也更新过去的,数据没有变化的话
    uti6770werty
        3
    uti6770werty  
    OP
       2022-02-17 22:25:06 +08:00
    也就是想把逻辑判断也做在 SQL 语句里
    littlewing
        4
    littlewing  
       2022-02-17 22:31:17 +08:00
    update 后面可以跟 col_name = 'value' 的
    goodSleep
        5
    goodSleep  
       2022-02-18 00:30:57 +08:00 via Android
    update 后跟 case when ,看看下面的例子 能解决问题么
    https://stackoverflow.com/questions/24000685/insert-on-duplicate-key-update-if
    MarsBar
        6
    MarsBar  
       2022-02-18 07:42:45 +08:00
    假设你说的是 postgresql
    那么: https://www.postgresqltutorial.com/postgresql-upsert/
    你可以用:
    DO UPDATE SET column_1 = value_1, .. WHERE condition
    xaplux
        7
    xaplux  
       2022-02-18 09:13:54 +08:00
    试试把 UpdateTime 设置为有变动时自动更新,不要显示的传入
    mysql 可以这样:
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    你的 sql 语句改为:
    NSERT INTO mcrun (starttime,loop,chckport,mcid) VALUES ('2022-02-15 09:20:11','9','R','217') ON DUPLICATE KEY UPDATE xxx
    xaplux
        8
    xaplux  
       2022-02-18 09:27:05 +08:00
    仔细看了一下你的需求,我上面说的也实现不了,建议你还是乖乖的先查出来,再更新吧
    dqzcwxb
        9
    dqzcwxb  
       2022-02-18 11:03:52 +08:00
    不建议使用 ON DUPLICATE,会导致自增主键不连续因为它的是先尝试 insert 发现唯一索引冲突再 delete 数据再 insert

    如果你的业务大部分情况下是 update,那么建议直接 update 再判断返回值是否为 1,如果为 1 则说明更新成功不用再处理如果为 0 则说明数据不存在或者 update 前后数据一致,那么此时需要 select 数据然后判断是 insert 还是不需要处理,记得 insert 的时候加锁或者忽略索引冲突异常即可
    jimmzhou
        10
    jimmzhou  
       2022-02-18 11:20:34 +08:00
    不知道楼主用啥语言 啥框架 golang 中 使用 gorm 有 Upsert 操作 可以指定更新需要修改的字段
    uti6770werty
        11
    uti6770werty  
    OP
       2022-02-18 11:29:25 +08:00
    谢谢各位,我没有用 ORM 的方式去与数据库交互,至于自增 id ,其实也不需要去考虑的,因为数据的发展永远到不了极限。。。

    我琢磨着试试写:
    ```
    INSERT INTO mcrun (starttime,loop,chckport,mcid,UpdateTime) VALUES ('2022-02-15 09:20:11','9','R','217',NOW())
    ON DUPLICATE KEY UPDATE `starttime`=IF(`starttime`<>'2022-02-22 22:22:22','2022-02-22 22:22:22',`starttime`),
    `loop`=IF((`loop`<>'9' OR `loop` IS NULL),'9',`loop`),
    `UpdateTime`=IF(`starttime`<>'2022-02-22 22:22:22' OR `loop`<>'9',NOW(),`UpdateTime`);
    ```

    1 、starttime 和 loop 都能被逻辑识别和修改成功,
    2 、主要是 UpdateTime ,好像条件没成立,也会被更新,是不是 SQL 语句做不了这么的逻辑判断?
    onhao
        12
    onhao  
       2022-02-18 11:29:42 +08:00
    不知 OP 敢不敢用触发器 例如: https://wuhao.pw/archives/268/
    把你的 1 ,2 ,3 写到触发器里去,不就完美了?
    uti6770werty
        13
    uti6770werty  
    OP
       2022-02-18 12:45:57 +08:00
    我的情况是,触发器过于维护复杂,安置的时候很爽,但后面有变动就算有文档都变得困难。。。

    最后,
    INSERT INTO mcrun (starttime,loop,chckport,mcid,UpdateTime) VALUES ('2022-02-15 09:20:11','9','R','217',NOW())
    ON DUPLICATE KEY UPDATE
    `UpdateTime`=IF(`starttime`<>'2022-02-22 22:22:22',NOW(),`UpdateTime`),
    `UpdateTime`=IF(`loop`<>'9' OR `loop` IS NULL,NOW(),`UpdateTime`),
    `starttime`=IF(`starttime`<>'2022-02-22 22:22:22','2022-02-22 22:22:22',`starttime`),
    `loop`=IF(`loop`<>'9' OR `loop` IS NULL),'9',`loop`);

    算研究出来了,
    逐个目标拆开写,细写,
    我想的是,SET 的顺序优先考虑 UpdateTime 处理了,再去判断真正的数值,
    这个逻辑在这个案例里够用,目前妥妥的正常,
    不知道会不会有什么幺蛾子没预料会发生的。。。。
    markgor
        14
    markgor  
       2022-02-18 15:34:46 +08:00
    @onhao #12
    触发器 一不小心就家毁人亡了......
    onhao
        15
    onhao  
       2022-02-19 12:08:50 +08:00
    @markgor 哈哈,不至于 @uti6770werty 是把,触发器这条路,也是条路,选择合适的在于自己,按需使用,其实没有必要把触发器想成洪水猛兽。
    markgor
        16
    markgor  
       2022-02-19 15:25:32 +08:00
    @onhao #15
    触发器,联合索引, 修改数据时一不小心导致另一张表也改变,但是自己不知道。后来自己测试时才发现异常,还好是在测试环境中。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5343 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 07:01 · PVG 15:01 · LAX 23:01 · JFK 02:01
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.