Characteristics and differences of Truncate/Delete/Drop table

Before Truncate/Delete/Drop has not been very clear understanding, so deliberately flipped the MySQL 5.7 Reference Manual, ready to understand the system, here are some translations, plus a little bit of their own knowledge.

Features of Truncate

logically truncate table is similar to delete from table_name;, but the process is to drop table first, then re-create table, if you want to empty all the data rows of a large table, truncate is more efficient than delete;

Truncate is a DDL operation that commits implicitly once executed, indicating that truncate cannot rollback, so be careful before executing;

If the table is locked, truncate will report an error;

Truncate also reports an error if there are foreign key constraints;

For the auto_increment column of InnoDB/MyISAM table, the sequence value can be reused after truncate table;

truncate table failed to trigger delete related triggers;

Delete Features

Delete is a DML operation. If there is no commit, you can rollback;

For the auto_increment column of InnoDB/MyISAM table, the sequence value cannot be reused after deleting the table, but the sequence can be reused after restarting;

Delete from table_name must be followed by the where condition, otherwise all rows of the table will be deleted;

Characteristics of Drop

Drop table deletes tables at the database level, but also deletes files such as xxx.ibd, xxx.frm(InnoDB table) or xxx.MYD,xxx.MYI, xxx.frm (MyISAM) at the system level;

drop table if exists table_name prevents an error from being reported if the table does not exist, but there will be a warning;

Drop tables are also implicitly committed, except for temporary tables;

reference link

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