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

One-Command Solution for Truncating MySQL Database Tables

Managing large databases often requires the ability to efficiently truncate (empty) or drop (remove) multiple tables simultaneously. This task can be tedious if performed manually for each table.

Truncating Tables

To truncate all tables in a database using a single command, utilize the following query:

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

This query employs the show tables command to retrieve a list of table names in the specified database (DATABASE_NAME). It then iterates through the list using a while loop, executing a truncate table command for each table. Truncation empties table data without deleting the table structure.

Dropping Tables

To drop all tables in a database using a single command, use the following query:

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

Like the truncation query, this query retrieves a list of table names and iterates through them using a while loop. However, it executes a drop table command for each table, which removes the table from the database completely.

The above is the detailed content of How Can I 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!