php 如何使用 Doctrine 2 截断表格？

How to truncate a table using Doctrine 2?

提问by murze

I assume that I need to build a native query to truncate a table using Doctine2.

我假设我需要构建一个本地查询来使用 Doctine2 截断表。

$emptyRsm = new \Doctrine\ORM\Query\ResultSetMapping();
$sql = 'TRUNCATE TABLE Article';
$query = em()->createNativeQuery($sql, $emptyRsm);
$query->execute();

This gives the error

这给出了错误

SQLSTATE[HY000]: General error

What do I need to change to my code to make this work?

我需要对代码进行哪些更改才能使其正常工作？

回答by cmt

Beware of Truncating Tables

小心截断表

Beware of truncating tables in any RDBMS, especially if you want to use explicit transactions for commit/rollback functionality. Please read the 'My recommendation' of this answer.

当心在任何 RDBMS 中截断表，特别是如果您想使用显式事务进行提交/回滚功能。请阅读此答案的“我的建议”。

---

---

DDL statements perform an implicit-commit

DDL 语句执行隐式提交

Truncate table statements are data definition language (DDL) statements, and as such truncate table statements trigger an implicit COMMITto the database upon their execution. If you perform a TABLE TRUNCATEthen the database is implicitly committed to--even if the TABLE TRUNCATEis within a START TRANSACTIONstatement--your table will be truncated and a ROLLBACKwill notrestore it.

Truncate table 语句是数据定义语言 (DDL) 语句，因此truncate table 语句COMMIT在执行时会触发数据库的隐式。如果执行TABLE TRUNCATE，则数据库隐含承诺-即使TABLE TRUNCATE是内START TRANSACTION声明-你的表将被截断和ROLLBACK将不会恢复。

Because truncate table statements perform implicit commits, Maxence's answer does not perform as expected(but it's not wrong, because the question was "how to truncate a table"). His answer does not perform as expected because it truncates the table in a tryblock, and assumes that the table can be restored in the catchblock, if something goes wrong. This is an incorrect assumption.

因为 truncate table 语句执行隐式提交，Maxence 的回答没有按预期执行（但这并没有错，因为问题是“如何截断表”）。他的回答没有按预期执行，因为它截断了try块中的表，并假设catch如果出现问题，可以在块中恢复表。这是一个错误的假设。

Other user's comments & experiences in this thread

其他用户在此线程中的评论和体验

ChrisAelbrecht was unable to get Maxence's solution to work properly because you cannot rollback a truncate table statement, even if the truncate table statement is in an explicit transaction.

ChrisAelbrecht 无法使 Maxence 的解决方案正常工作，因为您无法回滚 truncate table 语句，即使 truncate table 语句在显式事务中也是如此。

user2130519, unfortunately, was downvoted (-1 until I upvoted) for providing the correct answer--although he did so without justifying his answer, which is like doing math without showing your work.

不幸的是，user2130519 因提供正确答案而被否决（-1，直到我投了赞成票）——尽管他这样做没有证明他的答案是正确的，这就像在做数学而不展示你的工作一样。

My recommendation DELETE FROM

我的推荐 DELETE FROM

My recommendation is to use DELETE FROM. In most cases, it will perform as the developer expects. But, DELETE FROMdoes not come without drawbacks either--you must explicitly reset the auto increment value for the table. To reset the auto increment value for the table, you must use another DDL statement--ALTER TABLE--and, again, don't use ALTER TABLEin your tryblock. It won't work as expected.

我的建议是使用DELETE FROM. 在大多数情况下，它将按照开发人员的预期执行。但是，DELETE FROM也不是没有缺点——您必须明确地重置表的自动增量值。要重置表的自动增量值，您必须使用另一个 DDL 语句ALTER TABLE——同样，不要ALTER TABLE在try块中使用。它不会按预期工作。

If you want tips on when you should use DELETE FROMvs TRUNCATEsee Pros & Cons of TRUNCATE vs DELETE FROM.

如果您想了解何时应该使用DELETE FROMvs 的提示，TRUNCATE请参阅TRUNCATE 与 DELETE FROM 的优缺点。

If you really must, here's how to truncate

如果你真的必须，这里是如何截断

