如何在Yii2中插入批量数据库? [英] How to do a bulk database insert in Yii2?
问题描述
我想知道您如何在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
.
理想情况下,如果有一种解决方案可以提供一定的灵活性,例如能够编写大多数自己的语法,作为下面发布的答案之一,那将是一件好事:
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();
这篇关于如何在Yii2中插入批量数据库?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!