SoFunction
Updated on 2025-03-02

Detailed explanation of MySQL delete table data and clear table commands (the difference between truncate, drop, delete)

1. Three ways to clear table data in MySQL

1.1 Clear table data: truncate

  • sql command
#Clear the data of all tables in multiple tables and librariestruncate table table_name1,table_name2,...;

#Clear data from a single tabletruncate table table_name;

Notice:

  • truncate will delete all data in the table and free up space, but retain the table structure
  • Only operate tables, not with where
  • The truncate deletion operation takes effect immediately. The original data is not placed in the rollback segment. It cannot rollback. The operation does not trigger trigger.
  • After deleting data, truncate will release table space and reset Identity (identification column, self-increment field), which is equivalent to the self-increment column being set to the initial value, and then start recording from 1, instead of following the original id number
  • After truncate deletes data, the server log will not be written, and the overall deletion speed will be fast.

1.2 Delete table: drop

  • sql command
drop table table_name;
drop table if exists table_name;

Notice:

  • Drop will delete the entire table, including the table structure and data, freeing up space
  • Execute immediately, with the fastest execution speed
  • Can't roll back

1.3 Delete/clear table data: delete

  • sql command
#Delete some datadelete from tb_name where clause;

#Clear the table, only delete the data, retain the table structure, and do not free up the table space.delete from tb_name;

Notice:

  • Delete data in tables without deleting table structures and freeing up space
  • delete can delete a row, multiple rows, or even a whole table
  • Each time a row is deleted, one is recorded for each row deleted in the transaction log, and can be rolled back
  • If there is no where condition, it means that all data in the table is deleted, only the data is deleted, the table structure is preserved, and the table space is not released.

The above three commands are available for MySQL, Mariadb, PostgreSQL deletion table data and clear table commands.

2. Principles of use

The principles of use are summarized as follows:

  • When you don't need the table (delete data and structure), use drop;
  • Use truncate when you still want to keep the table and delete only all data table contents;
  • When you want to delete some records and hope to roll back, use delete;

Use drop and truncate with caution without backup.

In actual applications, appropriate operations should be selected according to specific needs and scenarios.

3. The difference between truncate, drop, delete

Truncate, delete, and drop are all operations for deleting data or tables, but there are some key differences between them:

1. Operation Type

  • truncate: Delete all data in the table, preserve the table structure, and free up space. It is a DDL (Data Definition Language) operation that performs faster.
  • delete: Delete specific rows in the table, which can be deleted row by row, retain the table structure and do not free up space. It is a DML (data operation language) operation that performs slowly.
  • drop: Delete the entire table, including the table structure and data, and free up space. It is a DDL operation, but unlike truncate, it frees up space occupied by the table.

2. Data deletion method

  • truncate: Delete all data in the table, but the table structure will not be deleted. Applicable to cases where table structure needs to be preserved.
  • delete: Delete specific data in the table, which can be deleted according to the conditions, and the structure and constraints of the table remain unchanged. Applicable to situations where data needs to be deleted based on specific conditions.
  • drop: Delete the entire table, including the table structure and data. Suitable for situations where table structure is no longer needed.

3. Execution speed: drop > truncate > delete

  • truncate: Execution is faster because it deletes all data at once, frees up the table's storage space to delete the data, and resets the table to its initial state.
  • delete: Execution is slow because it requires deletion of data line by line and generates a large number of transaction logs without freeing up space.
  • drop: Execution is faster because it deletes the entire table at once.

4. Rollback ability

  • truncate: Cannot be rolled back. Once executed, the data will be permanently deleted and cannot be restored.
  • delete: Can be rolled back, and the deletion operation can be undoed using the ROLLBACK statement.
  • drop: Cannot be rolled back. Once executed, the table structure and data will be permanently deleted.

5. Trigger

  • truncate: The trigger will not be triggered.
  • delete: Will trigger the trigger.
  • drop: The trigger will not be triggered because it is deleted from the entire table.

Summarize

  • If you need to quickly delete data from the entire table, but keep the table structure, you can choose truncate.
  • If you need to delete data from a specific row, you can delete it according to the conditions and choose delete.
  • If you need to delete the entire table, including the table structure and data, you can choose drop.
  • In actual applications, appropriate operations should be selected according to specific needs and scenarios.

The above is personal experience. I hope you can give you a reference and I hope you can support me more.