Использование SQL-запросов для изменения данных подразумевает, что вы уже находитесь на таком уровне владения WordPress, а также PHP и MySQL, когда можете самостоятельно править базу данных, а не использовать для этой цели плагины. В этом случае вы должны знать, где у вас на локальном и на реальном сервере находится phpMyAdmin, какие таблицы в БД создает WordPress и что есть такое sql-запросы. На всякий случай, давайте посмотрим, как вызвать phpMyAdmin на локальном сервере OpenServer: перейдите в меню Дополнительно → PhpMyAdmin.
Сразу оговорюсь, что любые эксперименты с SQL-запросами подразумевают, что вы сначала создали бэкап своей БД, т.е. сохранили sql-файл со всеми таблицами или с какой-то одной таблицей, с которой вы будете работать либо у себя на сервере, либо на локальном компьютере. Иначе вы можете потерять все данные без возможности восстановления их.
Бэкап (резервная копия) базы данных
Самым простым бэкапом (резервной копией) является экспорт всей базы данных или отдельной таблицы (нескольких таблиц). Для этого нужно перейти в phpMyAdmin, выбрать нужную базу данных, если их у вас несколько, или одну таблицу и нажать на кнопку Экспорт (Export).
Далее выберите не стандартный быстрый метод экспорта, а обычный со всеми возможными настройками.
Обязательным параметром, который вам нужно будет отметить флажок "Добавить выражение DROP TABLE / TRIGGER". Если вам все-таки придется ваш файл импортировать после неудачных SQL-запросов, то те таблицы, которые уже существуют в базе данных будут удалены и заменены на те, что вы экспортировали в sql-файл.
Как правило, вы получите либо предложение сохранить sql-файл (в браузере Mozilla Firefox, например), либо загрузка 'этого файла начнется автоматически (Chrome), т.к. за это отвечают настройки по умолчанию.
При желании вы можете сохранить сжатую версию в формате zip или gzip, но это необязательно.
Импорт сохраненной резервной копии базы данных
После экспорта рабочей базы данных на локальном сервере вы можете смело экспериментировать с различными SQL-запросами, т.к. это, пожалуй, единственный способ разобраться, как все устроено. Даже, если вы удалите больше, чем хотелось в начале, у вас будет sql-файл с рабочей версией БД, к которому в любой момент можно вернуться, или "откатить изменения".
Для этого необходимо перейти в phpMyAdmin на вкладку Импорт (Import), указать путь к сохраненному sql-файлу и загрузить его, нажав кнопку "Вперед" ("Go"). Поскольку при экспорте БД вы отметили флажком настройку "Добавить выражение DROP TABLE / TRIGGER", все ваши существующие таблицы будут заменены на те, что были ранее, и вы вернетесь к первоначальному результату.
Обратите внимание на параметр "Максимальный размер" для загружаемого файла - на реальных серверах он может быть куда меньше 100Мб.
Имейте ввиду, что вы можете выполнять экспорт БД на любом шаге внесения изменений с помощью SQL-запросов. Главное, чтобы вы потом разобрались, какая копия какой вариант содержит. Т.к. имена файлов wp_post(1).sql, wp_post(2).sql, wp_post(3).sql малоинформативны, советую изменять их на wp_post-no-revisions.sql или подобным образом. Или проверять, как выглядит сайт и удалять ненужные бэкапы БД.
Имитация SQL-запроса
PhpMyAdmin позволяет не только выполнять SQL-запросы, но и имитировать их. Для этого после добавления SQL-запроса вам необходимо щелкнуть на кнопке "Имитировать запрос" вместо щелчка на кнопке "Вперед". Эта кнопка появляется, если вы обновляете (UPDATE) запись в таблице или удаляете ее (DELETE). Если при этом вы увидите надпись "Затронуто n строк" (вместо n - любая цифра > 0), то все в порядке - запрос будет выполнен для ряда элементов в указанной таблице. Если же вы увидите 0, то либо строк, соответствующих вашему запросу в таблице нет, либо вы сформировали его неверно.
Ошибка может быть:
- В названии столбца (его лучше найти в правой части страницы с SQL-запросом и перенести в поле запроса кнопкой "<<");
- В том значении, которое вы ищите в этом столбце;
- В условии, которое вы сформировали (скопировали) после ключевого слова WHERE
Иногда в качестве ошибки вы получите сообщение вида MySQL Error. Это значит, что ваш запрос сформирован в корне неверно. Ошибка в этом случае может быть:
- В названии столбца (потеряли или добавили буквы или другие символы) - этот вариант ошибки вы видите на скриншотах ниже;
- Неверном условии.
Не закрыты двойные кавычки в конце запроса.
Имитация SQL-запроса позволяет исправить его ДО выполнения. Это хороший вариант проверки ваших действий в базе данных.
Примечание: в разных версиях локальных и реальных серверов могут стоять разные версии phpMyAdmin, которые имеют разный интерфейс и язык отображения этого интерфейса (чаще всего русский или английский), поэтому скриншоты в этой статье и в вашем phpMyAdmin могут отличаться.
SQL-запросы для WordPress
Теперь приступим к рассмотрению наиболее популярных SQL-запросов, которые связаны с такими распространенными в WordPress задачами:
- Удаляем ревизии WordPress
- Удаляем автосохранение записей/страниц
- Заменяем записи на страницы и наоборот
- Поиск нужных постов в БД
- Удаляем конкретный пост (запись, страницу) или медиафайл
- Обновляем контент нескольких записей
- Заменяем протокол http:// на https://
- Закрываем комментарии для всех записей
- Заменяем пароль пользователя
- Заменяем логин пользователя
Удаляем ревизии WordPress
Одним из наиболее популярных запросов в базу данных WordPress является запрос на удаление ревизий. Ревизии - это дубликаты вашей записи/страницы, которые сохраняются при любом редактировании этой записи/страницы в базе данных (сокращенно БД) как отдельная строка.
С одной стороны это хорошая возможность вернуться к предыдущему варианту вашей статьи, с другой - раздутие базы данных, если вы пишите статьи сразу в редакторе WordPress, а не в Word или в другом текстовом редакторе. Чем больше таких сохранений, тем больше записей в БД, тем она больше места занимает у вас на сервере.
Реально наличие ревизий мне понадобилось один или 2 раза, когда случайно была стерта половина уже готовой статьи, и после этого черновик был сохранен. Во всех остальных случаях их приходится чистить хотя бы раз в месяц, если вы постоянно публикуете статьи на сайте.
Вы можете использовать для удаления ревизий и оптимизации базы данных WordPress такие плагины, как WP Optimize, Optimize Database after Deleting Revisions, Simple Revisions Delete, Delete Post Revisions In WordPress и другие, но целью этой статьи является рассмотрение работы с базой данных, поэтому мы используем для этой цели phpMyAdmin.
Итак, ревизии сохраняются в вашей БД в таблице wp_posts (вместо префикса wp_ может быть любой, который вы назначили при установке WordPress). Поэтому запрос на удаление ревизий будет такой:
| 1 2 3 | DELETE FROM wp_posts WHERE post_type = "revision"; //или DELETE FROM wp_posts WHERE post_name LIKE "%revision%" |
Реальный случай - таблица wp_posts после такого запроса уменьшилась с 27Мб до 9.9Мб, т.е. почти в 3 раза. Впечатляет количество мусора в вашей БД?
Если вы хотите, чтобы ревизии вообще не сохранялись у вас в БД, в файле wp-config.php в корне вашего сайта добавьте строку
| 1 | define('WP_POST_REVISIONS', false); |
Удаляем автосохранение записей/страниц
Возможно вы замечали, что при редактировании записи/страницы у вас через некоторое время становится недоступной кнопка "Сохранить", "Опубликовать" или "Обновить" справа в админке WordPress. Это система выполняет автосохранение вашей статьи в базу данных для того, чтобы вы могли использовать то, что написали, если случайно выключите компьютер или он у вас сделает это сам при низком заряде батареи.
По умолчанию WordPress выполняет автосохранение постов каждую минуту, или 60 секунд. Вы можете изменить это значение, указав другое число (подразумевается, что вы задаете время в секундах).
Для начала мы посмотрим, как можно увеличить интервал между автосохранениями. Аналогично тому, как мы отменили сохранение ревизий, можно задать интервал для автосохранения записей/страниц в wp-config.php. Для этого нужно добавить строку
| 1 | define('AUTOSAVE_INTERVAL', 300 ); |
Если же вы намерены совсем от этого избавится, задайте интервал в секундах, аналогичный 24 часам:
| 1 2 | /** Disable autosaves **/ define('AUTOSAVE_INTERVAL', 86400); |
Только хорошо подумайте - а стоит ли это делать? В случае непредвиденных обстоятельств вы не сможете восстановить напечатанный текст.
Чтобы очистить базу данных от ревизий постов с автосохранением, необходимо задать такой SQL-запрос:
| 1 | DELETE FROM `wp_posts` WHERE `post_type` = "revision" AND `post_name` LIKE "%autosave%" |
Запросы в phpMyAdmin вы можете формировать с помощью интерфейса этого web-инструмента: выбираете нужный запрос (SELECT, INSERT, UPDATE или DELETE), в условие после ключевого слова WHERE переносите из правого поля один из столбцов кнопкой "<<" и дописываете, чему должно быть равно значение этого столбца.
Либо просто вставить приведенный код в поле, заменив предлагаемый по умолчанию вариант с SELECT:
Заменяем записи на страницы и наоборот
Предположим, что вы создали страницу, которая должна быть записью, либо наоборот, сделали запись, которую хотели бы превратить в страницу. Инструментов сделать это из админки у WordPress нет , а поменять надо. Конечно, можно удалить запись/страницу в корзину и создать их заново в виде нужного вам варианта, но что делать, если опять-таки это надо сделать массово.
Давайте разберемся в разнице между записями и страницами в WordPress. Если мы говорим о записях, то они характеризуются тем, что имеют разделение по рубрикам, типам записей (стандартная, аудио, видео, галерея и т.п.), а также появляются на сайте куда чаще страниц. Страницы - это более "постоянные" записи, которые, появившись однажды на сайте, либо вообще не меняются, либо меняются весьма незначительно. Плюс количество страниц на сайте ограничено, т.к. именно они, как правило, попадают в меню, а его нельзя растянуть до бесконечности. В отличие от записей, страницы не имеют рубрик и типов, зато для них можно назначить шаблон отображения, который часто связан с настройками темы или виджетами, или с кастомными типами записей, например, с отзывами или товарами.
Вернемся к нашим записям, которые должны стать страницами. Например, мы необдуманно поместили в записи то, что должно быть страницами с заголовками "О проекте" и "Контакты".
Для того чтобы поменять тип нашей записи на нужный, находим ее ID. Проще всего получить его в строке статуса при наведении на название-ссылку этой записи. В приведенном скриншоте пока еще запись "Контакты" имеет id=14, а "О проекте" - id=23. Пусть вас не смущает такая разница в цифрах ID между рядом стоящими записями. Дело в том, что в таблицу wp_posts, в которой хранится информация о записях и страницах, также попадают сведения о всех загруженных изображениях, и каждому такому элементу присваивается номер в порядке их добавления. Он-то и является числом, которое связано с параметром (столбцом в БД) ID.
Формируем SQL-запрос такого вида:
| 1 | UPDATE wp_posts SET post_type= 'page' WHERE ID=14 or ID=23 |
В таблице вывода записей мы теперь не видим наших "Контактов" и "О проекте", зато они появились в таблице вывода всех страниц.
Если вы захотите поменять тип "страница" (page) на тип "запись" (post), то запрос будет выглядеть несколько иначе:
| 1 | UPDATE wp_posts SET post_type='post' WHERE ID=14 or ID=23 or ID=27 |
Нужно отметить, что аналогичную операцию вы можете проделать и вручную, если боитесь SQL-запросов, но займет это намного больше времени.
Поиск нужных постов в БД
Вернемся немного назад и предположим, что у нас "Контакты" и "О проекте" еще являются записями, а не страницами. Мы можем воспользоваться поиском в phpMyAdmin, нажав на соответствующую вкладку.
Теперь нам нужно определиться с параметрами поиска. Сначала попробуем поискать по заголовку нашей пока еще записи. С точки зрения WordPress эта информация расположена в столбце post_title таблицы wp_posts. Мы можем ввести в поле "Значение" слово Контакты или О проекте без всяких кавычек, и в качестве результата нам вернется одна строка таблицы, описывающая одну запись.
Однако это неинтересно. Хочется получить сразу обе строки. Поэтому поищем то, что в заголовках этих пока еще записей является одинаковым. В данном случае в обоих названиях есть сочетание букв "кт" - Контакты и О проекте и внести это в поле поиска со спецсимволами %...%, которые говорят о том, что данное сочетание букв встречается в любом месте заголовка.
Есть еще более простой способ задать поиск по символам внутри слова. Выберите в списке оператор не "LIKE", который идет по умолчанию, а следующий за ним "LIKE %...%" и введите в поле "Значение" только нужные символы:
Нажимайте клавишу Enter и смотрите на те 2 строки, которые мы получили из поиска. Это именно те 2 записи, которые нам были нужны. Сразу оговорюсь, что в том случае, если у вас много записей, велика вероятность того, что строк будет куда больше.
Еще один момент, на который хотелось бы обратить внимание: поиск - это не что иное, как SQL-запрос типа SELECT, который вы видите выше таблицы с результатами. В нашем случае это запрос такого вида, только с другим префиксом для таблиц:
| 1 | SELECT * FROM `wp_posts` WHERE `post_title` LIKE '%кт%' |
Теперь необходимо поменять тип записи "post" на "page". Сделать это можно, найдя в конце таблице столбец post_type и дважды кликнув на тексте "post". Меняйте его на "page" и нажимайте клавишу Enter. Все, тип записи вы изменили.
Второй путь несколько сложнее, но его можно использовать сразу для нескольких строк. В начале строки есть значок в виде карандаша, который приведет вас на страницу редактирования всех полей данной строки (скриншот ниже). Такой же карандаш есть внизу под всеми строками. Его имеет смысл использовать, если вы отметили все строки или несколько из них, и затем его нажали. Тогда вы редактируете ряд полей для нескольких записей, например.
Обратите внимание, что всплывающая подсказка сообщает нам о том способе редактирования, который мы использовали выше.
После нажатия на значок карандаша вам нужно перейти в нижнюю часть открывшейся страницы, найти поле post_type и заменять "post" на "page", а затем нажать кнопку "Вперед" или клавишу Enter.
На самом деле поиск в phpMyAdmin - это очень удобный инструмент. С его помощью имеет смысл не только менять записи на страницы (мы видели и способ попроще), а находить различные настройки или метаполя, которые нужно изменить при переносе, например, с локального на реальный хостинг.
С тем, что и где искать имеет смысл разбираться в зависимости от конкретной задачи.
Удаляем конкретный пост (запись, страницу) или медиафайл
Как мы уже видели, в таблице wp_posts сохраняются данные о записях, страницах и любых загруженных медиафайлах. Каждый из них имеет свой id, который вы можете посмотреть в админке (способ для записей описан выше). Именно по этому id к им обращается WordPress при любых попытках редактирования.
Если вы считаете, что вам не нужен какой-либо пост (запись) или страница, то вы можете удалить их через БД таким запросом:
| 1 2 | DELETE FROM `wp_posts` WHERE `ID` = 27; DELETE FROM `wp_postmeta` WHERE `post_id` = 27; |
На самом деле, это, пожалуй, не слишком оптимальный подход к удалению, если запись/страница/медиафайл у вас один. Намного проще сделать это через админку, удалив соответствующий элемент, используя таблицу всех записей, страниц или медиафайлов. В случае последних будут удалены и все варианты их нарезки, которых в WordPress, как минимум 3 (скриншот пункта меню консоли Настройки → Медиафайлы):
Вы можете удалить любой файл в консоли (админке) WordPress, отобразив их в виде списка:
Массовое удаление медиафайлов из библиотеки с помощью sql-запроса:
| 1 2 3 | DELETE FROM `wp_posts` WHERE `post_type` = "attachment"; DELETE FROM `wp_postmeta` WHERE `meta_key` = "_wp_attached_file"; DELETE FROM `wp_postmeta` WHERE `meta_key` = "_wp_attachment_metadata"; |
Будьте осторожны! Такой запрос удалит ВСЕ ваши изображения!
Есть еще один вариант, когда стоит удалять посты/страницы из БД с помощью запроса. К сожалению, это часто происходит после внедрения вируса на ваш сайт, причем такого, который создает фейковые посты за вас. На скриншоте утрированный вариант с нечитаемыми заголовками, но, думаю, принцип понятен.
Здесь показан вариант, когда вы добавили свою запись после вирусных постов и, конечно, ее вы удалять не собираетесь. Поэтому вам придется посмотреть, какие ID были у записей, страниц и изображений, которые публиковались ДО вируса и ПОСЛЕ него, а затем сделать проверочную выборку записей в таблице wp_post примерно таким запросом:
| 1 | SELECT * FROM wp_posts WHERE ID > 23 and ID <32 |
В нашем упрощенном случае все ненужные посты вместе с ревизиями и автосохранениями попали в выборку:
Поэтому теперь мы можем их удалить, изменив sql-запрос (не забудьте предварительно сделать бэкап вашей базы данных):
| 1 | DELETE FROM wp_posts WHERE ID > 23 and ID <32 |
Проверяем записи в таблице в админке WordPress. Теперь все чисто:
Обновляем контент нескольких записей
Не секрет, что WordPress, как система с открытым кодом, подвержена различным атакам и вирусам, хоть над этой CMS и работает группа разработчиков, которая стремится все дыры в безопасности закрыть. Мне приходилось сталкиваться с тем, что внутрь текста любой записи/страницы внедрялся либо код со ссылками, либо скрипт, который приводил к неработоспособности мегаменю, например, либо просто текст, который возникал ни к месту.
Давайте предположим, что в наши записи закрался текст, выделенный жирным "Белки - это лучшие друзья кошек". Не ищите никакого смысла в этой фразе - ее просто нет. Зато есть необходимость найти все вхождения этих слов, причем обрамленных тегами <strong>...</strong>, во всех записях и страницах. Поэтому сначала воспользуемся поиском для столбца post_content:
После нажатия на клавишу Enter увидим, что таких строчек у нас 8 штук.
Сформируем SQL-запрос для замены этого текста на пустую строку ("" - 2 кавычки рядом):
| 1 | UPDATE wp_posts SET post_content = REPLACE (post_content, "<strong> Белки - это лучшие друзья кошек.</strong>", ""); |
Давайте сначала сымитируем запрос:
Видим, что количество затронутых строк у нас тоже 8. Потому можем теперь нажимать кнопку "Вперед" и выполнять запрос по-настоящему. После этого также выведется сообщение, что затронуто 8 строк.
Если еще раз выполнить поиск, то, вполне возможно, вы увидите, что текст еще остался в ревизиях. Тут уж вам решать - то ли повторить операцию, то ли удалить ревизии еще одним запросом.
Заменяем протокол http:// на https://
В том случае, если вы приобрели SSL-сертификат через некоторое время после запуска сайта, все ваши ссылки и изображения были сохранены в базу данных с протоколом http:// вместо https://. Поэтому вам нужно будет добавить следующие правила в файл .htaccess:
| 1 2 3 4 5 | RewriteEngine on # force SSL RewriteCond %{SERVER_PORT} ^80$ RewriteRule ^(.*)$ https://%{SERVER_NAME}%{REQUEST_URI} [L,R=301] |
Этот код будет перенаправлять запросы пользователей через 301 редирект (moved permanently). В том случае, если вы не хотите использовать 301 редирект, то вместо последней строки нужно записать так:
| 1 | RewriteRule ^(.*)$ https://%{SERVER_NAME}%{REQUEST_URI} [L,R] |
Однако вы можете подойти к вопросу замены протоколов с точки зрения правки базы данных. Применяемые SQL-запросы затрагивают таблицы wp_option (настройки сайта), wp_posts (записи, страницы, изображения), wp_postmeta (метаданные записей):
| 1 2 3 4 | UPDATE wp_options SET option_value = replace(option_value, 'http://mysite.com', 'https://mysite.com') WHERE option_name = 'home' OR option_name = 'siteurl'; UPDATE wp_posts SET guid = REPLACE (guid, 'http://mysite.com', 'https://mysite.com'); UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://mysite.com', 'https://mysite.com'); UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://mysite.com','https://mysite.com'); |
В этом случае вам не нужно добавлять лишние правила в файл .htaccess и создавать лишние пернаправления.
Закрываем комментарии для всех записей
По умолчанию WordPress предоставляет пользователям возможность комментировать любые записи, т.к. изначально CMS предназначена для создания блогов и получния фидбеков от читателей. Если же ваш сайт на основе WordPress имеет чисто информационный характер (сайт-визитка, например), то комментарии, скорей всего , вам не нужны в большинстве случаев.
Отключить возможность комментирования можно с помощью настроек WordPress (Настройки → Обсуждение), но это действие будет влиять только на те записи, которые вы добавляете после сохранения такой настройки.
На те записи, которые уже были созданы ДО отключения комментариев, отключение распространятся не будет. Поэтому их можно отключить пакетным методом, выделив все записи на сайте.
Затем вы выбираете пункт "Запретить" для комментариев и для уведомлений. Уведомления (pingbacks, или пингбеки) - это особый вид комментариев, который приходит с тех сайтов на платформе WordPress, на которых была размещена ссылка на вашу запись. Минусом является то, что такие комментарии могуи отправляться даже с вашего собственного сайте. 
Если записей очень много, легче сделать это через SQL-запрос в БД:
| 1 2 | //закрываем комментарии UPDATE wp_posts SET comment_status = 'open', ping_status = 'close' |
Если вы передумали, и вам необходимо включить комментарии, то запрос меняется на такой:
| 1 2 | //открываем комментарии UPDATE wp_posts SET comment_status = 'open', ping_status = 'open' |
После того, как вы нажали кнопку "Вперед", вы увидите, сколько строк было затронуто. Это значит, что столько записей было обновлено.
Заменяем пароль пользователя
Довольно частой является ситуация, когда вы забыли пароль от вашего сайта, причем чаще это происходит с сайтами на локальном сервере, т.к. вы могли работать какое-то время над сайтом, а затем забросить его. И в тот момент, когда вы наконец-то решили вернуться к работе над сайтом, пароль никак не вспоминается. У вас есть вариант перейти по ссылке "Забыли пароль", для того чтобы восстановить его. Нажмите на ссылку - и WordPress пришлет вам письмо со ссылкой на восстановление пароля.
В случае работы на OpenServer вы найдете письмо о восстановлении пароля в папке OSPanel/userdata/temp/email.
Если же вы не хотите восстанавливать пароль таким длинным путем, перейдите в phMyAdmin и выполните такой запрос:
| 1 | UPDATE `wp_users` SET `user_pass`= MD5('my-pass') WHERE `ID`=1 |
После этого вы без проблем должны войти в админку WordPress с новым паролем. MD5 - это функция, с помощью которой WordPress выполняет шифрование пароля перед сохранением его в базу данных и при проверке правильности ввода пароля, когда вы выполняете вход в админку.
Заменяем логин пользователя
Если заменить пароль пользователя можно с помощью функционала WordPress, то сделать то же самое с его логином получится только с помощью базы данных. Не на всех сайтах доступна регистрация пользователей, зато на каждом есть один или несколько администраторов. Поэтому будем менять в базе данных именно логин самого главного администратора, который создается в обязательном порядке при установке CMS WordPress на локальный или реальный хостинг и имеет ID=1.
| 1 2 3 4 5 | //меняем только логин UPDATE `wp_users` SET `user_login` = 'bestAdmin' WHERE `ID` = 1; //меняем логин и отображаемое на сайте имя UPDATE `wp_users` SET `user_login` = 'bestAdmin', `user_nicename` = 'bestAdmin', `display_name` = 'Админ сайта' WHERE `ID` = 1; |
В админке WordPress в таблице пользователей до и после изменений в базе данных у нас отобразятся такие логины:
Учтите, что если вы перед сменой логина выполнили вход в админку WordPress, вам придется повторить вход с новым логином и старым паролем.
