MySQL和Oracle批量插入SQL的通用寫法示例

更新時(shí)間：2021年11月12日 14:27:02 作者：GreatHair

當(dāng)我們要往數(shù)據(jù)庫(kù)中批量保存多條數(shù)據(jù)得時(shí)候,分不同數(shù)據(jù)庫(kù),有不同得插入方式,這篇文章主要給大家介紹了關(guān)于MySQL和Oracle批量插入SQL的通用寫法的相關(guān)資料,需要的朋友可以參考下

舉個(gè)例子：

現(xiàn)在要批量新增User對(duì)象到數(shù)據(jù)庫(kù)USER表中

public class User{
    //姓名
    private String name;
    //年齡
    private Integer age;
    //性別
    private Integer sex
}

大部分人對(duì)MySQL比較熟悉，可能覺得批量新增的SQL都是這樣寫，其實(shí)并不然。該寫法在MySQL中沒(méi)問(wèn)題，而在Oracle中，這樣寫就會(huì)報(bào)錯(cuò)。

MySQL寫法：

INSERT INTO USER 
    (NAME,AGE,SEX)
VALUES
('val1_1', 'val1_2', 'val1_3'),
('val2_1', 'val2_2', 'val2_3'),
('val3_1', 'val3_2', 'val3_3');

Oracle寫法：

//多次單條插入
INSERT INTO USER (NAME,AGE,SEX) VALUES ('val1_1', 'val1_2', 'val1_3');
INSERT INTO USER (NAME,AGE,SEX) VALUES ('val2_1', 'val2_2', 'val2_3');
INSERT INTO USER (NAME,AGE,SEX) VALUES ('val3_1', 'val3_2', 'val3_3');

//批量插入
INSERT ALL
   INTO USER (NAME,AGE,SEX) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO USER (NAME,AGE,SEX) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO USER (NAME,AGE,SEX) VALUES ('val3_1', 'val3_2', 'val3_3')
SELECT 1 FROM DUAL;

可以發(fā)現(xiàn)Oracle的兩種寫法都比較的麻煩，批量插入也壓根沒(méi)有減少插入的列名。除此之外，另一個(gè)麻煩的事情就是，在企業(yè)開發(fā)中，一套軟件系統(tǒng)可能需要支持多套數(shù)據(jù)庫(kù)的，因此這條新增的操作，就得適配兩套數(shù)據(jù)庫(kù)，維護(hù)兩套SQL，大大地增加了開發(fā)成本。

那么有沒(méi)有一種通用的寫法呢？答案是有的。

通用寫法：

INSERT INTO USER (NAME,AGE,SEX)
    select ('val1_1', 'val1_2', 'val1_3') from dual union all
    select ('val2_1', 'val2_2', 'val2_3') from dual union all
    select ('val3_1', 'val3_2', 'val3_3') from dual

這樣一來(lái)，既簡(jiǎn)單又能少維護(hù)一套SQL，兩全其美。

下面是XML文件里各種寫法的代碼。

<!--MySQL的批量插入-->
<insert id="batchInsertUser" databaseId="mysql">
   INSERT INTO USER 
            (NAME,AGE,SEX)
   VALUES
    <foreach collection="userList" index="index" item="user" separator=",">
            (#{user.name},#{user.age},#{user.sex}）
  </foreach>
</insert>

<!--Oracle的批量插入-->
<insert id="batchInsertUser" databaseId="oracle">
  BEGIN
  <foreach collection="userList" index="index" item="user" separator=";">
   INSERT INTO USER 
            (NAME,AGE,SEX)
   VALUES
            (#{user.name},#{user.age},#{user.sex}）
  </foreach>
  ;END;
</insert>

仔細(xì)觀察MySQL和Oracle的寫法，因?yàn)镸ySQL支持上述在VALUES后面直接插入多條數(shù)據(jù)，因此。foreach標(biāo)簽只需要循環(huán)遍歷出VALUES后面（）里的內(nèi)容即可；而Oracle因?yàn)椴恢С诌@種寫法因此需要循環(huán)遍歷整個(gè)INSERT語(yǔ)句。

<!--通用的批量插入-->
<insert id="batchInsertUser" databaseId="mysql">
   INSERT INTO USER 
            (NAME,AGE,SEX)
  <foreach collection="userList" index="index" item="user" separator="union all">
     SELECT
            (#{user.name},#{user.age},#{user.sex})
     FROM DUAL
  </foreach>
</insert>