mysql中delete、truncate和drop区别说明

下文笔者讲述mysql中删除数据的三种方式分享,如下所示

删除数据的三种方式

delete、truncate、drop 关键字 
  可进行数据删除

运行速度简介

drop > truncate >> DELETE;

原理说明

DELETE

DELETE from TABLE_NAME where xxx
 

1、DELETE
   是数据库DML操作语言
   只删除数据不删除表的结构
    会走事务，运行时会触发trigger

2、在InnoDB中，
   DELETE其实并不会真的把数据删除，
   mysql 实际上只是给删除的数据打了个标记为已删除
    因此 delete 删除表中的数据时
	 表文件在磁盘上所占空间不会变小
	 存储空间不会被释放
	  只是把删除的数据行设置为不可见
	  虽然未释放磁盘空间
	  但是下次插入数据的时候
	  仍然可以重用这部分空间(重用 → 覆盖)

3、 DELETE执行时
     会先将所删除数据缓存到rollback segement中
	 事务commit之后生效 

4、 delete from table_name删除表的全部数据
      对于MyISAM 会立刻释放磁盘空间
	   InnoDB不会释放磁盘空间

5、对于delete from table_name where xxx 
      带条件的删除
	   不管是InnoDB还是MyISAM都不会释放磁盘空间 

6、 delete操作以后使用 optimize table table_name 
       会立刻释放磁盘空间
	   不管是InnoDB还是MyISAM
	   所以要想达到释放磁盘空间的目
	     delete以后执行optimize table 操作。

例
查看表占用硬盘空间大小SQL语句如下
(用M做展示单位，数据库名：java，表名：test）

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as table_size
   from information_schema.tables
      where table_schema='java' AND table_name='test';

delete 操作是一行一行运行删除
  同时将该行的的删除操作日志记录在redo和undo表空间
    中以便进行回滚(rollback)和重做操作
	生成的大量日志也会占用磁盘空间。

truncate

Truncate table TABLE_NAME

truncate
属于数据库DDL定义语言
  不走事务
   原数据不放到 rollback segment 中，操作不触发 trigger。

执行后立即生效，无法找回 

truncate table table_name 立刻释放磁盘空间 
不管是 InnoDB和MyISAM 

truncate table其实有点类似于drop table 然后create

truncate能够快速清空一个表
 并且重置auto_increment的值

对于MyISAM 
   truncate会重置auto_increment（自增序列）的值为1
    而delete后表仍然保持auto_increment。

对于InnoDB，truncate会重置auto_increment的值为1
  delete后表仍然保持auto_increment
  但是在做delete整个表之后重启MySQL的话，则重启后的auto_increment会被置为1。
 
 InnoDB的表本身是无法持久保存auto_increment
   delete表之后auto_increment仍然保存在内存
     但是重启后就丢失了，只能从1开始 

drop

Drop table Tablename

drop
  属于数据库DDL定义语言
   同Truncate；

执行后立即生效，无法找回 

drop table table_name 立刻释放磁盘空间 
不管是 InnoDB 和 MyISAM;
 drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index); 
 依赖于该表的存储过程/函数将保留,但是变为 invalid 状态

DELETE、TRUNCATE 和 DROP简介及区别

DELETE：
	DELETE 用于删除表中的行。
	它是一个DML（数据操作语言）命令。
	DELETE 操作会逐行删除数据，并且这个过程可以被回滚。
	DELETE 操作会触发与行相关的删除触发器。
	DELETE 操作不会重置表的自增计数器（如果有的话）。
	DELETE 操作会更新相关的索引，这可能会比 TRUNCATE 操作慢，特别是当表中有大量数据时。
	DELETE 操作可以指定条件，只删除满足条件的行。

TRUNCATE：
	TRUNCATE 用于快速删除表中的所有行，包括表中的所有行和系统表中关于该表的信息。
	它是一个DDL（数据定义语言）命令。
	TRUNCATE 操作不会逐行删除数据，而是直接删除表并重新创建，这个过程不能被回滚。
	TRUNCATE 操作不会触发与行相关的删除触发器。
	TRUNCATE 操作会重置表的自增计数器（如果有的话）。
	TRUNCATE 操作通常比 DELETE 快，因为它不更新索引，而是删除整个表结构并重新创建。
	TRUNCATE 操作不能指定条件，它会删除表中的所有行。

DROP：
	DROP 用于删除数据库中的表或其它数据库对象（如视图、索引、触发器等）。
	它是一个DDL命令。
	DROP 操作会完全删除表结构及其定义，包括表中的所有数据、索引、触发器等。
	DROP 操作不能被回滚。
	DROP 操作不会触发与表相关的任何触发器。
	DROP 操作会释放表所占用的空间。
	DROP 操作可以指定条件，但通常用于无条件地删除对象。