WordPress как на ладони
rgbcode is looking for WordPress developers. eurobyte.ru - мощные сервера с Дата-центрами в Нидерландах и Москве. От 159 ₽/мес.

20+ полезных SQL запросов для WordPress

Админка WordPress, впрочем как и любая другая админка, является по сути лишь панелью управления базой данных движка. Практически вся информация сайта хранится в БД и порой чтобы изменить ту или иную информацию нужно помучится, например, чтобы закрыть комментарии у всех записей придется провозится немало времени, а если таких записей около 5000, то сделать это через админку вообще мало реально и отважатся на такое только психи, которые не знают, что подобные вещи можно сделать используя SQL запросы, примеры которых вы увидите ниже.

В этой статье я собрал все лучшее из аналогичных статей и дополнил все это своими примерами, кое-что подправил.

SQL запросы можно выполнять, используя, маленький и совершенно безобидный, плагин: SQL Executioner: https://wordpress.org/plugins/sql-executioner/

Или пользуясь конструкцией:

global $wpdb;
$wpdb->query("здесь_код_запроса");

Использовать эту конструкцию можно в файлах темы, например в functions.php или в каком нибудь шаблоне постоянной страницы, в общем, где угодно, главное задействовать.

Перед выполнением запросов не ленитесь делать дампы (бэкапы) Базы Данных, а то ведь можно очень сильно пожалеть...

wp_ в примерах нужно изменить, если у вас префикс таблиц в БД отличается.

Удаление неиспользуемых элементов таксономии из БД одним SQL запросом

Несколько дней назад я удалил ненужные записи с сайта клиента. После чего обнаружил, что на сайте куча ненужных меток (записей в которых нет). Они захламляют БД без надобности и такие пустые метки желательно удалить. Чтобы не делать этого вручную я написал такой запрос:

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE `count` = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id NOT IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id NOT IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

Тут 3 запроса, которые удаляют строки из таблиц: wp_terms, wp_term_taxonomy, wp_term_relationships. Первый запрос находит все термины значение count которых равно 0 и удаляет их. Второй и третий удаляют строки из таблиц wp_term_taxonomy, wp_term_relationships чтобы они соответствовали wp_terms.

Этот запрос удаляет все пустые термины из всех имеющихся таксономий (category, post_tag). Если нужно ограничить такое удаление, например, только метками (post_tag). То первая строка должна выглядеть так:

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE taxonomy='post_tag' AND count=0 );

После удаления данных из базы данных, желательно оптимизировать таблицы, чтобы очистить неиспользуемые индексы и дефрагментировать файлы данных. Это полезно делать, когда из таблицы были удалены большие объемы информации. Оптимизируются таблицы таким запросом:

OPTIMIZE TABLE `wp_terms` , `wp_term_taxonomy` , `wp_term_relationships`;

Как сбросить пароль пользователя

Иногда жизненно необходимо восстановить (сбросить) пароль или просто поменять его, а доступа к админке сайта нет. Для смены пароля можно использовать такой SQL запрос:

UPDATE wp_users SET user_pass = MD5('newpass') WHERE user_login = 'admin'

Здесь пароль — newpass, а логин юзера у кого он будет изменен — admin.

Если вдруг вы забыли логин, но точно помните, что вы были первым юзером на блоге, а значит ваш ID равен 1, то можно идентифицировать юзера для смены пароля по ID (WHERE ID=1):

UPDATE wp_users SET user_pass = MD5('newpass') WHERE ID=1;

MD5('newpass') = e6053eb8d35e02ae40beeeacef203c1a

Как изменить логин пользователя

По умолчанию в WordPress логин создается 1 раз и в дальнейшем его невозможно изменить. Это не совсем так. Логин можно поменять, используя такой SQL запрос:

UPDATE wp_users SET user_login = 'shef' WHERE user_login = 'admin'

Здесь мы меняем логин на shef у пользователя, логин которого был равен admin.

Комментарии

