MYSQL 的 DROP/DELETE/TRUNCATE

黎 浩然/ 9 7 月, 2022/ 数据库/DATABASE, 计算机/COMPUTER/ 0 comments

在数据库管理中，DROP、DELETE和TRUNCATE都是用来移除数据的命令，但它们的用途、效果和内部工作原理有显著差异。以下是每个命令的详细介绍、它们的共同点和区别：

DELETE

作用

• DELETE命令用于删除表中的一行或多行记录，并且可以包含WHERE子句来指定要删除的记录。

原理

• DELETE操作是DML（数据操作语言）命令，它在删除数据时会记录每个删除的行到事务日志中，这允许操作被回滚。
• DELETE操作触发任何与表相关联的删除触发器。

示例

DELETE FROM Employees WHERE EmployeeID = 123;

TRUNCATE

作用

• TRUNCATE命令用于快速清空一个表的所有行，但不删除表本身。

原理

• TRUNCATE是DDL（数据定义语言）命令，它通过释放存储表数据的数据页来工作，并在很多数据库系统中重新设置自增计数器（如果有的话）。
  在大多数关系型数据库系统中，使用TRUNCATE命令清空带有外键约束的表可能会受到限制，这主要取决于外键约束的存在及其被设置的具体规则。为什么可能有限制？
  外键约束的作用
  • 外键约束用于确保参照完整性，即保持数据库中的数据关联性和一致性。它确保一个表（子表）中的字段（外键）必须对应于另一个表（父表）中的相应记录的唯一键（通常是主键）。
  TRUNCATE的操作方式
  • TRUNCATE命令用于快速清空表中的所有数据，它直接删除表中的数据而不触发任何行级触发器，通常也不逐行检查外键约束。
  • 因为TRUNCATE是DDL（数据定义语言）操作，它通过释放存储数据的页来工作，而非逐条删除数据，这常常意味着它不能逐个验证各行是否满足外键约束。在MySQL中，如果外键约束存在，TRUNCATE通常会失败。
  • MySQL要先删除外键约束或使用DELETE命令来清空表，因为DELETE会逐行检查并应用外键约束。
  如何处理这种限制？
  • 使用DELETE命令：如果因为外键约束而无法使用TRUNCATE，可以使用DELETE FROM table_name;来代替。虽然这比TRUNCATE慢（因为它是逐行处理，并记录日志），但它会遵守外键约束。
  • 临时移除外键约束：在某些情况下，你可以在TRUNCATE前暂时移除外键约束，然后再重新添加。注意，这种做法需要谨慎操作，因为它可能会破坏数据完整性。
  
  总结
  
  总的来说，是否可以使用TRUNCATE命令清空有外键约束的表，取决于具体的数据库系统和设置。通常情况下，由于TRUNCATE不逐行处理数据也不检查外键约束，许多数据库系统不允许对参与外键约束的表使用TRUNCATE。理解你的数据库系统的具体行为是确保数据完整性和有效操作的关键。
  
• 相比DELETE，TRUNCATE通常不记录每一行的删除，因此速度更快~~，但这也意味着不能被回滚~~（某些数据库系统如SQL Server除外，其中TRUNCATE可以在事务中回滚）。
• TRUNCATE通常不触发表的删除触发器。

示例

TRUNCATE TABLE Employees;

DROP

作用

• DROP命令用于完全删除数据库中的一个表、视图或其他对象，包括其结构和数据。

原理

• DROP也是一个DDL命令，它会从数据库中移除表和表的元数据，并释放存储该表数据的空间。
• DROP操作不仅删除表中的数据，还删除表的定义~~，并且这个操作不能被回滚~~。
• DROP通常也不触发删除触发器。

示例

DROP TABLE Employees;

共同点与区别

共同点

• 所有这三个命令都与删除数据或数据结构有关。
• 它们都是基本的数据库操作，普遍存在于所有关系型数据库管理系统中。

区别

