Delete all data of all tables of all user databases in MariaDB

Question

In our development environment, we want to have a DeleteData.sql query that developers can use to delete all of the data of all tables of all databases of their MariaDB docker instance.

For example, let's say our developers work on a project that has 3 databases:

• Accounts
• Products
• Orders

And let's say each database has 10 tables. We want to run DeleteData.sql and delete data from all tables in these three databases.

We do this to give developers a tool to test different data sets both for performance testing and for data validation and consistency.

But we have no idea how we can achieve this. In SQL Server we would:

1. Use a cursor to loop over databases
2. Create delete statements for all tables of each database dynamically
3. Use execute sp_executesql to run that query dynamically on that database

Is it possible in MariaDB? Is there a better way to get to this result?

Answer 1

Plan A:

Write a script to create the 30 TRUNCATE statements from information_schema.TABLES and put them in the script.

(Note: TRUNCATE is faster than DELETE and also it resets AUTO_INCREMENT, so it is better at going back to the start.)

Plan B:

Use mysqldump --no--data to generate the tables and, I think, all the DROPs needed. (If not, preface with three DROP DATABASEs.)