Mybatis foreach batch data insertion: difference between Oracle and MySQL, mybatisforeach

Mybatis foreach batch data insertion: difference between Oracle and MySQL, mybatisforeach

Next we will introduce the differences between MySQL and Oracle in mybatis foreach batch data insertion:

• The main difference lies in the setting of the separator attribute in the foreach label:

• When separator is set to ",", the final code format for merging is:insert into table_name (a,b,c) values (v1,v2,v3) ,(v4,v5,v6) ,...

• When separator is set to "union all", the final code format for merging is:insert into table_name (a,b,c) values (v1,v2,v3) union all (v4,v5,v6) union all...

• For details, see the sample code:

Oracle:

<insert id="inserData" parameterType="com.test.aaa.Bac">  insert into table_name (name, adress, age)    values    <foreach collection="list" item="item" index="index" separator="union all">      (select #{item.name},           #{item.adress},           #{item.age}         from dual  )    <foreach></insert>

MySQL:

<insert id="inserData" parameterType="com.test.aaa.Bac">  insert into table_name (name, adress, age)    values    <foreach collection="list" item="item" index="index" separator=",">      (  #{item.name}, #{item.adress}, #{item.age} )    <foreach></insert>

Summary

The above section describes how to insert data in batches using mybatis foreach: What is the difference between Oracle and MySQL? I hope it will help you. If you have any questions, please leave a message, the editor will reply to you in time!