ON DUPLICATE KEY UPDATE

最新推荐文章于 2025-03-05 18:33:08 发布

森统

最新推荐文章于 2025-03-05 18:33:08 发布

阅读量841

点赞数 2

分类专栏： mybatis 文章标签： ON DUPLICATE KEY UPDATE运用

本文链接：https://blog.youkuaiyun.com/qq_28118609/article/details/90411370

版权

mybatis 专栏收录该内容

1 篇文章

订阅专栏

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>" })