Doctrine2 - 多次插入一次 [英] Doctrine2 - Multiple insert in one shot

问题描述

我是新教的，对我来说还有一些模糊的地方。在这种情况下，我将使用循环和实体管理器在数据库中插入新记录。它工作正常，但我注意到，Doctrine使实体进行一次插入查询，这可能变得非常巨大。

使用Doctrine2和Symfony 2.3，我想知道我们如何设置它，所以它只会使其中包含所有值的1个插入查询（我们当然只说1个实体）。

我的意思是改变这个

  INSERT INTO dummy_table VALUES（x1，y1）
 INSERT INTO dummy_table VALUES（x2，y2）
  

进入

  INSERT INTO dummy_table VALUES（x1，y1），（x2，y2）
  

这是我的代码：

  $ em = $ this-> container-> get（'doctrine'） - > getManager（）; 
 
 foreach（$ items as $ item）{
 $ newItem = new Product（$ item ['datas']）; 
 $ em-> persist（$ newItem）; 
} 
 
 $ em-> flush（）; 
  

解决方案

根据这个答案，Doctrine2不允许你将多个INSERT语句组合成一个：

有些人似乎在想知道为什么Doctrine不使用
多插入（insert into（...）values（。 ..），（...），（...），...

首先，这种语法只支持mysql和更新的
postgresql版本;其次，在使用
AUTO_INCREMENT或SERIAL时，没有简单的方法来获取所有
所生成的标识符，并且ORM需要标识符为
最后，插入性能很少是ORM的
瓶颈，普通插入对于
大多数情况来说都非常快，如果你真的想做快速批量插入，那么一个
多插入不是最好的方式，即Postgres COPY或Mysql
LOAD DATA INFILE快几个数量级。

这些是在
ORM中执行多个插入mysql和postgresql的
抽象是不值得的努力的原因。

您可以在这里阅读有关Doctrine2批处理的更多信息：
http://www.doctrine-project.org/blog/doctrine2-batch-processing.html

您可以切换到DBAL或通过在一定量的插入后冲洗您的实体经理来处理您的数据：

  $ batchSize = 20 ; 
 
 foreach（$ items as $ i => $ item）{
 $ product = new Product（$ item ['datas']）; 
 
 $ em-> persist（$ product）; 
 
 //每20次插入一次数据库
 if（（$ i％$ batchSize）== 0）{
 $ em-> flush（）; 
 $ em-> clear（）; 
} 
} 
 
 //刷新剩余的对象
 $ em-> flush（）; 
 $ em-> clear（）; 
  

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.

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).

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();

解决方案

According to this answer, Doctrine2 does not allow you to combine multiple INSERT statements into one:

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.

You can read more about Doctrine2 batch processing here: 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:

$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();

这篇关于Doctrine2 - 多次插入一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！