MySQL和Oracle批量插入SQL差异详解

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 ALL
    INTO 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 ALL
    INTO 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 ALL
    SELECT value4, value5, value6 FROM DUAL
) s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET 
        t.column1 = s.col1,
        t.column2 = s.col2
WHEN NOT MATCHED THEN
    INSERT (id, column1, column2)
    VALUES (s.id, s.col1, s.col2);