Несколько команд для очистки базы данных MySQL на примере БД для WordPress.
Как делать выборку нужных значений
SELECT
COUNT(0) AS total,
COUNT(CASE WHEN meta_key LIKE '\_oembed%' THEN 1 END) AS oembeds_cache,
COUNT(CASE WHEN meta_value LIKE 'field\_%' THEN 1 END) AS acf_fields_leftovers
FROM wp_postmeta
WHERE post_id = 4884
COUNT(0) AS total,
COUNT(CASE WHEN meta_key LIKE '\_oembed%' THEN 1 END) AS oembeds_cache,
COUNT(CASE WHEN meta_value LIKE 'field\_%' THEN 1 END) AS acf_fields_leftovers
FROM wp_postmeta
WHERE post_id = 4884
Изначальное кол-во строк в wp_options:
Rows 1 — 1000 of 1 312 434 from table
Общий запрос показал ненужных полей:
Удаляем эти поля из таблицы wp_postmeta:
DELETE
FROM wp_postmeta
WHERE meta_key LIKE '\_oembed%' OR meta_value LIKE 'field\_%'
FROM wp_postmeta
WHERE meta_key LIKE '\_oembed%' OR meta_value LIKE 'field\_%'
То же самое проделываем для таблицы wp_options:
DELETE
FROM wp_options
WHERE option_value LIKE 'field\_%'
FROM wp_options
WHERE option_value LIKE 'field\_%'
Сносим другие поля
По аналогии сносим другие поля
DELETE
FROM wp_options
WHERE option_name LIKE 'mail2author\_%'
DELETE
FROM wp_options
WHERE option_name LIKE 'bmr_sent_emails\_%'
DELETE
FROM wp_options
WHERE option_name LIKE 'cache-group\-%'
DELETE
FROM wp_options
WHERE option_name LIKE 'rel\_%'
FROM wp_options
WHERE option_name LIKE 'mail2author\_%'
DELETE
FROM wp_options
WHERE option_name LIKE 'bmr_sent_emails\_%'
DELETE
FROM wp_options
WHERE option_name LIKE 'cache-group\-%'
DELETE
FROM wp_options
WHERE option_name LIKE 'rel\_%'
