on duplicate key update的用法

最新推荐文章于 2026-05-03 16:53:22 发布

原创最新推荐文章于 2026-05-03 16:53:22 发布 · 4.4k 阅读

10 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

个人

本文详细介绍了MySQL中ONDUPLICATEKEYUPDATE语句的使用方法，解释了如何在遇到重复键时更新或插入记录。通过具体实例展示了单个及批量操作的SQL语句，并提供了MyBatis中的应用示例。

介绍:

在MySQL数据库中，如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句，而要插入的行与表中现有记录的惟一索引或主键中产生重复值，那么就会发生旧行的更新；如果插入的行数据与现有表中记录的唯一索引或者主键不重复，则执行新纪录插入操作。

说通俗点就是数据库中存在某个记录时，执行这个语句会更新，而不存在这条记录时，就会插入。

用法:

insert into tablename(field1,field2,field3,......) 
values
(value1,value2,value3,....) 
on duplicate key update 
field1=values(value1),field2=values(value2),field3=values(value3),......;

实际操作

表(这里id用的是自增):

新增操作

原先记录:

SQL:

insert into cmdb_device_resources( ID,NODE,project_batch_id,IDC,DEVICECLASS1ID,DEVICECLASS2ID,DEVICECONFIGID,
PLANNING_RESOURCES,BUILDING_RESOURCES,REMARK,CREATE_DATE)
    VALUES(null,'all',null,'all','','','',8,4,'备注',now())
    ON DUPLICATE KEY UPDATE 
    ID = VALUES(ID),
    NODE = VALUES(NODE),
    project_batch_id = VALUES(project_batch_id),
    IDC = VALUES(IDC),
    DEVICECLASS1ID = VALUES(DEVICECLASS1ID),
    DEVICECLASS2ID = VALUES(DEVICECLASS2ID),
    DEVICECONFIGID = VALUES(DEVICECONFIGID),
    PLANNING_RESOURCES = VALUES(PLANNING_RESOURCES),
    BUILDING_RESOURCES = VALUES(BUILDING_RESOURCES),
    REMARK = VALUES(REMARK),
    CREATE_DATE = VALUES(CREATE_DATE)

插入后记录:

修改操作:

原先记录(就是上图插入后记录,这里不插入图片)

SQL:区别在于id(索引)

insert into cmdb_device_resources( ID,NODE,project_batch_id,IDC,DEVICECLASS1ID,DEVICECLASS2ID,DEVICECONFIGID,
PLANNING_RESOURCES,BUILDING_RESOURCES,REMARK,CREATE_DATE)
    VALUES(4,'all',null,'all','','','',10,5,'备注',now())
    ON DUPLICATE KEY UPDATE 
    ID = VALUES(ID),
    NODE = VALUES(NODE),
    project_batch_id = VALUES(project_batch_id),
    IDC = VALUES(IDC),
    DEVICECLASS1ID = VALUES(DEVICECLASS1ID),
    DEVICECLASS2ID = VALUES(DEVICECLASS2ID),
    DEVICECONFIGID = VALUES(DEVICECONFIGID),
    PLANNING_RESOURCES = VALUES(PLANNING_RESOURCES),
    BUILDING_RESOURCES = VALUES(BUILDING_RESOURCES),
    REMARK = VALUES(REMARK),
    CREATE_DATE = VALUES(CREATE_DATE)

修改后记录:

在mybatis中的使用:

单个增加或修改sql的写法为：

<insert id="saveOrUpdatePlanReportList" parameterType="java.util.List">
        insert into cmdb_device_resources
        ( ID,NODE,project_batch_id,IDC,
        DEVICECLASS1ID,DEVICECLASS2ID,DEVICECONFIGID,
        PLANNING_RESOURCES,BUILDING_RESOURCES,REMARK,CREATE_DATE)
        VALUES(
            #{item.cdrId},#{item.node},#{item.project},#{item.idc},
            #{item.deviceClass1Id},#{item.deviceClass2Id},#{item.deviceConfigId},
            #{item.planningResources},#{item.buildingResources},#{item.remark},now()
         )
        ON DUPLICATE KEY UPDATE
        ID = VALUES(ID),
        NODE = VALUES(NODE),
        project_batch_id = VALUES(project_batch_id),
        IDC = VALUES(IDC),
        DEVICECLASS1ID = VALUES(DEVICECLASS1ID),
        DEVICECLASS2ID = VALUES(DEVICECLASS2ID),
        DEVICECONFIGID = VALUES(DEVICECONFIGID),
        PLANNING_RESOURCES = VALUES(PLANNING_RESOURCES),
        BUILDING_RESOURCES = VALUES(BUILDING_RESOURCES),
        REMARK = VALUES(REMARK),
        CREATE_DATE = VALUES(CREATE_DATE)
    </insert>

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

<insert id="saveOrUpdatePlanReportList" parameterType="java.util.List">
        insert into cmdb_device_resources
        ( ID,NODE,project_batch_id,IDC,
        DEVICECLASS1ID,DEVICECLASS2ID,DEVICECONFIGID,
        PLANNING_RESOURCES,BUILDING_RESOURCES,REMARK,CREATE_DATE)
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.cdrId},#{item.node},#{item.project},#{item.idc},
            #{item.deviceClass1Id},#{item.deviceClass2Id},#{item.deviceConfigId},
            #{item.planningResources},#{item.buildingResources},#{item.remark},now()
            )
        </foreach>
        ON DUPLICATE KEY UPDATE
        ID = VALUES(ID),
        NODE = VALUES(NODE),
        project_batch_id = VALUES(project_batch_id),
        IDC = VALUES(IDC),
        DEVICECLASS1ID = VALUES(DEVICECLASS1ID),
        DEVICECLASS2ID = VALUES(DEVICECLASS2ID),
        DEVICECONFIGID = VALUES(DEVICECONFIGID),
        PLANNING_RESOURCES = VALUES(PLANNING_RESOURCES),
        BUILDING_RESOURCES = VALUES(BUILDING_RESOURCES),
        REMARK = VALUES(REMARK),
        CREATE_DATE = VALUES(CREATE_DATE)
    </insert>

参考:

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

https://www.jianshu.com/p/78ea17c6d190

https://blog.csdn.net/qq_22771739/article/details/84668620

https://www.cnblogs.com/zjdxr-up/p/8319982.html