Частенько бывает нужно массово закрыть или открыть комментарии или сделать что-то еще с комментариями. Для таких манипуляций можно использовать следующие SQL запросы:

Закрыть комментарии для всех записей
UPDATE wp_posts SET comment_status = 'closed'
Открыть комментарии для всех записей
UPDATE wp_posts SET comment_status = 'open'
Комментирование только для зарегистрированных пользователей
UPDATE wp_posts SET comment_status = 'registered_only'
Удаление спам комментариев

Некоторые антиспам плагины, пропускают все комменты, но подозрительные помечают как спам, чтобы, если это не так, потом можно было одобрить комментарий. Например, так делает хваленный Akismet (никогда им не пользовался, он действительно так хорош?). Если спам комментов собралось большое множество, то чистить весь этот мусор руками никак не идет в сравнение с использованием такого простого SQL запроса:

DELETE FROM wp_comments WHERE comment_approved = 'spam'

Если нужно удалить все не одобренные комментарии, используйте:

DELETE FROM wp_comments WHERE comment_approved = 0
Удалим URL у всех комментариев

Чтобы очистить значения поля URL у всех комментариев, можно использовать такой запрос:

UPDATE wp_comments SET comment_author_url=''
Закрыть комментарии в рубрике

Если нужно закрыть комментарии только в какой-то одной рубрике, можно использовать такой SQL запрос, который разом закроет возможность комментирования для всех постов из рубрики, например, stat (альтернативное название рубрики, слаг):

UPDATE wp_posts p
	LEFT JOIN wp_term_relationships rel ON ( p.ID = rel.object_id )
	LEFT JOIN wp_term_taxonomy tax ON ( tax.term_taxonomy_id = rel.term_taxonomy_id  )
	LEFT JOIN wp_terms tm ON ( tm.term_id = tax.term_id )
SET p.comment_status = 'closed'
WHERE tm.slug = 'stat'

stat - рубрика в которой мы закрываем комментарии.

Закрыть комментирование в старых постах

Чтобы закрыть возможность оставлять комментарии для старых постов, допустим, опубликованных до 1 января 2010 года, можно использовать такой SQL запрос:

UPDATE wp_posts SET comment_status = 'closed'
WHERE post_date < '2010-01-01' AND post_status = 'publish'

где, как вы догадались, 2010-01-01 — дата позднее которой комменты будут закрыты.

Удаление комментариев с плохими УРЛами

Можно удалить комментарии по маске, например, если в УРЛе комментатора встречается сочетание букв poker:

DELETE a,b
FROM wp_comments a
	LEFT JOIN wp_commentmeta b ON ( a.comment_ID = b.comment_id )
WHERE
	a.comment_author_url LIKE '%poker%'
Изменение сайта комментатора

Теоретически УРЛ, который указывает комментатор в поле "сайт" можно изменить при редактировании комментария, но если комментариев этого комментатора много, то везде изменять УРЛ — занятие для обезьянок, проще запросом:

UPDATE wp_comments SET comment_author_url = 'http://example.com'
WHERE comment_author = 'Kama'
AND comment_author_email = 'kama@gmail.com'

Где example.com - УРЛ который мы хотим установить для комментатора, Kama - ник комментатора, а kama@gmail.com - его email. Ник и мыло я решил указать, чтобы точно идентифицировать комментатора.

Еще один вариант аналогичный предыдущему примеру. Заменит все совпадения old-example.com на new-example.com, в УРЛах всех комментаторов:

UPDATE wp_comments
SET comment_author_url = REPLACE( comment_author_url, 'old-example.com', 'new-example.com' )
Меняем ID родительского комментария

Иногда пользователи некорректно постят древовидные комментарии — отвечая на коммент не используют кнопку "ответить" в итоге получается, что ответ теряется. Чтобы исправить эту ситуацию можно изменить ID родительского коментария, чтобы правильно расположить новый коммент в дереве:

UPDATE wp_comments SET comment_parent=21 WHERE comment_ID=97

21 - родительский комментарий, 97 - комментарий который нужно поправить.

