on duplicate key update的用法

╰半橙微兮° 2024-04-17 05:44 63阅读 0赞

介绍:

在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用的是自增):

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjaF8yMDE2_size_16_color_FFFFFF_t_70][]

新增操作

原先记录:

![20190823170251338.png][]

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)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjaF8yMDE2_size_16_color_FFFFFF_t_70 1][]

插入后记录:

![20190823170627672.png][]

修改操作:

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

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)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjaF8yMDE2_size_16_color_FFFFFF_t_70 2][]

修改后记录:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjaF8yMDE2_size_16_color_FFFFFF_t_70 3][]

在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_dev.mysql.com_doc_refman_5.7_en_insert-on-duplicate.html]

[https://www.jianshu.com/p/78ea17c6d190][https_www.jianshu.com_p_78ea17c6d190]

[https://blog.csdn.net/qq\_22771739/article/details/84668620][https_blog.csdn.net_qq_22771739_article_details_84668620]

[https://www.cnblogs.com/zjdxr-up/p/8319982.html][https_www.cnblogs.com_zjdxr-up_p_8319982.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjaF8yMDE2_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/62f2f76a366246cd95b69bc5ff110ca0.png
[20190823170251338.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/9b57cc8cef074c8ebc81e48ae51d1250.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjaF8yMDE2_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/6e96ab37fe044b3380e8c874fd59b853.png
[20190823170627672.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/2565ce0f20de422babd597d581b28602.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjaF8yMDE2_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/c47da677719a44ca83da84f7f8c19746.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xjaF8yMDE2_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/acf17439f5d740d29812efd4bf7a4428.png
[https_dev.mysql.com_doc_refman_5.7_en_insert-on-duplicate.html]: https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
[https_www.jianshu.com_p_78ea17c6d190]: https://www.jianshu.com/p/78ea17c6d190
[https_blog.csdn.net_qq_22771739_article_details_84668620]: https://blog.csdn.net/qq_22771739/article/details/84668620
[https_www.cnblogs.com_zjdxr-up_p_8319982.html]: https://www.cnblogs.com/zjdxr-up/p/8319982.html