MySQL -- INSERT ON DUPLICATE KEY UPDATE 使用

前言：
MySQL 中 INSERT ON DUPLICATE KEY UPDATE 这个方法可能不是很常用，但却很好用，它主要功能是：当插入的数据中的主键 与 数据库中现有的数据主键 重复的情况下就不会执行插入操作，而是可以对现有的数据进行更新操作，不存在相同主键则执行插入操作。

下面来说说，我为什么会需要这样的操作：
假设有表 A，B，C，他们的主键分别是 a-pk，b-pk，c-pk

A表结构

• 其中A表是报表数据表，它是由 b-pk，c-pk 来确定唯一一条数据，最开始的想法是，通过建立（b-pk,c-pk）的联合唯一约束 来确保数据行的唯一性，若插入失败，说明已经存在相同的 b-pk，c-pk 组合，则对 other 字段进行更新操作。是否可以进行优化一下？不建立联合唯一约束可以不？

• 因为 b-pk，c-pk，他们本身作为主键具有唯一性，由此他们的组合也应当具有唯一性，通过简单的 b-pk+c-pk 字符串拼接可得出一个唯一的字符串，可作为 A 表的主键 a-pk 的值，这样可以确保唯一性，也可以免掉 MySQL 获取自增主键所带来的消耗。

• 同时为了使A表主键长度在一定范围内，所以我们再对 b-pk+c-pk 拼接的字符串结果进行 32位 MD5 后作为 A 表的主键，MD5 是十六进制，所以32位的 MD5 个数有 16的32次方个，这个数非常大，40亿的4次方个数，除非你的表数据超过了 40亿的4次方行，不然不可能会重复的。
  

• 主键是通过 md5(b-pk+c-pk) 外部生成的，所以可以直接利用主键唯一性约束来确保数据行唯一性，这样就不需要建立（b-pk,c-pk）联合唯一约束。到这里够好了吗？这样还是需要先插入，失败则更新，需要两条SQL，有没有可以更好的？

• 当然还可以更好，我想了想我这种操作正好适用 插入主键重复时做更新操作，于是就用了文章开头所说的方法 INSERT ON DUPLICATE KEY UPDATE

使用：

1.单条数据插入（以表A为例）

• 固定更新参数值：

  INSERT INTO A (a-pk,b-pk,c-pk,other) VALUES (md5(bbcc),bb,cc,666) ON DUPLICATE KEY UPDATE other=666;

• 传入更新参数值：

  INSERT INTO A (a-pk,b-pk,c-pk,other) VALUES (md5(bbcc),bb,cc,666) ON DUPLICATE KEY UPDATE other=VALUES(other);

• 上面两条SQL执行后 other 值都为 666，当没有相同数据执行插入时返回的受影响行数为 1，当相同的时候执行更新操作返回的受影响行数为 2（一条是我们准备插入的，一条是原有更新的）。

  
  

2.批量数据插入

• 单字段更新：

INSERT INTO `student_course`(`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`) 
VALUES 
(277941, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', '工业会计实战V9.0--55', 'kuaiji'),
(277942, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', '工业会计实战V9.0--66', 'kuaiji'),
(277943, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', '工业会计实战V9.0--77', 'kuaiji')
ON DUPLICATE KEY UPDATE
    course_name = VALUES(course_name)

• 多字段更新：

INSERT INTO `student_course`(`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`) 
VALUES 
(277941, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', '工业会计实战V9.0--55', 'kuaiji'),
(277942, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', '工业会计实战V9.0--66', 'kuaiji')
ON DUPLICATE KEY UPDATE
    course_name = VALUES(course_name),
    business_id = VALUES(business_id);

3.Mybatis中的使用

<insert id="insertBatch" parameterType="java.util.List" >
        INSERT INTO bi_log_student_attend (
        id, user_plan_id, class_type_id,
        compliance_per, creation_time, modified_time
        )
        VALUES
        <foreach collection="list" index="index" item="item" separator="," >
        (
            #{item.id,jdbcType=VARCHAR},
            #{item.userPlanId,jdbcType=BIGINT},
            #{item.classTypeId,jdbcType=BIGINT},
            #{item.compliancePer,jdbcType=DECIMAL},
            #{item.creationTime,jdbcType=TIMESTAMP},
            #{item.modifiedTime,jdbcType=TIMESTAMP}
        )
        </foreach>
        ON DUPLICATE KEY UPDATE
        compliance_per = VALUES(compliance_per),
        modified_time = VALUES(modified_time)
    </insert>