E-mail'ы комментаторов

Чтобы собрать все мылы комментаторов, например, чтобы сделать какую-нибудь рассылку, можно использовать такой SQL запрос:

SELECT DISTINCT comment_author_email FROM wp_comments

В результате будет получен список из уникальный email'ов.

Пинги и уведомления

Так же как комментариями, можно управлять пингами. Вот несколько примеров такого управления:

Закрываем возможность принимать пинги

Вели вы блог, вели и вдруг пошла нагрузка на сервер через пинги, которые, в общем, никакой практической пользы не несут и которые, очень даже, можно вообще закрыть и забыть о них. Чтобы закрыть все пинги используйте такой SQL запрос.

UPDATE wp_posts p SET p.ping_status = 'closed'
Открыть возможность принимать пинги
UPDATE wp_posts p SET p.ping_status = 'open'
Удаляем все уведомления

Уведомления — это те же пинги — это такие "комментарии", которые оповещают вас о том, что Вася Пупкин сослался на вас. Если вы решили что такие горе-комментарии вам не нужны, их можно удалить оптом, используя такой SQL запрос:

DELETE FROM wp_comments WHERE comment_type = 'pingback';

После удаления пингов логично закрыть возможность получать пинги (см. предыдущий пример), также пинги нужно закрыть в настройках.

Метки

Несмотря на то что WordPress предоставляет достаточно гибкий интерфейс управления метками, вам могут пригодится следующие SQL запросы.

Получаем пустые метки

Спустя несколько лет ведения блога, могут появится пустые метки - метки в которых нет записей. В поздних версиях WP такие метки можно удалить, на странице настроек меток, отсортировав их по количеству записей, а в ранних версиях так отсортировать не получится и для такой операции вам в помощь такой SQL запрос, который получит все метки в которых нет ни одной записи:

SELECT * FROM wp_terms wt
	INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id
WHERE wtt.taxonomy='post_tag' AND
wtt.COUNT=0

Вместо post_tag можно написать любую другую таксономию, например категории.

Удаляем пустые метки

В предыдущем примере мы получали пустые метки, а теперь просто удалим их:

DELETE a,b,c FROM wp_terms a
	LEFT JOIN wp_term_taxonomy c ON a.term_id = c.term_id
	LEFT JOIN wp_term_relationships b ON b.term_taxonomy_id = c.term_taxonomy_id
WHERE (
	c.taxonomy = 'post_tag' AND
	c.count = 0
	)

Ну, вы же понимаете, если изменить c.count = 0 на c.count < 2, то будут удалены все метки с 0 и 1 записями в них.

Смена домена

Исправляем домен в опциях

При переезде на другой домен, помимо того, что нужно заменить домен везде где он встречается в шаблоне, также нужно изменить записи в Базе Данных в двух местах, в таблице опций (wp_options):

UPDATE wp_options SET option_value = 'http://example.com/'
WHERE option_name = 'home' OR option_name = 'siteurl'

Запрос меняет значение полей siteurl и home. Не забудьте поменять example.com на ваш новый домен!

Исправляем домен в записях

При смене домена, надо позаботится и о том, чтобы в постах были правильные внутренние ссылки, т.е. ссылки из статей на другие статьи блога, после смены домена станут битыми. Обычно конечно настраивается перенаправление со старого домена на новый в .htaccess или PHP с 301 редиректом, но помимо этого эстетически правильно, если в статьях не будет ссылок на старый домен. Этим запросом мы заменим все виды ссылок, включая ссылки на картинки:

UPDATE wp_posts
SET post_content = REPLACE (post_content, 'http://old-example.com', 'http://new-example.com')

old-example.com и new-example.com старый и новые домены, соответственно. Не забудьте изменить.

Аналогично можно поменять любую строку, например, слово Вордпресс на WordPress. См. пример ниже.

Меняем домен в произвольных полях

В произвольных полях также могут быть записи хранящие какие-либо УРЛы на старый домен, поэтому при смене домена возможно нужно заменить домен и в произвольных полях:

