[mysql]mysql通过on duplicate key update实现批量插入或更新

Oracle版请参照此文章：https://blog.csdn.net/weixin_43303530/article/details/108495327?spm=1001.2014.3001.5501

定时更新或插入用户的排名表数据，由于数据量的缘故，导致数据量比较大，采取单条的insertOrUpdate比较耗时，且在多线程并行插入的时候容易导致索引冲突报错，从而数据插入失败，这里采用创建联合索引和on duplicate key update来实现，需要注意的是，此方法会锁表，导致其他的表操作需要等待，若操作的表变更比较频繁，建议另寻其他方案

表结构

-- auto-generated definition
create table user_activity_ranking
(
    id             bigint auto_increment comment 'id主键'
        primary key,
    activity_id    bigint                               not null comment '活动id',
    period int (20) not null comment '第几阶段',
    `rank`         int(20)                              not null comment '排名',
    user_id        bigint                               not null comment '用户id',
    name           varchar(100)                         null comment '加密用户名',
    telephone      varchar(60)                          null comment '加密电话号码',
    num            int(20)                              not null comment '第几次游戏',
    score          int(20)                              not null comment '本次游戏分数',
    total_score    int(20)                              not null comment '游戏累计分数',
    is_send        tinyint(1)                           not null comment '是否已发券，0 未发 1 已发',
    is_deleted     tinyint(1) default 0                 not null comment '是否已删除 缺省 0 ， 1 为已删除',
    create_user_id bigint     default 0                 not null comment '创建人  缺省为0 代表system',
    create_time    timestamp  default CURRENT_TIMESTAMP null comment '创建时间-应用操作时间',
    update_time    timestamp  default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '最后修改时间',
    update_user_id bigint     default 0                 not null comment '修改人 缺省为0 代表system',
    constraint idx_activity_id_period_user_id
        unique (activity_id, period, user_id)
)
    comment '用户排名表';

create index idx_activity_range
    on user_activity_ranking (activity_id, period, is_send, is_deleted, create_time);

create index idx_activity_ranking
    on user_activity_ranking (activity_id, user_id, is_deleted);

上表以activity_id、period、user_id创建联合唯一索引idx_activity_id_period_user_id，保证活动相同用户相同且活动阶段相同的情况下用户的排名数据只有一条，而在此业务逻辑删除字段is_deleted正常来说是不会发生变更的，都是是未删除的情况，所以没有联合字段is_deleted。

实现(后端代码技术架构为java+mybatis)

• dao：

void batchInsertUserActivityRanking(@Param("list") List<UserActivityRankingPO> list);

• xml：

insert into user_activity_ranking (activity_id, period, rank, user_id,name,telephone,num, score, total_score,
                                           is_send, is_deleted, create_user_id, create_time, update_time, update_user_id)
        values
<foreach collection="list" item="item" separator=",">
            (#{item.activityId}, #{item.period}, #{item.rank}, #{item.userId}, #{item.name}, #{item.telephone}, #{item.num}, #{item.score}, #{item.totalScore}, #{item.isSend},
                                            0, #{item.createUserId}, now(), now(), #{item.updateUserId})
        </foreach>
        on duplicate key update
            update_time = now(),
            rank = VALUES(rank),
            telephone = VALUES(telephone),
            num = VALUES(num),
            score = VALUES(score),
            total_score = VALUES(total_score)
    </insert>

PS：

• 使用此方法切记需要考虑性能问题，若是批量解析excel等问题，建议异步使用该SQL语句；
• 该语句虽然好用，但是在并发情况下可能会造成数据库死锁；
• 如果插入的表是id自增，可能会出现id不连续的情况，原因是update的同时也会对id序列加1；

更多的性能问题可参阅：https://www.cnblogs.com/better-farther-world2099/articles/11737376.html