How to Truncate or Drop All Tables in a MySQL Database with a Single Command?

Truncating Tables in a MySQL Database with a Single Command

MySQL databases can contain numerous tables, each holding valuable data. Occasionally, it may become necessary to clear all the tables in a database simultaneously for various reasons, such as cleaning up or preparing for a new data set. This task can be achieved with a single command, eliminating the need for time-consuming manual deletion of individual tables.

To truncate (empty) all tables in a database, the following code snippet can be utilized:

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done

This command will iterate through all the tables within the specified database, DATABASE_NAME, and execute a truncate operation for each one. Alternatively, if the goal is to drop (remove) all tables instead of truncating them, the following command can be employed:

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done

This command will execute a drop operation for each table in the database, effectively deleting them permanently. It's important to note that the drop operation is irreversible, meaning that the data in the tables will be lost forever. Therefore, it is crucial to use this command cautiously and ensure that no critical data will be erased unintentionally.

The above is the detailed content of How to Truncate or Drop All Tables in a MySQL Database with a Single Command?. For more information, please follow other related articles on the PHP Chinese website!