This morning I am faced with a task that will involve repeatedly dropping and reimporting a lot of data. MySQL has DROP TABLE and DROP DATABASE but there is no command to drop all tables or truncate the database.
After finding a reference on the MySQL Lists (http://lists.mysql.com/mysql/193430) I started playing with the idea. I didn't want to have to dump into one file and then run that query. Also - the problem with the above concept is that it drops the table and then recreates it - not what i wanted!
I then looked into using the pipe and grep features in linux. Now I was getting somewhere! A few tweaks later and this is what I got:
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
In the above, [USERNAME], [PASSWORD] & [DATABASE] are all the details for your database. You might not need the username and password fields - depends on your setup!
thanks for the solution
He great, this solution for 'drop all table'. Was looking for it quite a while already.
Rik
thank you!
This is a great solution for the "drop tables from database" problem, thank you! Looked for it for a long time...
Alternate ...
How about logging into mysql and then using DROP and CREATE database.
# mysql -uroot -p
mysql> DROP DATABASE dbname;
mysql> CREATE DATABASE dbname;
I just did that and it seemed to effectively empty the database. Is there a downside to doing it this way?
Thinking about it - you might be right
I have a feeling that does do the job just as well - although it doesn't preserve any settings applied to the database (for example, character set or collation).
Still - its a very good point!
The alternate solution is
The alternate solution is only going to work if you have permissions to create a new database. If you only have one database that you can work with, and no permissions outside that db, that isn't going to help.
Very true!
This is a VERY good point.
Thanks for bringing this up Chekote as I'd not considered it before.
That solution doesn't work
That solution doesn't work for those of us on shared hosting services who don't have permissions to create and drop databases.
Shared hosting is limited anyway.
If you're on shared hosting there is often very little that you can do in terms of usefull and cool things. They're usually VERY restrictive.
Thanks
Thanks. Worked perfectly with me.
Thank you
Thank you!
reply
Is there a way in phpMyAdmin to drop all tables from a MySQL database without deleting the database, just removing all contents? Sort of like a wildcard drop?
I'm using phpMyAdmin -
I'm using phpMyAdmin - 2.10.0.2 with my web hosting. There's an option to Select All tables, then apply "DROP" command. It deleted all tables in the database, just as you wanted.
Alternatively, you can also tick as many tables as you want before selecting the drop command. Very handy if you want to delete some tables but not all.
slight alternatve
Here's a slight alternative to the solution:
mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | \ gawk '{print "drop table " $1 ";"}' | mysql -u uname dbnameFrom my mysql drop all tables using single command.
I'm not sure if there's any good reason to use one or the other. Obviously some command line options (like username) are missing from mine...
Interesting alternative!
Thanks for that Greggles - I'll have to look into the 'gawk' command - not sure I've ever really seen it used before!
This is easier if you have root on the computer
I did
# rm -f /var/mysql//*
which works fine if it's your own computer.
I needed to delete all the tables because my database became corrupted when two different versions of a client program on my local net were writing to the same database :(
Interesting
All tables, then apply "DROP" command?
??
I'm not sure if there's any good reason to use one or the other
There is a very good reason
There is a very good reason to use 'show tables' and not mysqldump. If you have a large database 'show tables' will offer a superior performance.
downside
The only downside is that you might have the GRANT new permissions on the database again.
Nope
From my experience, dropping and creating a table doesn't require recreation of the permissions.
Thank you for great solution!
Thank you for great solution!
foreign keys
BTW, if you have foreign key constraints, i think you have to do:
Set foreign_key_checks=off;before doing the drops (at least i had to). (using MySql 5.0.)
Too bad I don't have grep or gawk on windows...
for windows
ok, i found a note on mysql forums, & with a little adaptation came up with this for a Windows environment:
mysql -u --execute="SELECT concat('DROP TABLE ',table_name,';') INTO OUTFILE 'c:/drop.sql' FROM information_schema.TABLES WHERE TABLE_SCHEMA='mydbname';source c:/drop.sql;" mydbname
This requires read access to information_schema.TABLES -- i don't know if that's typically available (to non-admins) or not! One other thing -- the output file (drop.sql above) can NOT exist -- so you need to delete it each time. (Of course, if the tables are always the same, you can just do "source c:/drop.sql;" each time after the first!)
...and insert "set foreign_key_checks=off;" before the "source" command if you have FK constraints.
Nice explanation!
Thanks for posting that - much appreciated. Definitely an alternative way of doing this.
Your site is great and I
Your site is great and I really appreciate it!
I have always enjoyed reading your site.