How do I delete all rows from all tables in a database from MySQL?

Asked May 10 '19 at 00:09

Active May 21 '19 at 23:27

Viewed 55 times

I have a database in my mysql. Inside are tables with each name an email address. Each table has the same structure. So something like

data (database)
    x@x.com (table name)
        [column 1] [column 2] ... (column names)

I'd like to create an event that removes entries after 24 hours. Unfortunately I'm unable to do something like

DELETE FROM data.* WHERE created < DATE_SUB(NOW(), INTERVAL 1 DAY)

It looks like I'm unable to use the wildcard. What's the easiest way to accomplish this?

Edit:

Truncating all tables is not the proper solution I'm looking for as I'm not looking to purge all data in a table, but rather select data based on a condition.

I found the easiest solution would be to just write a script like Nick mentioned to select the data I need from information_schema and delete the entries I need from each table based on the condition I need.

edited May 21 '19 at 23:27

asked May 10 '19 at 00:09

thecodingmate

4

This is why you shouldn't use dynamic table names in the first place. – Barmar May 10 '19 at 00:11
You need to write a procedure that gets all the table names from `INFORMATION_SCHEMA.TABLES`, and constructs dynamic SQL to execute. – Barmar May 10 '19 at 00:12
1

or create an event per table at the same time you create the table. – danblack May 10 '19 at 01:53

How do I delete all rows from all tables in a database from MySQL?

0 Answers0

Linked