UPDATE wp_postmeta
SET meta_value = REPLACE (meta_value, 'http://old-example.com','http://new-example.com')
Редактируем GUID

При смене домена сайта желательно позаботиться о том, чтобы у всех записей поле guid в таблице wp_posts было корректное.

Это поле используется как уникальный ID для идентификации записи в RSS ленте. Также, поговаривают, что оно нужно для корректного перенаправления с некорректных УРЛов, но это не так — я проверял secret

UPDATE wp_posts
SET guid = REPLACE (guid, 'http://www.oldblog.ru', 'http://www.newblog.ru')

Для некоторых проектов я использую поле guid для других целей и для обновления этого поля я написал мини-плагин.

Замена текста в записях (постах)

Можно заменить текст в постах и сделать это прямо в Базе Данных. Например можно добавить атрибут target="blank" ко всем ссылкам с атрибутом rel="nofollow":

UPDATE wp_posts SET post_content = REPLACE (post_content, 'rel="nofollow"', 'target="blank" rel="nofollow"')

Еще можно проставить внутренние ссылки с определенным анкором, например, из слова "WordPress" сделать ссылку на какую-нибудь релевантную страницу, чтобы поднять её значимость. В прочем, для этого существуют специальные плагины, которые не трогают текст в БД, а создают ссылки на лету:

UPDATE wp_posts SET post_content = REPLACE (post_content, ' WordPress ', ' <a href="http://example.com/статья-о-wordpress">WordPress</a> ')

Произвольные поля (postmeta)

Удаляем ненужные произвольные поля

Стоял плагин и создавал себе произвольные поля, как вдруг — он стал не нужен и вы его удалили, а осиротелые, никому не нужные произвольные поля остались в Базе Данных. В такой или подобной ситуации, все произвольные поля с названием, например "meta_name" можно удалить таким простым SQL запросом:

DELETE pm FROM wp_postmeta pm WHERE pm.meta_key = 'meta_name'

Если название произвольного поля (meta_name) в кириллице, то убедитесь, чтобы кодировка файла из которого будет производиться SQL запрос соответствовала кодировке блога (обычно UTF-8 без BOM).

Получим все произвольные поля с пустым значением

Несмотря на то, что в админке создать произвольное поле не задав ему никакое значение не получится, по крайней мере стандартными средствами, такие "пустые" произвольные поля все же могут быть в вашей Базе Данных. Например, их могут оставить всякие там плагины или кривые руки. Чтобы посмотреть есть ли подобные поля и затем решить их судьбу, воспользуетесь таким SQL запросом:

SELECT *
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

Меняем авторов

Вообще, приписывать свое имя под чужой статьей — не хорошо, поэтому в данном примере подразумевается, что вы сменили ник или купили весь контент с авторским правом и вам во что бы то не стало, нужно изменить авторство у статей. Чтобы поменять одного автора статьи на другого (оба пользователя должны быть зарегистрированы разумеется), можно использовать такой SQL запрос:

UPDATE wp_posts SET post_author=1 WHERE post_author=2

где, 1 - новый пользователь, 2 - старый. ID пользователей ищите в админке.

Удаляем ревизии (редакции) записей

По умолчанию в WordPress включены ревизии записей. Они засоряют базу данных и редко по-настоящему нужны. Если ревизии у вас включены, то я рекомендую, очищать из хотя бы раз в год. Сделать это можно таким запросов в phpMyAdmin:

-- зависимость с таксономиями
DELETE FROM wp_term_relationships WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type = 'revision');

-- метаполя
DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'revision');

-- сами ревизии
DELETE FROM wp_posts WHERE post_type = 'revision';

Данный запрос удалить редакции записей и автосохранения. Также удалит связанные метаполя (если они есть) и связь ревизии с таксономией (если она есть).

Вариант 2

В качестве альтернативного запроса, можете воспользоваться таким. Это пример удаления с JOIN:

