How to Empty All Tables in a MySQL Database with PHP

This is a simple PHP script to empty all tables in a MySQL database. This script first will list all tables in a database using ‘SHOW TABLES‘ statement then it will empty  each table using  ’TRUNCATE TABLE‘ statement.

$con = mysql_connect('dbhost','dbuser','dbpassword');

if (!$con) die("Can't connect to database!");

mysql_select_db('dbname');

$res = mysql_query('SHOW TABLES', $con);

while ($row = mysql_fetch_array($res, MYSQL_NUM))
{
  $res2 = mysql_query("TRUNCATE TABLE `$row[0]`", $con);
}

About TRUNCATE Statement

For an InnoDB table before version 5.0.3, InnoDB processes TRUNCATE TABLE by deleting rows one by one. As of MySQL 5.0.3, row by row deletion is used only if there are any foreign key constraints that reference the table. If there are no foreign key constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one.

facebooktwittergoogle_plusredditpinterestlinkedinmailby feather

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *