如何在 Yii2 中进行批量数据库插入?

2022-01-04 00:00:00 mysql yii2 yii

我想知道你是如何在 Yii2 中进行批量数据库 INSERT 的?

I'm wondering how you go about doing a bulk database INSERT in Yii2?

例如一个普通的 INSERT 我想要这样:

For example a normal INSERT I would like so:

$sql = $this->db("INSERT INTO some_table(id,my_value) VALUES(:id,:my_value)");
$sql->bindValues([':id' => $id, ':my_value' => $my_value]);
$sql->execute();

现在如果我想创建一个批量INSERT怎么办?

Now what if I wanted to create a bulk INSERT?

如果没有绑定值，你可以像这样:

Without binding values you could it something like:

foreach ($foo as $bar) {
    $data_sql .= '(' . $id . ',' "'" . $bar . "'),"
}

$data_sql = rtrim($data_sql, ',');

$sql = $this->db("INSERT INTO some_table(id,my_value) VALUES" . $data_sql);
$sql->execute();

但是如果你仍然想要绑定这些值，你怎么能做到这一点呢?

But how can you achieve this if you still want to bind the values?

这个问题与链接的问题不同，因为我没有使用 ActiveRecord.

This question is not the same as the linked one as I am not using ActiveRecord.

编辑 2:

理想情况下，如果有一种解决方案提供一定的灵活性，例如能够编写您自己的大部分语法，就像下面发布的答案之一一样，那就太好了:

Ideally it would be good if there was a solution that offered some flexibility, such as being able to write most of your own syntax, as one of the answers posted below:

Yii::$app->db->createCommand()->batchInsert('tableName', ['id', 'title', 'created_at'], [
    [1, 'title1', '2015-04-10'],
    [2, 'title2', '2015-04-11'],
    [3, 'title3', '2015-04-12'],
])->execute();

...不提供.对于这种特殊情况，我需要使用 IGNORE 语句，上面的解决方案没有提供.

...doesn't offer that. For this particular situation I need to use the IGNORE statement, which the above solution doesn't offer.

推荐答案

有一个特殊的 batchInsert 方法:

There is a special batchInsert method for that:

Yii::$app->db->createCommand()->batchInsert('tableName', ['id', 'title', 'created_at'], [
    [1, 'title1', '2015-04-10'],
    [2, 'title2', '2015-04-11'],
    [3, 'title3', '2015-04-12'],
])->execute();

相关文章