MySQL和Oracle批量插入SQL差異詳解

1. 基本批量插入語法

1.1 MySQL批量插入

-- 方式1：使用VALUES
INSERT INTO table_name (column1, column2) 
VALUES 
(value1, value2),
(value3, value4),
(value5, value6);-- 方式2：使用INSERT ... SELECT
INSERT INTO table_name (column1, column2)
SELECT value1, value2 UNION ALL
SELECT value3, value4 UNION ALL
SELECT value5, value6;

1.2 Oracle批量插入

-- 方式1：使用INSERT ALL
INSERT ALLINTO table_name (column1, column2) VALUES (value1, value2)INTO table_name (column1, column2) VALUES (value3, value4)INTO table_name (column1, column2) VALUES (value5, value6)
SELECT 1 FROM DUAL;-- 方式2：使用UNION ALL
INSERT INTO table_name (column1, column2)
SELECT value1, value2 FROM DUAL UNION ALL
SELECT value3, value4 FROM DUAL UNION ALL
SELECT value5, value6 FROM DUAL;

2. 帶序列的批量插入

2.1 MySQL帶自增ID的批量插入

-- MySQL自增ID會自動處理
INSERT INTO table_name (column1, column2) 
VALUES 
(value1, value2),
(value3, value4);

2.2 Oracle帶序列的批量插入

-- 方式1：使用INSERT ALL
INSERT ALLINTO table_name (id, column1, column2) VALUES (seq_name.NEXTVAL, value1, value2)INTO table_name (id, column1, column2) VALUES (seq_name.NEXTVAL, value3, value4)
SELECT 1 FROM DUAL;-- 方式2：使用UNION ALL
INSERT INTO table_name (id, column1, column2)
SELECT seq_name.NEXTVAL, value1, value2 FROM DUAL UNION ALL
SELECT seq_name.NEXTVAL, value3, value4 FROM DUAL;

3. 條件批量插入

3.1 MySQL條件批量插入

-- 使用INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO table_name (id, column1, column2) 
VALUES 
(value1, value2),
(value3, value4)
ON DUPLICATE KEY UPDATE
column1 = VALUES(column1),
column2 = VALUES(column2);

3.2 Oracle條件批量插入

-- 使用MERGE INTO
MERGE INTO table_name t
USING (SELECT value1 as id, value2 as col1, value3 as col2 FROM DUAL UNION ALLSELECT value4, value5, value6 FROM DUAL
) s
ON (t.id = s.id)
WHEN MATCHED THENUPDATE SET t.column1 = s.col1,t.column2 = s.col2
WHEN NOT MATCHED THENINSERT (id, column1, column2)VALUES (s.id, s.col1, s.col2);