3

I have database called Database1 and this DB have 40 tables. Now i want to delete all data from that 40 tables.I know that to display all tables from DB using 

SELECT table_name 
FROM INFORMATION_SCHEMA.tables 
WHERE table_schema = 'Database1';

So how to delete all data from all tables form Database1 using single query?

Note :

I should delete only data, not tables.

I am using mysql workbench 6.0

Improve this question
3
  • DROP Database1; CREATE Database1 and everything is empty Commented Sep 23, 2015 at 12:19
  • 4
    @BerndBuffen: That would also lose the table definitions... Commented Sep 23, 2015 at 12:19
  • SELECT GROUP_CONCAT(CONCAT('TRUNCATE TABLE ',t.TABLE_SCHEMA,'.',t.TABLE_NAME,';\n') SEPARATOR '') FROM INFORMATION_SCHEMA.tables t WHERE t.table_schema = 'Database1' - It deletes also views. Query the result of this Commented Sep 23, 2015 at 12:27

3 Answers 3

Reset to default
4

You can try this:

mysqldump -d -uuser -ppass --add-drop-table yourdatabasename > yourdatabasename.sql
mysql -uuser -ppass yourdatabasename < yourdatabasename.sql

As pointed correctly by Zafar, if you want to include stored procedure/function also then you can include -R option.

Or you can try like

mysql -Nse 'show tables' yourdatabasename | while read table; do mysql -e "truncate table $yourtable" yourdatabasename; done
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Or as one command, just pipe mysqldump ouput to mysql and do away with the intermediate file...
Re your edit using truncate, see the point I made on @Zafer's answer: "Be careful—$table should really be quoted and escaped (it might contain characters that are invalid in unquoted identifiers)." I find the answer using mysqldump to be much safer.
@eggyal:- Yes agreed and even I would prefer to use mysqldump. But just added one more option. :)
@RahulTripathi: ths dump technique will loose stored procedure/function so include -R also.
@RahulTripathi, please note that your method will also preserve AUTO_INCREMENT counters, unlike TRUNCATE TABLE. Whether that's a good thing or not, should be left for OP to consider.
|
0

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 "truncate table $table"; done

You can also do it by any gui like sqlyog by below steps-

right click on database1 > choose more database options > truncate database

Third option is by structure backup and restore as per below-

mysqldump -R -d -uroot -proot123 database1 | mysql -uroot -proot123 database1

Note: Always use -R if you are using stored procedures/function other wise you loose it.

enjoy...

Improve this answer

6 Comments

Be careful—$table should really be quoted and escaped (it might contain characters that are invalid in unquoted identifiers). I find @Rahul's answer using mysqldump to be much safer.
@eggyal: without -R, mysqldump will not include sotored procedire/function in backup file.
That is true, but the sprocs/functions are never being dropped from the original database—only the tables get dropped (if you specify --add-drop-table option to mysqldump, which you're not doing, so actually your version will fail due to the tables already existing).
@eggyal: As per my information --add-drop-table is default with mysqldump so no need to include it....also when your backup file does not contain procedure/function schema then how they will be restored.
The database is never dropped. The sprocs are never dropped. So there's no need to "restore" them.
|
0

For Oracle : if you want to delete all the records of all the tables without drop the tables you should have a look, it may help you https://dba.stackexchange.com/questions/74519/delete-all-the-data-from-all-tables

you can try any of following : Delete data from all tables in MYSQL

How to empty all rows from all tables in mysql (in sql)

Improve this answer

1 Comment

The linked question (and its answers) is only applicable to Oracle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.