Re: Delete from all tables

[Rick James]

It is possible to write a stored procedure that would walk through (using a cursor) information_schema.TABLES to find all the TABLE_NAMEs. For each name, build (using "concat" and "prepare") each DELETE statement, then EXECUTE it.

But why have 5500 tables? That smells like poor schema design, especially if they are similar enough to have the same column.

Note: If your database grows to, say, 55K tables, performance will be bad -- because the Operating System can't deal with lots of files in a single directory. MySQL implementation: One database = one directory; one table = 1-3 files.

If `xxx` is a date, and this DELETE is to purge old data, then let's talk about PARTITIONing on RANGE and using DROP PARTITION instead of DELETE. If both are practical for your app (5500->1 and time-based purging), we can talk about how to make both conversions at the same time.