Mysql ON DUPLICATE KEY UPDATE_xzvp-优快云博客

本文链接：https://blog.youkuaiyun.com/chengmin123456789/article/details/112660774

这篇博客探讨了MySQL中如何使用INSERT INTO...VALUES和ON DUPLICATE KEY UPDATE语句进行单条和批量的数据插入与更新。内容包括一次性插入多条记录，以及在MyBatis框架中如何实现类似的SQL操作，用于高效地管理数据库中的信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

单条插入

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)  
      ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

将查询的结果一次性插入

INSERT INTO ipos_zdyjb2 (org_id,zd_id,nd,mn,xzvp)
SELECT *
FROM (
	SELECT org_id, zd_id, DATE_FORMAT(FROM_UNIXTIME(qyrq),'%Y') as year,DATE_FORMAT(FROM_UNIXTIME(qyrq),'%m')-0 as month,count(*) as xzvp
				 
				 FROM ipos_vip 
				 WHERE qy = 1 AND zd_id > 0  and DATE_FORMAT(FROM_UNIXTIME(qyrq),'%Y-%m-%d')>'2018-08-01'
				 GROUP BY zd_id,year,month
) A
ON DUPLICATE KEY UPDATE xzvp=VALUES(xzvp);

在mybatis中进行批量增加或修改的sql为：

在mybatis中进行批量增加或修改的sql为：

<insert id="insertOrUpdateCameraInfoByBatch" parameterType="java.util.List">
      insert into camera_info(
          zone1Id,zone1Name,zone2Id,zone2Name,zone3Id,zone3Name,zone4Id,zone4Name,
          cameraId
          )VALUES
           <foreach collection ="list" item="cameraInfo" index= "index" separator =",">
             (
                #{cameraInfo.zone1Id}, #{cameraInfo.zone1Name}, #{cameraInfo.zone2Id},
                #{cameraInfo.zone2Name}, #{cameraInfo.zone3Id}, #{cameraInfo.zone3Name},
                #{cameraInfo.zone4Id}, #{cameraInfo.zone4Name}, 
                #{cameraInfo.cameraId}, 
             )
           </foreach>
           ON DUPLICATE KEY UPDATE
               zone1Id = VALUES(zone1Id),zone1Name = VALUES(zone1Name),zone2Id = VALUES(zone2Id),
               zone2Name = VALUES(zone2Name),zone3Id = VALUES(zone3Id),zone3Name = VALUES(zone3Name),
               zone4Id = VALUES(zone4Id),zone4Name = VALUES(zone4Name),
               cameraId = VALUES(cameraId)
    </insert>