Mysql – How to empty all rows from all tables in thesql (in sql)


I'm writing some db utility scripts, and one of the tasks I need to do is rebuild the data only, but leave the schema intact. What is the easiest way to automate this from the command-line using bash and the mysql tools (no php, etc)?

I'd like the solution to handle all tables in one command, and if possible, not need to be updated if tables are added or removed.

Best Solution

TRUNCATE tableName;

This will empty the contents of the table.

Edit in response to the Q edit: It seems from my quick test that you will have to do at least 2 queries as it seems that "show tables" cannot be used as a sub query, I don't know how to do this in bash so here is a PHP example, hopefully it will help.

mysql_connect('localhost', 'user', 'password');
$dbName = "database";
mysql_select_db($dbName); /*added semi-colon*/
$result_t = mysql_query("SHOW TABLES");
while($row = mysql_fetch_assoc($result_t))
   mysql_query("TRUNCATE " . $row['Tables_in_' . $dbName]);

At a minimum this needs some error handling.