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

问题描述

  $ emptyRsm = new \ 
我假设我需要使用Doctine2构建本机查询来截断表。 Doctrine\ORM\Query\ResultSetMapping（）; 
 $ sql ='TRUNCATE TABLE Article'; 
 $ query = em（） - > createNativeQuery（$ sql，$ emptyRsm）; 
 $ query-> execute（）; 
  

这给出错误

  SQLSTATE [HY000]：一般错误
  

我需要更改为我的代码让这个工作？

解决方案

小心截断表

小心在任何RDBMS中截断表，特别是如果要使用显式事务提交/回滚功能。

DDL语句执行隐式提交

截断表语句是数据定义语言（DDL）语句，因此 truncate table语句在执行时触发隐藏的 COMMIT 到数据库。如果执行 TABLE TRUNCATE ，则数据库将被隐式提交到 - 即使 TABLE TRUNCATE 在 START TRANSACTION 语句 - 您的表将被截断，并且 ROLLBACK 将不还原它。

因为truncate table语句执行隐式提交，所以Maxence的答案不能像预期的那样执行（但是没有错，因为问题是截断表）。他的答案没有按预期的方式执行，因为它截断了一个 try 块中的表，并假定表可以在 catch 阻止，如果出现问题。这是一个不正确的假设。

其他用户的评论&在线程中的经验

ChrisAelbrecht无法使Maxence的解决方案正常工作，因为即使truncate table语句处于显式事务中，也无法回滚截断表语句。

user2130519，不幸的是，被提拔正确答案（-1，直到我升华为止） - 虽然他没有证明他的答案，这就像做数学没有显示您的工作。

我的建议 DELETE FROM

我的建议是使用 DELETE FROM 。在大多数情况下，它将按照开发者的期望来执行。但是， DELETE FROM 也不会有缺点 - 您必须显式地重置表的自动增量值。要重置表的自动增量值，您必须使用另一个DDL语句 - ALTER TABLE - 并且再次不要使用 ALTER try 块中的TABLE 。如果您想要使用 DELETE FROM vs <$>的提示，请按照预期的方式工作。

c $ c> TRUNCATE 请参阅优点&缺少TRUNCATE和DELETE FROM 。

如果你真的必须，以下是截断

的所有内容。如果您真的想使用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'）; 
  

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

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

  $ cmd = $ em-> getClassMetadata（$ className ）; 
 $ connection = $ em-> getConnection（）; 
 $ connect-> beginTransaction（）; 
 
 try {
 $ connection-> query（'SET FOREIGN_KEY_CHECKS = 0'）; 
 $ connection-> query（'DELETE FROM'。$ cmd-> getTableName（））; 
 //谨防ALTER TABLE在这里 - 这是另一个DDL语句，并将导致
 //隐式提交。 
 $ connection-> query（'SET FOREIGN_KEY_CHECKS = 1'）; 
 $ connection-> commit（）; 
} catch（\Exception $ e）{
 $ connection-> rollback（）; 
} 
  

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

I assume that I need to build a native query to truncate a table using 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?

解决方案

Beware of Truncating Tables

Beware of truncating tables in any RDBMS, especially if you want to use explicit transactions for commit/rollback functionality.

DDL statements perform an implicit-commit

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.

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.

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.

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.

My recommendation DELETE FROM

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.

If you want tips on when you should use DELETE FROM vs TRUNCATE see Pros & Cons of TRUNCATE vs 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)

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

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

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