Explore Courses Blog Tutorials Interview Questions
0 votes
in SQL by (6.1k points)
I am writing some database utility scripts, and one of the tasks I want to do is rebuild the data only, however, leave the schema intact. How can I simply automate this from the command-line using bash and the MySQL tools (no php, etc)?

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

1 Answer

0 votes
by (12.7k points)
edited by

You can try this below query, it will clear the contents of the table. 

TRUNCATE tableName;

You need to be performing at least 2 queries as it appears that "show tables" can not be handled as a subquery, Since I am not aware of how to do this in bash therefore here is a PHP example, probably, 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 requires some error handling.

To become a SQL expert, enroll with this complete SQL Training Course today!

Related questions

0 votes
1 answer
0 votes
1 answer
asked Jul 6, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Jul 9, 2019 in SQL by Tech4ever (20.3k points)

Browse Categories