如何使用Doctrine 2截断表？

Question

我认为我需要使用Doctrine2构建一个本机查询来截断表。

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

这会导致错误

SQLSTATE[HY000]: General error

我需要更改我的代码才能使其正常工作吗？

- murze

---

你的语法是正确的。你尝试过使用“DELETE FROM Article”吗？ - ron tornambe

也许有一个外键引用了正在被删除的表中的某个字段。您尝试从MySQL控制台执行查询了吗？ - galymzhan

Answer 1

警惕截断表

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

---

DDL语句执行隐式提交

truncate table语句是数据定义语言（DDL）语句，因此截断表语句会在执行时向数据库触发隐式COMMIT。如果执行TABLE TRUNCATE，则数据库将被隐式提交--即使TABLE TRUNCATE在START TRANSACTION语句内，您的表也将被截断，ROLLBACK将无法恢复它。

由于截断表语句执行隐式提交，Maxence的答案并不像预期那样工作（但它不是错误的，因为问题是“如何截断表格”）。他的答案不按预期工作，因为它在try块中截断了表，并假设如果出现问题，则可以在catch块中还原该表。这是一个不正确的假设。

本主题中其他用户的评论和经验

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

user2130519不幸地因提供正确答案而被投票否定（直到我投了赞成票），虽然他没有为自己的答案提供解释，但这就像不展示你的工作就做数学一样。

我的建议：使用DELETE FROM

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

如果您想了解何时应该使用DELETE FROM vs TRUNCATE的提示，请参见 Pros & Cons of TRUNCATE vs 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();
$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 <tableName> AUTO_INCREMENT = 1。

- cmt

---

因为我不知道它是什么意思，所以我必须通过谷歌搜索来了解：$con->exec('ALTER TABLE ' . $cmd->getTableName() . ' AUTO_INCREMENT = 1;');翻译：该代码用于重置MySQL数据表的自增计数器。 - MonocroM

在执行修改命令后，请不要忘记添加 $em->clear();，否则您有可能会将旧记录插入到数据库中。 - vvolkov

这真的有一种“孩子们在家不要尝试这个”的感觉！不过回答思路很清晰。我猜如果我通过alter table来重置自增编号，那么根据你所说的，它会破坏回滚的能力？ - Scott Flack

对于使用 PostgreSQL 的人来说，一般 DDL 命令可以在事务中运行，并且如文档所述：TRUNCATE 在处理表中的数据时是安全的：如果周围的事务没有提交，则截断将被安全地回滚。 - Jan Mares

Answer 2

这是我正在使用的代码：

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

- Maxence

---

刚刚测试了这段代码，使用Symfony 2.1.0版本，一切正常！谢谢！ - aletzo

我正准备开始编写特定于引擎的代码来截断表，感谢您的有用贡献 :) - Lee Davis

MySQL简化版代码：$em->getConnection()->query('START TRANSACTION;SET FOREIGN_KEY_CHECKS=0; TRUNCATE table1; TRUNCATE table2;SET FOREIGN_KEY_CHECKS=1; COMMIT;')翻译：这段代码执行了MySQL数据库的事务操作，先启动一个事务（START TRANSACTION），然后暂时禁用外键约束（SET FOREIGN_KEY_CHECKS=0），接着清空table1和table2两个表的数据（TRUNCATE table1; TRUNCATE table2;），最后再开启外键约束（SET FOREIGN_KEY_CHECKS=1），并提交整个事务（COMMIT）。 - E Ciotti

使用DBAL的好处在于，名称中的“AL”部分代表“抽象层”。如果您直接针对MySQL进行操作，可以使用PDO并完全忘记DBAL。将“TRUNCATE table”设置在字符串内可能会破坏与其他引擎的兼容性。 - Xavi Montero

Answer 3

或者您可以尝试这个:

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

如果您有关联，您应该小心处理这些链接的实体。

- piers.warmers

---

清空数据表并不等同于截断数据表。 - jayem

是的，自增变量没有被设置为零。 - E Ciotti

删除数据比清空数据慢得多。 - Riccardo Galli

在某些情况下可能会有用：例如：我正在编写功能测试，由于我的表中只有很少的实体，所以我猜性能不应该太差。 - greg0ire

这很酷，可以重置一个小表格（如设置或小常量容器），因为它非常易读。 - Erdal G.

Answer 4

这是单元测试中从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;
}

请注意，如果您不使用$em->flush()，您可能会在下一个对Doctrine的查询中遇到问题。

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

- Borys Ermokhin

Answer 5

这是如何使用Doctrine在Symfony中删除实体存储库中的所有实体（不忽略外键检查）的方法。该函数返回已删除实体的计数。

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

    $qb->delete();

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

- Sebastian Viereck