Oracle的批量插入问题

问题:现在有个问题,oci驱动下，batchInsert 生成的sql 是insert all into 方式，即使主键ID自增方式为 GENERATED AS IDENTITY 依然无法进行批量执行，还是会出现ID主键错误

你反馈的这个问题非常典型，涉及Yii2 + Oracle（oci驱动）+ batchInsert + GENERATED AS IDENTITY的兼容性。
下面我详细分析原因，并给出可行的解决方案。

1. 问题本质

• Yii2的batchInsert在MySQL/PG下生成的是INSERT INTO ... VALUES (...), (...)，但在Oracle（oci驱动）下会生成INSERT ALL INTO ...语法。
• Oracle的INSERT ALL在配合GENERATED AS IDENTITY时，不会为每一行自动分配ID，而是只分配一次，导致多行ID冲突（主键重复）。
• 这其实是Oracle自身的限制，也是Yii2和oci驱动的兼容性问题。

2. 现象复现

假设表结构如下：

CREATE TABLE test_identity (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100)
);

用INSERT ALL批量插入：

INSERT ALL
  INTO test_identity (name) VALUES ('张三')
  INTO test_identity (name) VALUES ('李四')
SELECT 1 FROM DUAL;

结果：报主键冲突！
因为Oracle只分配了一个ID，所有行都用同一个ID。

3. 官方文档说明

Oracle官方文档明确指出：

“对于GENERATED AS IDENTITY的列，INSERT ALL语句只会生成一次identity值，所有行都用同一个值。”

4. 解决方案

A. 避免用INSERT ALL，改用多条单独INSERT

• 用多条INSERT INTO ... VALUES ...，每条语句Oracle都会分配新的ID。
• 这也是Yii2在MySQL/PG下的做法。

手动实现（推荐）

$data = [
    ['张三'],
    ['李四'],
    // ...
];
$rows = [];
foreach ($data as $row) {
    $rows[] = "('" . implode("','", array_map('addslashes', $row)) . "')";
}
$sql = "INSERT INTO test_identity (name) VALUES " . implode(',', $rows);
Yii::$app->db->createCommand($sql)->execute();

这样生成的SQL是：INSERT INTO ... VALUES (...), (...), ...，每行分配不同ID。

或循环插入（小批量时可用）

foreach ($data as $row) {
    Yii::$app->db->createCommand()
        ->insert('test_identity', ['name' => $row[0]])
        ->execute();
}

B. 自定义批量插入方法

• 可以写一个helper，自动拼接多条INSERT INTO ... VALUES ...，而不是用INSERT ALL。

C. ORM层面规避

• 如果用ActiveRecord，建议循环save()，或者用自定义的批量插入方法。
• 也可以重写batchInsert，让其在Oracle下生成多条INSERT INTO ... VALUES ...。

D. 反馈/升级Yii2驱动

• 你可以向Yii2官方或oci驱动反馈这个兼容性问题，未来可能会修复。

5. 总结

• Oracle的GENERATED AS IDENTITY不支持INSERT ALL批量分配ID，只能一行一分配。
• 解决办法：用多条INSERT INTO ... VALUES ...，不要用INSERT ALL。
• Yii2下建议：自定义批量插入方法，或循环插入。

6. 参考代码（自动拼接多条INSERT）

function batchInsertOracle($table, $columns, $rows) {
    $sqls = [];
    foreach ($rows as $row) {
        $values = [];
        foreach ($row as $val) {
            $values[] = is_null($val) ? 'NULL' : "'" . addslashes($val) . "'";
        }
        $sqls[] = "INSERT INTO $table (" . implode(',', $columns) . ") VALUES (" . implode(',', $values) . ")";
    }
    $db = Yii::$app->db;
    foreach ($sqls as $sql) {
        $db->createCommand($sql)->execute();
    }
}

这样每条语句都能正确分配自增ID。

如需更自动化的批量插入工具、SQL生成器，或和现有ORM集成的建议，请告诉我你的具体需求，我可以帮你写出适合你项目的代码！

本作品采用《CC 协议》，转载必须注明作者和本文链接