(说点废话先)其实这三个的差别困扰了我挺久的,也看了不少帖子,大多讲的太晦涩,我就在这里讲讲我所理解出来的拙见,有什么错误还请大家指正。
目录
1.Delete
2.Drop
3.truncate
4.执行速度
(我有看过别人拿书来做比喻,我感觉很形象)
如果把数据库表想象成一本本的书
1.delete全表,就是撕掉了书的内容(内容可以找回,且表结构还在)
2.drop,烧掉了整本书(都成灰了,找不回来了,要谨慎使用)
3.truncate,把书的目录复印一份,再烧掉原来的书(保留表结构<虽然已经不是以前的表了>,内容不可找回)
1.Delete
应该是初学sql接触最早的删除命令(这名字但凡学过初中英语也明白是删除的意思)。
delete FROM 表名 [where 列名=值][order by 列名][limit ..]
注意:在本文提及的三种删除命令中只有delete可以使用条件删除。表示删除满足条件的数据。
(1)仅删除表数据,会走事务,可以回滚。
delete仅删除表中的数据内容,保留表定义,即保留表结构、属性及索引。delete操作会先将所删除数据缓存到rollback segement 中,事务commit之后生效,以便进行回滚操作。
(2)不会把数据直接删除,采用添加删除标记。
delete 语句是数据库操作语言(dml)。在 InnoDB 中,delete其实并不会真的把数据删除,mysql 实际上只是给删除的数据打了个标记为已删除,因此 delete 删除表中的数据时,表文件在磁盘上所占空间不会变小,存储空间不会被释放,只是把删除的数据行设置为不可见,所以delete并不会重置自增列。虽然未释放磁盘空间,但是下次插入数据的时候,仍然可以重用这部分空间(重用 → 覆盖)。
(3)磁盘内存影响
删除表的全部数据,对于MyISAM 会立刻释放磁盘空间,InnoDB 不会释放磁盘空间。
带条件的删除, 不管是InnoDB还是MyISAM都不会释放磁盘空间;
删除操作日志记录在redo和undo表空间中以便进行回滚(rollback)和重做操作,会增加执行时间,生成的大量日志也会占用磁盘空间。
(4)optimize table
optimize table 表名
delete操作以后使用 optimize table 会立刻释放磁盘空间。不管是InnoDB还是MyISAM 。所以要想达到释放磁盘空间的目的,delete以后执行optimize table 操作。
2.Drop
drop可以用于删除数据库、表及索引。
drop删除数据库
drop database 数据库名
drop删除表
drop table 表名
drop删除索引
drop index 索引名
drop语句是数据库定义语言(DDL),执行后会自动提交,无法回滚找回。
drop 语句删除表,会删除其表结构和依赖的约束(constrain)、触发器(trigger)、索引(index);将表所占用的空间全部释放。
3.truncate
truncate语句用于清除表中的所有数据。
truncate table 表名
truncate和drop一样也是数据库定义语言(DDL),执行后立即生效,且无法回滚。
truncate会重置自增列的值。因为其原理是重新建一个相同表结构的表,再把原来的表删除。
4.执行速度
drop> truncate > delete