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

MMMMS
  • 2,179
  • 9
  • 43
  • 83
  • DROP Database1; CREATE Database1 and everything is empty – Bernd Buffen Sep 23 '15 at 12:19
  • 4
    @BerndBuffen: That would also lose the table definitions... – eggyal Sep 23 '15 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 – Bernd Buffen Sep 23 '15 at 12:27

3 Answers3

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
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • Or as one command, just pipe mysqldump ouput to mysql and do away with the intermediate file... – eggyal Sep 23 '15 at 12:22
  • 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 Sep 23 '15 at 12:31
  • @eggyal:- Yes agreed and even I would prefer to use mysqldump. But just added one more option. :) – Rahul Tripathi Sep 23 '15 at 12:33
  • @RahulTripathi: ths dump technique will loose stored procedure/function so include -R also. – Zafar Malik Sep 23 '15 at 12:38
  • 1
    @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. – vhu Sep 23 '15 at 12:52
  • @ZafarMalik: Why would it lose stored procs? They're not being dropped anywhere... – eggyal Sep 23 '15 at 14:43
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...

Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
  • 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 Sep 23 '15 at 12:25
  • @eggyal: without -R, mysqldump will not include sotored procedire/function in backup file. – Zafar Malik Sep 24 '15 at 10:34
  • 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 Sep 24 '15 at 10:46
  • @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. – Zafar Malik Sep 24 '15 at 11:04
  • The database is never dropped. The sprocs are never dropped. So there's no need to "restore" them. – eggyal Sep 24 '15 at 11:10
  • @eggyal: Yes you are correct, here tables will be dropped and re-create not database/stored procs. – Zafar Malik Sep 26 '15 at 01:00
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)

Community
  • 1
  • 1
Vishal Barot
  • 16
  • 2