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)?
Update: 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.
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.
<?php
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.
If you are on unix/linux you can use the shell to run:
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
Or Rational Relational has a blog post on how to write stored procedure to do this.
Here's a BASH one-liner to truncate all tables from a list of databases:
for j in database_name1 database_name2; \
do for i in `echo 'show tables ' |mysql $j \
|grep -v 'Tables_in'`; do mysql $j -e "truncate $i"; done; done
Please note, truncating will remove all the data from the target tables without any prompting. Perhaps change "truncate $i" to "describe $i" first to make sure the tables in the result set are the ones intended to be emptied.
One more thing: if you want to iterate over every table in all MySQL databases (except information_schema and mysql, I would hope!), substitute the following for the above "database_name1 database_name2":
`echo 'show databases' | mysql | awk '$1 != "information_schema" && \
$1 != "mysql" {if (NR > 1) {print}}'`
So, here's a sample that's less destructive; it performs OPTIMIZE for all tables in every MySQL database (exceptions as noted above):
for j in `echo 'show databases' | mysql | \
awk '$1 != "information_schema" && $1 != \
"mysql" {if (NR > 1) {print}}'`; do for i in \
`echo 'show tables ' |mysql $j |grep -v \
'Tables_in'`; do mysql -e "optimize table $j.$i"; \
done; done
Modify the "action" performed as needed and with much trepidation!
for i in $(echo 'SHOW TABLES' | mysql -uroot -p<pwd> <dbname> | grep -v "Tables_in"); do echo "DELETE FROM $i;"; done | mysql -uroot -p<pwd> <dbname>this will empty all rows from table even if u gets any issue regarding foreign key constraint......
this will not delete table.
DELETE FROM <table name>;
DELETE FROM if TRUNCATE was already posted in an answer sixteen years ago?