mybatis upsert操作[插入/更新]

傻fufu码农

已于 2023-01-28 17:29:02 修改

阅读量5.2k

点赞数

分类专栏： mysql&mybatis 文章标签： mysql mybatis

于 2021-05-08 18:19:55 首次发布

本文链接：https://blog.youkuaiyun.com/qq_17303159/article/details/116537593

版权

mysql&mybatis 专栏收录该内容

11 篇文章

订阅专栏

这篇博客详细介绍了如何使用SQL语句进行动态插入和更新操作，特别是针对字段值为空的情况，如何智能地决定是否插入或使用默认值。示例代码展示了在`DemoTable`表中，根据`unique_key1`和`unique_key2`的唯一性，执行插入或更新操作，并考虑了`tel_number`和`addr`字段的非空判断。

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

    @Insert({"<script>"

            + " INSERT INTO "
            + " DemoTable ("
            + "       unique_key1,"
            + "       unique_key2,"
            + "       stu_name,"
            + "       stu_age,"
            /*判断要插入的该字段的值是否为空，为空则不插入该字段，此时会使用数据库创建时的默认值*/
            + "       <if test='telNumber != null and telNumber.length()>0'> tel_number, </if>" 
            + "       <if test='addr != null and addr.length()>0'> addr, </if>"
            + "       remarks,"
            + "       update_user,"
            + "       update_time"
            + " )"
            + " VALUES ("
            + "       #{uniqueKey1},"
            + "       #{uniqueKey2},"
            + "       #{stuName},"
            + "       #{stuAge},"
            + "       <if test='telNumber != null and telNumber.length()>0'> #{telNumber}, </if>"
            + "       <if test='addr != null and addr.length()>0'> #{addr}, </if>"
            + "       #{remarks},"
            + "       #{updateUser},"
            + "       #{updateTime}"
            + " )"
            + " ON DUPLICATE KEY UPDATE"  /*如果后面的条件成立则UPDATE，不成立则INSERT*/
            + " unique_key1=#{uniqueKey1}," /*条件1*/
            + " unique_key2=#{uniqueKey2}"  /*条件2*/

            + "</script>"})

参考链接：

MySql的upsert操作_data_scientist的博客-优快云博客_upsert into 几种写法

当字段为空时MyBatis添加默认值 - VoidCC