MySQL allows you to build complex database schema with multiple tables, columns, constrains and indexes. Sometimes you may want to delete just the data in all tables of your database but retain the tables, columns, indexes and constraints. This is also known as truncating tables of database. In this article, we will learn how to truncate all tables of database in MySQL. It can be tedious to manually delete the data of each table one by one. So we will learn how to do this for all database tables.
How to Truncate All Tables of Database in MySQL
The basic command to truncate a table in MySQL is TRUNCATE TABLE.
TRUNCATE TABLE table_name
You can easily empty all tables in your database with the following command. Replace DATABASE_NAME with the name of your database.
$ mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done
Let us look at the above command in detail. First of all we issue ‘SHOW TABLES’ command for our database, to get a list of all database tables.
We pipe its output to do…while command which basically iterates through each table one by one and executes ‘truncate table’ MySQL command for each table.
If you want to remove the table completely, then use DROP TABLE command.
$ mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done
All the above commands are to be run in Linux terminal, using mysql command line client. We use -e option to execute SQL statements via command line.
In this short article, we have learnt how to easily truncate all tables in database.
Also read:
How to Get Primary Key of Newly Inserted Row in PostgreSQL
How to Get Primary Key of Newly Inserted Row in MySQL
How to Find Tables With Column Name in MySQL
How to Find my.cnf Location in MySQL
How to Add Option to Select in jQuery