1. delete#

delete belongs to the data management language (DML), which can execute transactions and trigger triggers, and can roll back data. Common command usage is as follows:

① Delete all records in the table

delete from table_name;

② Delete some records in the table according to the conditions

delete from table_name where [condition];

delete only deletes the records, but the table structure and its columns, constraints, indexes, etc. remain unchanged.

The first type does not have a delete condition, which belongs to delete all records in the table. In the InnoDB engine, this deletion method is not a real deletion, that is, it will not release disk space, but just mark these deleted records, indicating that the records have been deleted; although the disk space is not released, there will be other records next time When inserting, you can use this part of the space directly. For the MyISAM engine, this deletion method will directly release disk space. This deletion method will not reset auto_increment to 1, but auto_increment will be set to 1 after restarting the database.

The second type with deletion condition means to delete some records in the table. Neither the InnoDB engine nor the MyISAM engine will release disk space, but mark the record as deleted, and this part of the space can be used by other records. This way will not reset auto_increment to 1.

If you want to actively release the unreleased disk space, you can use the optimize table table_name method to release the disk space immediately after delete.

2. truncate#

truncate belongs to the data definition language (DDL), does not execute transactions, and does not trigger triggers. Data cannot be rolled back. Common commands are as follows:

truncate does not support where conditions, only supports deleting all

truncate table table_name;

The truncate command deletes all rows from a table, but the table structure and its columns, constraints, indexes, etc. remain unchanged.

Regardless of whether it is the InnoDB engine or the MyISAM engine, the disk space will be released after the truncate command is executed, and auto_increment will be reset to 1.

Executing truncate requires drop permission. It is best to back up the database table in advance, because it cannot be rolled back through binlog.

3. drop#

Drop also belongs to Data Definition Language (DDL) and is not recoverable. Commonly used commands are as follows:

drop table table_name;

Regardless of the InnoDB engine or the MyISAM engine, the disk space will be released after the drop command is executed, and the constraints, triggers, and indexes that depend on the data table will be deleted.