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?

• Kondybas: As far as in mysql/mariadb cursors are available only within stored procedures there is no difference between 1 and 3. I would use that approach - CALL truncate_tables( someuser ) - to truncate all the tables.
• J.D.: I'm not sure MariaDB has sp_executesql, did you mean to tag your database system as such? If so, maybe you meant EXECUTE IMMEDIATE?
• Saeed Neamati: @J.D., that's for SQL Server. The problem with execute_immediate is that it seems that it only works inside a stored procedure.

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.)

• Saeed Neamati: Plan A is not dynamic. It can't be used for many applications with different sets of databases and tables. Thus that's not applicable. Plan B is interesting. In fact, Plan B is about deleting all databases first and then recreating their structure without data.
• Rick James: @SaeedNeamati - DROP + reCREATE is the fastest way. (I think it is what TRUNCATE does. Plan A can be dynamic -- if you construct the statements, then PREPARE and EXECUTE them.