Doctrine2 - 一次多次插入

2022-01-03 00:00:00 php mysql symfony doctrine-orm

我是 Doctrine 的新手，对我来说仍然有一些模糊的地方.在这种情况下，我使用循环和实体管理器在数据库中插入新记录.它工作正常，但我注意到 Doctrine 按实体创建一个插入查询，这可能会变得非常庞大.

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.

使用 Doctrine2 和 Symfony 2.3，我想知道我们如何设置它，以便它只使用其中的所有值进行 1 个插入查询(我们当然只讨论 1 个实体).

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

我的意思是改变这个:

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 语句组合成一个:

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

有些人似乎想知道为什么 Doctrine 不使用多插入(插入 (...) 值 (...), (...), (...), ...

Some people seem to be wondering why Doctrine does not use multi-inserts (insert into (...) values (...), (...), (...), ...

首先，这个语法只在mysql及更新版本上支持PostgreSQL 版本.其次，没有简单的方法可以掌握所有使用时在这种多插入中生成的标识符AUTO_INCREMENT 或 SERIAL 和 ORM 需要标识的标识符对象的管理.最后，插入性能很少是ORM 的瓶颈.普通的插入速度足够快大多数情况下，如果你真的想做快速批量插入，那么无论如何，多插入不是最好的方法，即 Postgres COPY 或 MysqlLOAD DATA INFILE 快了几个数量级.

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.

这些是不值得努力实施一个在 mysql 和 postgresql 上执行多插入的抽象ORM.

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.

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

You can read more about Doctrine2 batch processing here: http://www.doctrine-project.org/blog/doctrine2-batch-processing.html

您可以切换到 DBAL 或通过在一定数量的插入后刷新实体管理器来小批量处理您的数据:

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

相关文章