php Doctrine2 - 一次多次插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18654530/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Doctrine2 - Multiple insert in one shot
提问by Molkobain
I'm new to Doctrine and there are still some blurred areas for me. In this case I'm inserting new record in the database using a loop and the entity manager. It works fine but I noticed that Doctrine make one insert query by entity, which can become pretty huge.
我是 Doctrine 的新手，对我来说仍然有一些模糊的地方。在这种情况下，我使用循环和实体管理器在数据库中插入新记录。它工作正常，但我注意到 Doctrine 按实体进行一个插入查询，这可能会变得非常庞大。
Using Doctrine2 and Symfony 2.3, I would like to know how we can set it up so it would make only 1 insert query with all the values in it (we are talking of 1 entity only of course).
使用 Doctrine2 和 Symfony 2.3，我想知道我们如何设置它，以便它只使用其中的所有值进行 1 个插入查询（我们当然只讨论 1 个实体）。
What I mean is changing this :
我的意思是改变这一点：
INSERT INTO dummy_table VALUES (x1, y1)
INSERT INTO dummy_table VALUES (x2, y2)
Into
进入
INSERT INTO dummy_table VALUES (x1, y1), (x2, y2)
Here is my code :
这是我的代码：
$em = $this->container->get('doctrine')->getManager();
foreach($items as $item){
$newItem = new Product($item['datas']);
$em->persist($newItem);
}
$em->flush();
回答by ukliviu
According to this answer, Doctrine2 does not allow you to combine multiple INSERT statements into one:
根据这个答案，Doctrine2 不允许您将多个 INSERT 语句合并为一个：
Some people seem to be wondering why Doctrine does not use multi-inserts (insert into (...) values (...), (...), (...), ...
First of all, this syntax is only supported on mysql and newer postgresql versions. Secondly, there is no easy way to get hold of all the generated identifiers in such a multi-insert when using AUTO_INCREMENT or SERIAL and an ORM needs the identifiers for identity management of the objects. Lastly, insert performance is rarely the bottleneck of an ORM. Normal inserts are more than fast enough for most situations and if you really want to do fast bulk inserts, then a multi-insert is not the best way anyway, i.e. Postgres COPY or Mysql LOAD DATA INFILE are several orders of magnitude faster.
These are the reasons why it is not worth the effort to implement an abstraction that performs multi-inserts on mysql and postgresql in an ORM.
有些人似乎想知道为什么 Doctrine 不使用多插入（insert into (...) values (...), (...), (...), ...
首先，此语法仅在 mysql 和较新的 postgresql 版本上受支持。其次，当使用 AUTO_INCREMENT 或 SERIAL 时，没有简单的方法可以在这种多插入中获取所有生成的标识符，并且 ORM 需要标识符来进行对象的身份管理。最后，插入性能很少成为 ORM 的瓶颈。对于大多数情况，普通插入已经足够快了，如果你真的想做快速批量插入，那么多插入无论如何都不是最好的方法，即 Postgres COPY 或 Mysql LOAD DATA INFILE 快几个数量级。
这就是为什么不值得在 ORM 中实现对 mysql 和 postgresql 执行多插入的抽象的原因。
You can read more about Doctrine2 batch processing here: http://www.doctrine-project.org/blog/doctrine2-batch-processing.html
您可以在此处阅读有关 Doctrine2 批处理的更多信息：http: //www.doctrine-project.org/blog/doctrine2-batch-processing.html
You can either switch to DBAL or resort to processing your data in small batches by flushing your entity manager after a set amount of inserts:
您可以切换到 DBAL 或通过在一定数量的插入后刷新实体管理器来小批量处理您的数据：
$batchSize = 20;
foreach ($items as $i => $item) {
$product = new Product($item['datas']);
$em->persist($product);
// flush everything to the database every 20 inserts
if (($i % $batchSize) == 0) {
$em->flush();
$em->clear();
}
}
// flush the remaining objects
$em->flush();
$em->clear();
回答by s29a
You can try this fork https://github.com/stas29a/doctrine2. It implements exactly what you want. I tested it in MySQL and it works fine and 5 times faster than that batch processing. This fork get a first inserted id and increments it in php for getting other id's. It works for most cases but not in all. So you need to understand what are you doing when using this fork.
你可以试试这个 fork https://github.com/stas29a/doctrine2。它完全实现了你想要的。我在 MySQL 中对其进行了测试，它运行良好，并且比批处理速度快 5 倍。这个 fork 获取第一个插入的 id 并在 php 中增加它以获取其他 id。它适用于大多数情况，但不适用于所有情况。所以你在使用这个 fork 的时候需要了解你在做什么。
回答by Sviatoslav Oleksiv
You can use executeUpdate($query, array $params = array(), array $types = array())method of DriverConnectioninterface to perform this action. However it's little tricky to bind multiple parameters.
您可以使用接口executeUpdate($query, array $params = array(), array $types = array())方法DriverConnection来执行此操作。然而，绑定多个参数并不难。
Data:
数据：
$postMetaData = [
[
'post_id' => $product->getId(),
'meta_key' => '_visibility',
'meta_value' => 'visible',
],
[
'post_id' => $product->getId(),
'meta_key' => '_stock_status',
'meta_value' => $insert['in_stock'] ? 'instock' : 'outofstock',
]
];
Bulk update method:
批量更新方法：
public function updateOrCreateBulk($posts, \Doctrine\DBAL\Connection $connection)
{
$placeholders = [];
$values = [];
$types = [];
foreach ($posts as $columnName => $value) {
$placeholders[] = '(?)';
$values[] = array_values($value);
$types[] = \Doctrine\DBAL\Connection::PARAM_INT_ARRAY;
}
return $connection->executeUpdate(
'INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES ' . implode(', ', $placeholders) . ' ON DUPLICATE KEY UPDATE `meta_value` = VALUES(`meta_value`)',
$values,
$types
);
}
回答by idipous
I have not tested it but it seems possible to do this with a collection.
我还没有测试过，但似乎可以通过集合来做到这一点。
$collection = new Doctrine_Collection('tablename');
$collection->add($record1);
$collection->add($record2);
$collection->add($record3);
$collection->add($record4);
$collection->save();
Of course you should have the add in the loop.
当然，您应该在循环中添加 add。
