关键点:foreach中separator="union all"
示例:
<insert id="saveAuditLog" parameterType="java.util.Map">
INSERT INTO
TAUDITLOG(FID,FITEMID,FUID,FOPTTIME,FSTATUS,FOPINION)
<foreach collection="listitem" item="item" index="index" separator="union all">
(SELECT uuid(),
#{item.itemId,jdbcType=VARCHAR},
#{item.editor,jdbcType=VARCHAR},
NOW(),
#{item.status,jdbcType=INTEGER},
#{item.opinion,jdbcType=VARCHAR}
FROM DUAL)
</foreach>
</insert>
执行SQL:
INSERT INTO TAUDITLOG(FID,FITEMID,FUID,FOPTTIME,FSTATUS,FOPINION)
(SELECT uuid(), ?, ?, NOW(), ?, ? FROM DUAL) union all
(SELECT uuid(), ?, ?, NOW(), ?, ? FROM DUAL) union all
(SELECT uuid(), ?, ?, NOW(), ?, ? FROM DUAL) union all
(SELECT uuid(), ?, ?, NOW(), ?, ? FROM DUAL)
对DUAL的解释:现在一般线上使用的MySQL都是5.5以上的,在mysql里也存在和oracle里类似的dual虚拟表,官方声明纯粹是为了满足"select ... from"
这一习惯问题。