merge into mybatis批量插入或更新，有则更新，无则插入

merge into 语法

MERGE INTO [target-table] T USING [source-table sql] S
ON([conditional expression] and […]…)
WHEN MATCHED
THEN [UPDATE sql]
WHEN NOT MATCHED
THEN [INSERT sql]
实际实现功能，导入接口清单表，根据接口编号判断，存在即更新，不存在即插入，separator=”union all” 和 from dual的作用是将每行数据合并成一张虚拟临时表数据

<update id=”saveOrUpdateInterfaceList”>
MERGE INTO INTERFACE_LIST a
USING(
<foreach collection=”list” index=”index” item=”interfaceList”
separator=”union all”>
SELECT
#{interfaceList.id,jdbcType=VARCHAR} id,
#{interfaceList.projectId,jdbcType=VARCHAR} projectId,
#{interfaceList.interfaceCode,jdbcType=VARCHAR} interfaceCode,
#{interfaceList.requestSys,jdbcType=VARCHAR} requestSys,
#{interfaceList.responseSys,jdbcType=VARCHAR} responseSys,
#{interfaceList.interfaceMode,jdbcType=VARCHAR} interfaceMode,
#{interfaceList.description,jdbcType=VARCHAR} description,
#{interfaceList.dataFlow,jdbcType=VARCHAR} dataFlow,
#{interfaceList.protocol,jdbcType=VARCHAR} protocol,
#{interfaceList.interfaceStatus,jdbcType=VARCHAR} interfaceStatus,
#{interfaceList.prdTime,jdbcType=VARCHAR} prdTime,
#{interfaceList.remarks,jdbcType=VARCHAR} remarks
from dual
</foreach>
)b
ON (a.INTERFACE_CODE = b.interfaceCode)
when matched then
UPDATE SET a.PROJECT_ID = b.projectId,a.REQUEST_SYS = b.requestSys,
a.RESPONSE_SYS = b.responseSys,
a.INTERFACE_MODE = b.interfaceMode,
a.DESCRIPTION =
b.description,a.DATA_FLOW = b.dataFlow,
a.PROTOCOL = b.protocol,
a.INTERFACE_STATUS = b.interfaceStatus,
a.PRD_TIME = b.prdTime,
a.REMARKS = b.remarks
where a.PROJECT_ID = B.projectId
when not matched then
INSERT(a.ID,a.PROJECT_ID,a.INTERFACE_CODE,a.REQUEST_SYS,
a.RESPONSE_SYS,a.INTERFACE_MODE,
a.DESCRIPTION,a.DATA_FLOW,a.PROTOCOL,
a.INTERFACE_STATUS,a.PRD_TIME,a.REMARKS)
VALUES (b.id,b.projectId,b.interfaceCode,b.requestSys,
b.responseSys,b.interfaceMode,
b.description,b.dataFlow,b.protocol,
b.interfaceStatus,
b.prdTime,b.remarks)
</update>
————————————————
版权声明：本文为CSDN博主「Ju1y.」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/w15247147672/article/details/120824439