mysql删除表

本文主要讲mysql中三种删除表的操作，ｄｅｌｅｔｅ语句、ｔｒｕｎｃａｔｅ语句以及ｄｒｏｐ语句的区别

一.关联与区别1.区别

ｄｅｌｅｔｅ、ｔｒｕｎｃａｔｅ和ｄｒｏｐ是用于删除数据库中的数据或对象的SQL语句，但它们有不同的功能和用法。

ｄｅｌｅｔｅ语句：ｄｅｌｅｔｅ语句用于从表中删除特定的行或符合特定条件的行。ｄｅｌｅｔｅ语句是一种有条件的删除操作，它可以通过WHERE子句指定特定的条件来选择要删除的行。ｄｅｌｅｔｅ语句只能删除数据行，而不会删除表本身或其他对象。删除操作通常可以回滚，也就是能够撤回删除操作并恢复被删除的数据。ｔｒｕｎｃａｔｅ语句：ｔｒｕｎｃａｔｅ语句用于从表中删除所有的行，但保留表的结构。相比于ｄｅｌｅｔｅ，ｔｒｕｎｃａｔｅ是一种非常快速、高效的操作，因为它不需要逐行删除数据，而是直接将表中的数据页清空。ｔｒｕｎｃａｔｅ操作不会触发事务日志，无法回滚。因此，一旦执行ｔｒｕｎｃａｔｅ操作，被删除的数据将无法恢复。ｄｒｏｐ语句：ｄｒｏｐ语句用于删除数据库中的对象，包括表、视图、索引等。ｄｒｏｐ语句是一种物理删除，它会完全删除指定的对象以及与该对象相关的所有数据和定义。ｄｒｏｐ操作是不可恢复的，因此在执行ｄｒｏｐ操作之前需要特别小心，以防止误操作导致数据丢失。2.执行速度ｄｒｏｐ> ｔｒｕｎｃａｔｅ > ｄｅｌｅｔｅ3.使用场景

在删除数据库中的数据时，常常会存在这样几种情况：

已经确定删除整张表和数据，也不需要恢复数据了，用 ｄｒｏｐ；删除数据不删除表，但是删除之后还有可能会后悔，用 ｄｅｌｅｔｅ；只想删除数据，不用删除表，删除数据后不会后悔了，用 ｔｒｕｎｃａｔｅ；5.表和索引所占空间：当表被ｔｒｕｎｃａｔｅ后，这个表和索引所占用的空间会恢复到初始大小；ｄｅｌｅｔｅ操作不会减少表或索引所占用的空间；ｄｒｏｐ语句将表所占用的空间全释放掉。2.总结ｄｅｌｅｔｅ 语句可以使用where子句实现部分删除，而ｔｒｕｎｃａｔｅ不可以，会将表中的整个数据全部删除，使用时，可以按需求选择；如果想从表中删除所有的数据，不要使用ｄｅｌｅｔｅ，可以使用ｔｒｕｎｃａｔｅ语句，因为这样执行速度更快。ｔｒｕｎｃａｔｅ语句实际是删除原来的表然后重新建立一个新表；在没有备份情况下，谨慎使用 ｄｒｏｐ 与 ｔｒｕｎｃａｔｅ。要删除表结构使用ｄｒｏｐ;.对于由 FOREIGN KEY 约束引用的表，不能使用 ｔｒｕｎｃａｔｅ TABLE，而应使用不带 WHERE 子句的 ｄｅｌｅｔｅ 语句。由于 ｔｒｕｎｃａｔｅ TABLE 不记录在日志中，所以它不能激活触发器。ｄｅｌｅｔｅ from删空表后，会保留一个空的页，ｔｒｕｎｃａｔｅ在表中不会留有任何页。ｄｅｌｅｔｅ语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。 ｔｒｕｎｃａｔｅ TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。当使用行锁执行 ｄｅｌｅｔｅ 语句时，将锁定表中各行以便删除。ｔｒｕｎｃａｔｅ始终锁定表和页，而不是锁定各行。如果有identity产生的自增id列，ｄｅｌｅｔｅ from后仍然从上次的数开始增加，即种子不变；使用ｔｒｕｎｃａｔｅ删除之后，种子会恢复到初始值。

ｄｅｌｅｔｅ删除内容，不删除表结构，可以删除全部数据，也可以删除部分数据，一行行删除，效率较低，可以回滚。

ｔｒｕｎｃａｔｅ删除内容，不删除表结构，删除表全部数据，操作生效后，自动提交，不能回滚。

ｄｒｏｐ既删除表内容，又删除表结构，执行速度最快。

二.用法：一.ｄｅｌｅｔｅ

1.删除整张表的数据：

ｄｅｌｅｔｅ from table_name;

2.删除部分数据，添加where子句：

deletefrom table_name where...;

3.说明

ｄｅｌｅｔｅ可以和where子句连用删除指定行。属于DML语言，每次删除一行，都在事务日志中为所删除的每行记录一项。产生rollback，事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发，如果删除大数据量的表速度会很慢。删除表中数据而不删除表的结构(定义)，同时也不释放空间。ｄｅｌｅｔｅ不会删除索引 （新插入的数据将在删除数据的索引后继续增加）；二.ｔｒｕｎｃａｔｅ

1.只能操作表，将表中数据全部删除，在功能上和不带where子句的ｄｅｌｅｔｅ语句相同：

ｔｒｕｎｃａｔｅ table table_name;

2.说明

默认情况下，ｔｒｕｎｃａｔｅ通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。所以使用的系统和事务日志资源少，可以使用reuse storage; ｔｒｕｎｃａｔｅ会将高水线复位(回到最开始).ｔｒｕｎｃａｔｅ是DDL语言, 操作立即生效,自动提交，原数据不放到rollback segment中,不能回滚. 操作不触发trigger.删除内容、释放空间但不删除表的结构(定义)。三.ｄｒｏｐ

1.ｄｒｏｐ语句将删除表的结构，以及被依赖的约束(constrain),触发器(trigger),索引(index);

ｄｒｏｐ table table_name;

2.说明

删除之后，依赖于该表的存储过程/函数将保留,但是变为invalid状态.ｄｒｏｐ也属于DDL语言，立即执行，执行速度最快删除内容和定义，释放空间。