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