Truncate all tables in a MySQL database in one com

Question

Is there a query (command) to truncate all the tables in a database in one operation? I want to know if I can do this with one single query.

Answer 1

<?php
// connect to database
$conn=mysqli_connect("localhost","user","password","database");

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// sql query
$sql =mysqli_query($conn,"TRUNCATE " . TABLE_NAME);


// Print message
if ($sql === TRUE) {
  echo 'data delete successfully';
}
else {
 echo 'Error: '. $conn->error;
}

$conn->close();

?>

Here is code snippet which I use to clear a table. Just change $conn info and TABLE_NAME.

Answer 2

TB=$( mysql -Bse "show tables from DATABASE" );
for i in ${TB};
    do echo "Truncating table ${i}";
    mysql -e "set foreign_key_checks=0; set unique_checks=0;truncate table DATABASE.${i}; set foreign_key_checks=1; set unique_checks=1";
    sleep 1;
done

--

David,

Thank you for taking the time to format the code, but this is how it is supposed to be applied.

-Kurt

On a UNIX or Linux box:

Make sure you are in a bash shell. These commands are to be run, from the command line as follows.

Note:

I store my credentials in my ~/.my.cnf file, so I don't need to supply them on the command line.

Note:

cpm is the database name

I am only showing a small sample of the results, from each command.

Find your foreign key constraints:

klarsen@Chaos:~$ mysql -Bse "select concat(table_name, ' depends on ', referenced_table_name)
             from information_schema.referential_constraints
             where constraint_schema = 'cpm'
             order by referenced_table_name"

1. approval_external_system depends on approval_request
2. address depends on customer
3. customer_identification depends on customer
4. external_id depends on customer
5. credential depends on customer
6. email_address depends on customer
7. approval_request depends on customer
8. customer_status depends on customer
9. customer_image depends on customer

List the tables and row counts:

klarsen@Chaos:~$ mysql -Bse "SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cpm'" | cat -n

 1  address 297
 2  approval_external_system    0
 3  approval_request    0
 4  country 189
 5  credential  468
 6  customer    6776
 7  customer_identification 5631
 8  customer_image  2
 9  customer_status 13639

Truncate your tables:

klarsen@Chaos:~$ TB=$( mysql -Bse "show tables from cpm" ); for i in ${TB}; do echo "Truncating table ${i}"; mysql -e "set foreign_key_checks=0; set unique_checks=0;truncate table cpm.${i}; set foreign_key_checks=1; set unique_checks=1"; sleep 1; done

1. Truncating table address
2. Truncating table approval_external_system
3. Truncating table approval_request
4. Truncating table country
5. Truncating table credential
6. Truncating table customer
7. Truncating table customer_identification
8. Truncating table customer_image
9. Truncating table customer_status

Verify that it worked:

klarsen@Chaos:~$ mysql -Bse "SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cpm'" | cat -n

 1  address 0
 2  approval_external_system    0
 3  approval_request    0
 4  country 0
 5  credential  0
 6  customer    0
 7  customer_identification 0
 8  customer_image  0
 9  customer_status 0
10  email_address   0

On a Windows box:

NOTE:

cpm is the database name

C:\>for /F "tokens=*" %a IN ('mysql -Bse "show tables" cpm') do mysql -e "set foreign_key_checks=0; set unique_checks=0; truncate table %a; foreign_key_checks=1; set unique_checks=1" cpm

Answer 3

truncate multiple database tables on Mysql instance

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('db1_name','db2_name');

Use Query Result to truncate tables

Note: may be you will get this error:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

That happens if there are tables with foreign keys references to the table you are trying to drop/truncate.

Before truncating tables All you need to do is:

SET FOREIGN_KEY_CHECKS=0;

Truncate your tables and change it back to

SET FOREIGN_KEY_CHECKS=1;

Answer 4

Use this and form the query

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in (db1,db2)
INTO OUTFILE '/path/to/file.sql';

Now use this to use this query

mysql -u username -p </path/to/file.sql

if you get an error like this

ERROR 1701 (42000) at line 3: Cannot truncate a table referenced in a foreign key constraint

the easiest way to go through is at the top of your file add this line

SET FOREIGN_KEY_CHECKS=0;

which says that we don't want to check the foreign key constraints while going through this file.

It will truncate all tables in databases db1 and bd2.

Answer 5

mysqldump -u root -p --no-data dbname > schema.sql
mysqldump -u root -p drop dbname
mysqldump -u root -p < schema.sql

Answer 6

Drop (i.e. remove tables)

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done

Truncate (i.e. empty tables)

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done