How to empty mysql database in phpmyadmin

Question

I would like to empty a mysql database in phpmyadmin by deleting all tables, however this generates foreign key errors.

One solution would be to delete the tables in the right order to prevent foreign key errors, but is there a way to do this automatically in phpmyadmin? A feature to empty the database for instance?

What I do is I just get rid of the whole DB and create a new one with the same name. It takes less time and fewer mouse clicks. — Mohammed Joraid, Jul 01 '17 at 09:38
See answer "Use phpMyAdmin in this way:": https://stackoverflow.com/questions/1912813/truncate-all-tables-in-a-mysql-database-in-one-command#answer-7131368 — erc mgddm, Sep 18 '18 at 14:39

score 4 · Accepted Answer · answered Mar 28 '21 at 20:05

My answer is somehow late, but easy and working:

Go to the tables/structure view of your database.
Scroll down to the end, select "check all" and choose "with selected" "drop".
Disable the checkbox "Enable foreign key checks".
Click "yes" to execute your query.

You now have an empty database (the database still exists, and the database view says "No tables found in database".

Zafar Malik · Answer 2 · 2017-07-03T11:10:58.333

What do you mean here by database cleanup-

You want to drop all tables from DB and want DB blank.
Or you want to purge all data but need all tables intact in DB.

In first case better option is just drop database and re-create it.

OR if due to any specific reason you don't want to drop database (as in this way you will loose all triggers/views etc.) then first get tables for drop by show tables command and prepare "drop table table_name;" script in excel or by below command and execute through phpmyadmin.

SELECT CONCAT("drop table ",table_schema,".",table_name,";") FROM information_schema.TABLES WHERE table_type='BASE TABLE' AND table_schema='my_db';

In Second Case if you want to purge data only then you can prepare truncate script by below command and then execute it by phpmyadmin-

SELECT CONCAT("truncate table ",table_schema,".",table_name,";") FROM information_schema.TABLES WHERE table_type='BASE TABLE' AND table_schema='my_db';

Note: execute set foreign_key_checks=0; before executing drop or truncate commands and set foreign_key_checks=1; after execution to avoid foreign key related issues.

You can execute below command on server console.

mysql -uroot -p<pass> -Nse 'show tables' database1 | while read table; do mysql -uroot -p<pass> database1 -e "drop table $table"; done

OR

mysql -uroot -p<pass> -Nse 'show tables' database1 | while read table; do mysql -uroot -p<pass> database1 -e "truncate table $table"; done

@Shadow: he wants to truncate tables without dropping database....I don't understand what do you mean by delete???? — Zafar Malik, Oct 07 '15 at 09:36
There's no "right click on database1" in phpMyAdmin since it runs within the browser. — Mohammed Joraid, Jul 01 '17 at 09:33

How to empty mysql database in phpmyadmin

2 Answers2