How to delete all rows in all table of a database in MySQL?

Question

I had a database with nearly 500 tables and I want to delete all the records of all the tables. How to achieve this?

Do you want to remove all of the tables or just truncate the data within the tables? — Nick, Feb 19 '11 at 07:21

score 7 · Accepted Answer · answered Feb 19 '11 at 07:23

The simplest way is to drop and recreate the database structure using these shell commands:

mysqldump -d dbname > structure.sql
mysqladmin drop dbname
mysqladmin create dbname
mysql dbname < structure.sql

Insert mysql credentials as required, eg -u root -psecret -h localhost

score 3 · Answer 2 · edited May 23 '17 at 12:06

3

TRUNCATE tableName;

This will empty the contents of the table. check here

<?php
mysql_connect('localhost', 'user', 'password');
$dbName = "database";
mysql_select_db($dbName)
$result_t = mysql_query("SHOW TABLES");
while($row = mysql_fetch_assoc($result_t))
{
   mysql_query("TRUNCATE " . $row['Tables_in_' . $dbName]);
}
?>

edited May 23 '17 at 12:06

Community

1
1

answered Feb 19 '11 at 07:22

ayush

14,350
11
53
100

Just repeat 500 times... ;) – Juha Untinen Jul 02 '19 at 08:01

score 2 · Answer 3 · edited May 23 '17 at 12:00

2

check this other question on stackoverflow

also, it would be useful to know if you want to use just sql or also any scripting language.

edited May 23 '17 at 12:00

Community

1
1

answered Feb 19 '11 at 07:19

Andrea Zonca

8,378
9
42
70

How to delete all rows in all table of a database in MySQL?

3 Answers3