• 操作范围：DELETE操作是可控的，可以通过WHERE子句指定删除哪些行。TRUNCATE和DROP则是更“粗暴”的操作，TRUNCATE删除表中所有行，而DROP删除整个表。
• 性能：TRUNCATE通常比DELETE快，因为它不逐行删除数据，而是直接释放整个表的数据页。DROP最快，因为它直接删除整个表。
• 回滚能力：在大多数数据库中，DELETE操作可以回滚，而TRUNCATE在某些数据库中可以回滚，在其他数据库中则不可以。DROP操作通常不能回滚。 是否支持回滚的能力主要取决于两个关键因素：事务日志的记录和事务的控制方式（包括自动提交设置）。
• 事务日志记录DELETE命令作为一种数据操作语言（DML）操作，会在执行过程中对每个删除的行生成事务日志（Transaction Log）。这些日志记录了足够的信息（例如，哪些行被删除以及它们的原始值），这使得操作可以在需要时被完全回滚。日志的存在是使得任何DML操作（包括DELETE）能够支持回滚的基础。
• 事务的控制方式和自动提交无论数据库的自动提交设置如何，DELETE操作本身都是可以支持回滚的，因为它能生成完整的日志信息。然而，实际上能否回滚一个DELETE操作，还取决于事务的控制方式：
  • 自动提交开启：在许多数据库系统中，如MySQL和SQL Server，默认情况下自动提交是开启的。这意味着如果没有显式地开始一个事务，每个DELETE命令在执行完毕后立即被视为一个完整的事务并自动提交。一旦DELETE操作提交，就不能被回滚。
  • 显式事务控制：如果关闭了自动提交，或者显式地使用了BEGIN TRANSACTION（或相应的命令）来开启一个事务，那么在执行COMMIT命令之前，所有包括DELETE在内的操作都可以被ROLLBAC命令撤销。
  示例
  在自动提交开启的环境下，DELETE操作无法回滚：
  DELETE FROM Employees WHERE EmployeeID = 123;–
  如果自动提交开启，此操作不能回滚 在显式事务控制下，DELETE操作可以回滚：
  BEGIN TRANSACTION; — 开始一个新的事务
  DELETE FROM Employees WHERE EmployeeID = 123;
  ROLLBACK; — 撤销 DELETE 操作
  结论
  因此，DELETE支持回滚的原因是它记录了足够的日志信息来撤销每一行的删除，这是基础。然而，实际上能否回滚一个DELETE操作还取决于数据库是否处于自动提交模式，或者是否有显式的事务控制。
• 日志记录：DELETE记录详细的日志（每行数据的删除），而TRUNCATE记录的日志较少，DROP则几乎不记录日志。

结论

选择使用DELETE、TRUNCATE还是DROP命令取决于具体的需求：是否需要删除部分行（使用DELETE）、快速清空表（使用TRUNCATE）或彻底移除表（使用DROP）。理解它们之间的差异是数据库操作中保证数据安全和操作效率的关键。

对于最新版本的MySQL，我们需要区分DROP和TRUNCATE在事务中的行为，尤其是当使用InnoDB这种支持事务的存储引擎时。以下是对这两个命令在事务中回滚能力的更详细解释：

DROP

• 在使用InnoDB存储引擎时，DROP TABLE命令可以在事务中执行，并且支持回滚。这意味着如果你在一个事务块中执行了DROP TABLE并随后执行了ROLLBACK命令，那么被删除的表将被恢复。

TRUNCATE

• 对于InnoDB存储引擎，从MySQL 5.0.13版本开始，TRUNCATE TABLE被处理为一个事务安全操作，类似于一个没有where子句的DELETE命令。这意味着它现在支持在事务中回滚。但是，它不会为删除的数据生成undo记录（用于回滚非索引数据的修改），所以它比DELETE操作效率更高，但是在事务日志中的表现和DELETE一样，都可以被回滚。
• 重要的是，TRUNCATE对于AUTO_INCREMENT列的处理是重置其值，这在回滚时可能不会恢复到原来的状态。因此，尽管操作本身可以回滚，AUTO_INCREMENT的行为可能会有所不同。

总结

在最新版本的MySQL中，如果使用InnoDB存储引擎：

• DROP TABLE可以在事务中执行，并且可以回滚。
• TRUNCATE TABLE也被视为事务安全的，可以在事务中回滚，但它的某些行为（如AUTO_INCREMENT的重置）可能与DELETE操作有所不同。

这些特性使得InnoDB在处理需要事务安全性的场景时非常有用。然而，对于那些关键的操作，始终建议在生产部署前在你的具体环境中进行测试以确认具体行为，因为不同的配置和MySQL版本可能会影响具体的行为。