Clean multiple numbered mysql tables

Question

I have to update several tables to clean stacked cron jobs on a wordpress multisite install.

The network has 500+ plus sites and tables in wordpress are numbered so you will have:

wp_options
wp_1_options // for blog id 1
wp_2_options
wp_3_options
// and so on...

I was wondering if there is any wildcard I could use to replace that blog id number so it will take all the tables from 1 to x number at once. I tried with wp_*_options without any possitive result. (I'm not use to work with the DBs directly).

The query I'll be executing looks like this:

UPDATE wp_options SET option_value = '' WHERE option_name = 'cron';

Thanks.

Now you are finding out why using metadata to separate a "big" table into smaller ones is a SQL anti pattern which you should not use. — Raymond Nijland, Mar 15 '19 at 20:43
That is not what I did, is what Wordpress does @RaymondNijland — Jaypee, Mar 15 '19 at 20:45
*"That is not what I did, is what Wordpress does"* fair enough now you know Wordpress does it wrong — Raymond Nijland, Mar 15 '19 at 20:45
Why so much hate man? Also you voted negative when my question is valid @RaymondNijland — Jaypee, Mar 15 '19 at 20:46
*"Also you voted negative when my question is valid"* i didn't downvote also there is no way for you to tell. *"Why so much hate man? "* i just don't like Wordpress coding/engine/solutions this is one off the reasons it isn't something personal.. *"I was wondering if there is any wildcard I could use to replace that blog id number so it will take all the tables from 1 to x number at once."* No you will have to write 500+ plus separated`UPDATE` queries — Raymond Nijland, Mar 15 '19 at 20:56

score 2 · Accepted Answer · answered Mar 15 '19 at 22:17

Basically you are looking for dynamic SQL: this means using SQL to generate and execute SQL.

In MySQL, the standard approach requires creating a procedure that uses a cursor to loop through the table names and generate the queries, then use prepared statements to execute them. This can be tricky.

For a one-shot action, I would go for an intermediate option : use a SQL query to generate a list of SQL statements. We can query against INFORMATION_SCHEMA.TABLES to list the tables to update.

SELECT CONCAT(
    'UPDATE ', table_name, 
    ' SET option_value = NULL',
    ' WHERE option_name = ''cron'';'
) as sql_query
FROM information_schema.tables
WHERE table_name LIKE 'wp_%_options'

Then, all that is left to do is manually copy the results of the query to your preferred MySQL client, and execute the whole thing as a script.

NB1: carefully check the results and tests the queries before doing this in your Production database !

NB2: the accepted answer of this SO question explains thoroughly the logic of both approaches, especially the stored procedure solution.

Clean multiple numbered mysql tables

1 Answers1