如何使用 Doctrine 2 截断表格? [英] How to truncate a table using Doctrine 2?

问题描述

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

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

$emptyRsm = new DoctrineORMQueryResultSetMapping();
$sql = 'TRUNCATE TABLE Article';
$query = em()->createNativeQuery($sql, $emptyRsm);
$query->execute();

这给出了错误

SQLSTATE[HY000]: General error

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

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

推荐答案

注意截断表

当心在任何 RDBMS 中截断表，尤其是当您想使用显式事务来实现提交/回滚功能时.请阅读此答案的我的建议".

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.

Truncate table 语句是数据定义语言 (DDL) 语句，因此truncate table 语句在执行时触发对数据库的隐式 COMMIT.如果您执行 TABLE TRUNCATE，那么数据库将被隐式提交——即使 TABLE TRUNCATE 在 START TRANSACTION 语句中——您的表将被截断，ROLLBACK 将不恢复它.

Truncate table statements are data definition language (DDL) statements, and as such truncate table statements trigger an implicit COMMIT to the database upon their execution. If you perform a TABLE TRUNCATE then the database is implicitly committed to--even if the TABLE TRUNCATE is within a START TRANSACTION statement--your table will be truncated and a ROLLBACK will not restore it.

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

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 try block, and assumes that the table can be restored in the catch block, if something goes wrong. This is an incorrect assumption.

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

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.

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

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.

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

My recommendation is to use DELETE FROM. In most cases, it will perform as the developer expects. But, DELETE FROM does 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 TABLE in your try block. It won't work as expected.

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

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

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

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)

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

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

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

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

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

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

这篇关于如何使用 Doctrine 2 截断表格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！