DELETE a,b,c,d
	FROM wp_posts a
		LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
		LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
		LEFT JOIN wp_comments d ON (a.ID = d.comment_post_ID)
	WHERE a.post_type = 'revision'

Тут я также добавил удаление строк из таблицы wp_comments, мало ли вдруг в комментариях есть комменты которые относятся к ревизиям. Вообще их быть там не должно.

Деактивация всех плагинов

Бывают ситуации, когда невозможно зайти на страницу плагинов из-за одного некорректно работающего плагина. Такой плагин можно удалить через FTP, а можно просто деактивировать все плагины SQL запросом, что даст возможность войти на страницу настроек плагина:

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins'

Очистка кэша фида

WordPress сохраняет feed в таблицу опций и обновляет его при публикации новой записи или спустя определенный промежуток времени. Если нужно очистить кэш фида, то можно воспользоваться таким запросом:

DELETE FROM `wp_options` WHERE `option_name` LIKE ('_transient%_feed_%')

Получение названия всех колонок (полей) таблицы

SHOW COLUMNS FROM my_posts;

Или

global $wpdb;

$data = $wpdb->get_results("SHOW COLUMNS FROM $wpdb->posts");

print_r( $data );

/*
Array (
	[0] => stdClass Object
		(
			[Field] => ID
			[Type] => bigint(20) unsigned
			[Null] => NO
			[Key] => PRI
			[Default] =>
			[Extra] => auto_increment
		)

	[1] => stdClass Object
		(
			[Field] => post_author
			[Type] => bigint(20) unsigned
			[Null] => NO
			[Key] => MUL
			[Default] => 0
			[Extra] =>
		)

	[2] => stdClass Object
		(
			[Field] => post_date
			[Type] => datetime
			[Null] => NO
			[Key] =>
			[Default] => 0000-00-00 00:00:00
			[Extra] =>
		)

	...
)
*/

Транзакции через WPDB

Чтобы понять зачем могут пригодится транзакции, допустим нам нужно удалить 10 строк таблицы и если хоть с одним из запросов удаления что-то пойдет не так, нужно ничего не делать, не удалять то что уже было удалено.

В этом нет ничего сверхобычного, нужно просто о выполнить запрос START TRANSACTION, затем нужные нам запросы и в конце запрос COMMIT или ROLLBACK (в зависимости от результата):

В wordpress, используя wpdb это можно записать так:

$wpdb->query( 'START TRANSACTION' );
$result1 = $wpdb->delete( $table, $where );
$resul2 = $wpdb->delete( $table, $where );
if ( $result1 && $result2 ) {
	$wpdb->query( 'COMMIT' ); // все прошло ок, комитим транзакцию
}
else {
	$wpdb->query( 'ROLLBACK' ); // что-то пошло не так, откатываемся - ничего не делаем
}

Имейте ввиду, что движок MySQL MyISAM, не поддерживает транзакции. Поэтому для использования транзакций, убедитесь, что все ваши таблицы используют InnoDB.

Зачем нужны mysql транзакции

Транзакции в MySQL необходимы для поддержки целостности данных и обеспечения контроля за их изменениями. Вот некоторые основные причины, по которым они важны:

  1. Атомарность: Транзации обеспечивают атомарность операций, то есть либо все операции в транзакции успешно выполняются, либо ни одна не выполняется.

  2. Согласованность: После каждой транзакции база данных остается в согласованном состоянии.

  3. Откат: Если в процессе транзакции происходят какие-либо ошибки, то все изменения, произошедшие в рамках этой транзакции, откатываются, что предотвращает возможное повреждение данных.

  4. Параллелизм: Транзакции также помогают избегать конфликтов при параллельном доступе к данным.

  5. Безопасность: Транзакции предоставляют механизм защиты от потери или повреждения данных в результате сбоев системы или ошибок.

  6. Изоляция: Каждая транзакция выполняется изолированно от других транзакций, что обеспечивает безопасность данных.
48 комментариев
Полезные 5 Все
    Войти