Now, with all that said. If you really want to truncate a table using Doctrine2, use this: (Below is the portion of Maxence's answer that correctly truncates a table)

现在，说了这么多。如果您真的想使用 Doctrine2 截断表格，请使用：（以下是 Maxence 正确截断表格的部分答案）

$cmd = $em->getClassMetadata($className);
$connection = $em->getConnection();
$dbPlatform = $connection->getDatabasePlatform();
$connection->query('SET FOREIGN_KEY_CHECKS=0');
$q = $dbPlatform->getTruncateTableSql($cmd->getTableName());
$connection->executeUpdate($q);
$connection->query('SET FOREIGN_KEY_CHECKS=1');

How to delete a table with rollback/commit functionalty.

如何删除具有回滚/提交功能的表。

But, if you want rollback/commit functionality, you must use DELETE FROM: (Below is a modified version of Maxence's answer.)

但是，如果您想要回滚/提交功能，则必须使用DELETE FROM:（以下是 Maxence 答案的修改版本。）

$cmd = $em->getClassMetadata($className);
$connection = $em->getConnection();
$connection->beginTransaction();

try {
    $connection->query('SET FOREIGN_KEY_CHECKS=0');
    $connection->query('DELETE FROM '.$cmd->getTableName());
    // Beware of ALTER TABLE here--it's another DDL statement and will cause
    // an implicit commit.
    $connection->query('SET FOREIGN_KEY_CHECKS=1');
    $connection->commit();
} catch (\Exception $e) {
    $connection->rollback();
}

If you need to reset the auto increment value, remember to call ALTER TABLE <tableName> AUTO_INCREMENT = 1.

如果需要重置自动增量值，记得调用ALTER TABLE <tableName> AUTO_INCREMENT = 1.

回答by Maxence

Here is the code I'm using:

这是我正在使用的代码：

$cmd = $em->getClassMetadata($className);
$connection = $em->getConnection();
$dbPlatform = $connection->getDatabasePlatform();
$connection->beginTransaction();
try {
    $connection->query('SET FOREIGN_KEY_CHECKS=0');
    $q = $dbPlatform->getTruncateTableSql($cmd->getTableName());
    $connection->executeUpdate($q);
    $connection->query('SET FOREIGN_KEY_CHECKS=1');
    $connection->commit();
}
catch (\Exception $e) {
    $connection->rollback();
}

回答by piers.warmers

Or you could just try this:

或者你可以试试这个：

$this->getEm()->createQuery('DELETE AcmeBundle:Post p')->execute();

If you have relations you should be careful to handle the linked entities.

如果你有关系，你应该小心处理链接的实体。

回答by Borys Ermokhin

This is example truncating method from trait in unit tests.

这是在单元测试中从 trait 截断方法的示例。

/**
 * Cleanup any needed table abroad TRUNCATE SQL function
 *
 * @param string $className (example: App\Entity\User)
 * @param EntityManager $em
 * @return bool
 */
private function truncateTable (string $className, EntityManager $em): bool {
    $cmd = $em->getClassMetadata($className);
    $connection = $em->getConnection();
    $connection->beginTransaction();

    try {
        $connection->query('SET FOREIGN_KEY_CHECKS=0');
        $connection->query('TRUNCATE TABLE '.$cmd->getTableName());
        $connection->query('SET FOREIGN_KEY_CHECKS=1');
        $connection->commit();
        $em->flush();
    } catch (\Exception $e) {
        try {
            fwrite(STDERR, print_r('Can\'t truncate table ' . $cmd->getTableName() . '. Reason: ' . $e->getMessage(), TRUE));
            $connection->rollback();
            return false;
        } catch (ConnectionException $connectionException) {
            fwrite(STDERR, print_r('Can\'t rollback truncating table ' . $cmd->getTableName() . '. Reason: ' . $connectionException->getMessage(), TRUE));
            return false;
        }
    }
    return true;
}

Please note, that if you do not use $em->flush(), you have a risk to have a problem with next query to doctrine.

请注意，如果您不使用$em->flush()，您就有可能在下一次查询准则时出现问题。

Also you must understand, that if you use this method in a controller, you must change the lines fwrite(STDERR, print_r(...to something your logger service can use.

此外，您必须了解，如果您在控制器中使用此方法，则必须将行更改为fwrite(STDERR, print_r(...您的记录器服务可以使用的内容。

回答by Sebastian Viereck

This is how you can delete all entites from a entity reposiotry in symfony using doctrine (not ignoring foreign key checks). The functions returns the count of the deleted entites.

这就是您可以使用学说（不忽略外键检查）从 symfony 中的实体存储库中删除所有实体的方法。这些函数返回已删除实体的计数。

/**
 * @return int
 */
public function deleteAll(): int
{
    $qb = $this->createQueryBuilder('t');

    $qb->delete();

    return $qb->getQuery()->getSingleScalarResult() ?? 0;
}