ON DUPLICATE KEY UPDATE

1、有时候遇到问题:
(1)在新增数据或者更新数据的时候,经常先判断,该表中是否有数据,如果有,则更新,如果没有,则插入。

(2)有时候有的msyql字段,需要在原有基础上进行累加,比如点读一本书的时长。
因此引入ON DUPLICATE KEY UPDATE,进行批量操作。

2、前提:该语句是基于唯一索引或主键使用,需要建立unique index 索引,
3、mybatis中的语句,举例:
fk_login_id, study_date为unique index

 @Insert({ "<script>",
            "INSERT INTO ddb_learn_log_day_trace(id,fk_login_id,study_date,count_time,book_study_time,spoken_test_time,exercises_time,read_time,other_time,read_num,spoken_num,spoken_sixty_points_less,spoken_eighty_points_more,sum_spoken_score,create_date,update_date) ",
            "VALUES ",
            // logDayTraces为插入集合
            "<when test='logDayTraces!=null'>",
            "<foreach collection='logDayTraces' item='logDayTrace' separator=','>",
                "(#{logDayTrace.id},#{logDayTrace.fkLoginId},#{logDayTrace.studyDate},#{logDayTrace.countTime},#{logDayTrace.bookStudyTime},#{logDayTrace.spokenTestTime},#{logDayTrace.exercisesTime},#{logDayTrace.readTime},#{logDayTrace.otherTime},#{logDayTrace.readNum},#{logDayTrace.spokenNum},#{logDayTrace.spokenSixtyPointsLess},#{logDayTrace.spokenEightyPointsMore},#{logDayTrace.sumSpokenScore},#{logDayTrace.createDate},#{logDayTrace.updateDate})",
            "</foreach>", "</when>", "ON DUPLICATE KEY UPDATE ",
            // 该字段累加数据
            "count_time = count_time + VALUES(count_time),",
             // 该字段累加数据
            "book_study_time = book_study_time + VALUES(book_study_time),",
             // 该字段累加数据
            "spoken_test_time = spoken_test_time + VALUES(spoken_test_time),",
            
            "exercises_time = exercises_time + VALUES(exercises_time),", "read_time = read_time + VALUES(read_time),",
            "other_time = other_time + VALUES(other_time),", "read_num = read_num + VALUES(read_num),",
            "spoken_num = spoken_num + VALUES(spoken_num),",
            "spoken_sixty_points_less = spoken_sixty_points_less + VALUES(spoken_sixty_points_less),",
            "spoken_eighty_points_more = spoken_eighty_points_more + VALUES(spoken_eighty_points_more),",
            "sum_spoken_score = sum_spoken_score + VALUES(sum_spoken_score),", "update_date = VALUES(update_date)",
            "</script>" })
  • 2
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值