一直使用的是Oracle数据库,现在换成mysql数据库,数据库不同批量插入的语法也不同
mysql批量插入的语法:
<insert id="addBatch" parameterType="java.util.List" useGeneratedKeys="false">
INSERT INTO t_game_error_sort(
user_id,
record_id,
garbage,
garbage_img,
error_sort,
correct_sort,
enable_,
remark_,
create_by,
create_time,
update_by,
update_time
)
VALUES
<foreach collection ="list" item="item" separator =",">
(#{item.userId},
#{item.recordId},
#{item.garbageImage},
#{item.garbage},
#{item.errorSort},
#{item.correctSort},
#{item.enable},
#{item.remark},
#{item.createBy},
#{item.createTime},
#{item.updateBy},
#{item.updateTime})
</foreach >
</insert>
Oracle批量插入语法:无values插入 直接通过 SELECT t.* from( )t 的方式来填充值
<insert id="addBatch" parameterType="java.util.List" useGeneratedKeys="false">
INSERT INTO t_game_error_sort(
user_id,
record_id,
garbage,
garbage_img,
error_sort,
correct_sort,
enable_,
remark_,
create_by,
create_time,
update_by,
update_time
)
SELECT t.*
from(
<foreach collection="list" item="item" index="index" separator="UNION ALL">
select
#{item.userId} user_id,
#{item.recordId} record_id,
#{item.garbageImage} garbage_img,
#{item.garbage} garbage,
#{item.errorSort} error_sort,
#{item.correctSort} correct_sort,
#{item.enable} enable_,
#{item.remark} remark_,
#{item.createBy} create_by,
#{item.createTime} create_time,
#{item.updateBy} update_by,
#{item.updateTime} update_time
FROM dual
</foreach>
) t //批量插入的时候 要有别名
</insert>