Truncate Characteristics

logically truncate table and delete from table_name; be similar , But the process is first drop table, And then in re-create table, If you want to empty all data rows of a large table ,truncate Than delete Efficient ;

• Truncate It’s a DDL operation , Once executed, it implicitly commits , This means that truncate unable rollback, So be careful before you execute ;

• If the watch is locked ,truncate Will report a mistake ;

• If there are foreign key constraints ,truncate You will also report mistakes. ;

• about InnoDB/MyISAM Tabular auto_increment The column of ,truncate table You can then reuse the sequence values ;

• truncate table Unable to trigger delete Related triggers ;

Delete Characteristics

• Delete yes DML operation , If not submitted , Sure rollback;

• about InnoDB/MyISAM Tabular auto_increment The column of ,delete table After that, you can’t reuse the sequence value , But the sequence can be reused after restart ;

• Delete from table_name Be sure to follow where Conditions , Otherwise, all rows of the table will be deleted ;

Drop Characteristics

• Drop table In addition to deleting tables at the database level , It will also delete… At the system level xxx.ibd,xxx.frm(InnoDB surface ) or xxx.MYD,xxx.MYI,xxx.frm (MyISAM) Wait for the documents ;

• drop table if exists table_name It can prevent the report error that the table does not exist , But there will be one warning;

• drop table It also implicitly commits , Except for the temporary table ;

Reference material

https://dev.mysql.com/doc/refman/5.7/en/truncate-table.html
https://dev.mysql.com/doc/refman/5.7/en/delete.html
https://dev.mysql.com/doc/refman/5.7/en/drop-table.html

Link to the original text ：https://blog.51cto.com/darrenmemos/2288350