Замена, удаление части строки в базе MySQL.

Не всё удобно делать средствами PHP, а особенно, если дело касается базы. Если нам нужно удалить⁄заменить часть строки или одно слово в нескольких записях, то с использованием PHP, нам бы пришлось сначала вытаскивать каждую запись, изменять её и обновлять в БД. Не очень удобно... В некоторых случаях, всё это можно сделать одним запросом MySQL. Давайте посмотрим, как это можно сделать.
Для примера, возьмём таблицу "verses" (стихи), в которой есть записи с допущенной ошибкой в написании автора:

CREATE TABLE IF NOT EXISTS `verses` (
  `verse_id` int(11) NOT NULL AUTO_INCREMENT,
  `verse_content` text NOT NULL,
  PRIMARY KEY (`verse_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `verses` (`verse_content`) VALUES
  ('Я к вам пишу – чего же боле? Что я могу еще сказать? А.С. Пупкин'),
  ('А.С. Пупкин. У лукоморья дуб зелёный; Златая цепь на дубе том');

Теперь попробуем заменить неправильную фамилию "Пупкин" на "Пушкин". Самый простой вариант - используем функцию REPLACE() (! не путать с оператором REPLACE), которая позволит заменить все искомые вхождения (проще говоря - слова, фразы) в строке, на строку замены (нужное нам слово, фразу, символ и т.д.)

UPDATE 
  `verses`
SET 
  `verse_content` = REPLACE(`verse_content` , 'Пупкин', 'Пушкин');

Для тестирования можно просто выводить результат с заменой.

SELECT 
  REPLACE(`verse_content`, 'Пупкин', 'Пушкин') AS `correct_string`
FROM 
  `verses`;

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

SELECT 
  REPLACE('Здравствуйте, [name]! Запрос получен. Рад, [name], что вы с нами.', '[name]', 'Игорь') AS `correct_string`
FROM 
  `verses`;
// результат: "Здравствуйте, Игорь! Запрос получен. Рад, Игорь, что вы с нами."

То есть, в шаблоне мы сделали метку "[name]" и в дальнейшем подставляем вместо неё реальное имя. Метка может быть любой, только следите, чтоб она не совпадала с реальным словом или фразой, которое так же может встретиться в исходной строке.

Всё это хорошо, если заменить нужно только само слово или фразу, которая нам известна. Но что делать, если мы знаем с какого момента нужно начать замену и где закончить, а что может находится между - не известно? Возьмём реальный случай из моей практики. У человека был сайт, где использовался старый подход - в статьях выводились миниатюрки изображений, а по клику на них, открывалось новое окно с увеличенным изображением. Прогресс движется вперёд и он решил использовать знакомый многим плагин "fancybox". Задача состояла в том, чтоб удалить не нужную часть "onclick" и добавить атрибут "rel", который используется в плагине для инициализации изображений. Ниже показано, что примерно было и что должно получится в итоге:

// исходная строка
<a href="#" onclick="window.open('/path/my_img.png','','width=350,height=200,left=200,top=200');return false;">
  <img src="/path/my_img.png" alt="" ></a>

// желаемый результат
<a href="#" rel="group_img">
  <img src="/path/my_img.png" alt="" ></a>

Если в записи необходимая замена нужна только один раз, то можно идти по такой схеме: получить подстроку до первого вхождения слова "onclick" с помощью функции SUBSTRING_INDEX(), потом вычисляем закрывающую угловую скобку тега (">"), идущую первой после "onclick", чтоб получить подстроку после неё, и конкатенируем (объединяем) полученные части строк, добавляя между ними необходимый атрибут.
Будем использовать ту же таблицу `verses` и, если вы добавите туда исходную строку, то учитываем, что её индекс будет равен "3":

UPDATE
  `verses`
SET
  `verse_content` = CONCAT(
    SUBSTRING_INDEX(`verse_content`,'onclick', 1),
    'rel="group_img"',
    SUBSTRING(`verse_content`, LOCATE('>',`verse_content`, LOCATE('onclick',`verse_content`)))
  )
WHERE
  `verse_id` = 3;

Для простых задач, не требующих "танцев с бубном", такой способ вполне приемлем и работать он будет, как я уже говорил, только с первыми найденными вхождениями. То есть, если мы добавим в запись еще одну такую же строку, то первая будет изменена, а вторая не тронута. Для более сложных операций, нужно искать другие подходы. Например, для замены мы не можем использовать функцию REAPLACE() с функцией REGEXP(), которая бы находила по шаблону всё, что нужно заменить в строке, но на такой случай, существуют UDF (Пользовательские функции) и одной из которых можно было бы применить в данном случае - есть MySQL UDF REGEXP, Regex replace for MySQL или PREG functions for MySQL.
Обратил внимание, что нигде не обмолвился про удаление, но уверен, что у вас и так хватило бы смекалки, что вместо фразы на которую мы меняли в примерах выше, нужно просто указывать пустую строку ;)


Задачи с заменами или удалением части строк средствами MySQL не частые. Скорее они носят одноразовый характер и сомневаюсь, что их регулярное использование оправдано. Я сторонник того, чтоб в БД попадали уже полностью подготовленные данные, над которыми при выводе не нужно ломать голову. Но все мы знаем, что случаи бывают разные (лично сталкивался) и, если с таким столкнётесь, то уже не растеряетесь.

Incode Pro logo

5 комментариев

Гость 31.05.2016 11:58
Спасибо огромное. Не знал про SUBSTRING_INDEX, как раз подошёл, причём в сочетании с CONCAT.
Хоть задача была другая, но суть та же. Надо было отсеять "лишнее" в таблице.
Думал про регулярки, но ставить дополнительные расширения было бы геморно ради 130 записей (а это нагружать сисадмина). Как и писать php-код ради этой цели, коммитить в git а затем деплоить в продакшен.
Руками править тоже можно где-то накосячить.
А это самое оно. И соглашусь, лишь в некоторых редких случаях, что-то один раз исправить.
В общем в статье чётко описано, что это не для постоянного использования и подойдёт не для всех случаев.

Перед выполнением UPDATE также сделал SELECT (как в примере с 'Пупкин').
И продублировал нужное поле, чтобы видеть результат до и после.
И не выполняйте запрос сразу в продакшене. Протестируйте сначала на девелоперской базе. Хотя это очевидно вроде.
samgoma 21.04.2017 15:21
Спасибо Вам - даже рекламу включил и кликнул ) помогли - правда частично решил проблему, но все же спасибо.
Скажите а если на многих страницах ещё вредный код:
<!--цифры и буквы разные--><a style="display: none;" title="гадость">гадость</a><!--/цифры и буквы разные--><!--цифры и буквы разные-->
<div style="display: none;"><a href="гадость">гадость</a></div>
<!--/цифры и буквы разные-->

и бывает несколько каких блоков бывает один - как то можно удалить эту всю гадость оптом?
Incode 21.04.2017 16:42
@samgoma, любой бы мой коллега, начал бы вас отчитывать в плане того, что "лечить" нужно не следствие, а причину. И был бы прав, т.к. записывать в базу необработанные и не проверенные данные, приходящие от клиента, это в нашем деле сродни преступлению.
Но раз уж так произошло, то нужно искать отличительные признаки и закономерности такого кода. Если закономерности найдены не будут, то придётся чистить в несколько проходов, удаляя отдельные элементы этого кода.
Есть и другой вариант - PHP-класс HTML Purifier. Обычно им пользуются для обработки данных, содержащих HTML-код, перед записью, но можно попробовать направить его на решение вашей проблемы. То есть, вам нужно нужно написать сценарий, который будет доставать запись из БД, прогонять её через Purifier и записывать обратно. В настройках этого класса, как вы понимаете, нужно будет указать допустимые теги, для того, чтобы отфильтровать всякую каку ))
samgoma 05.05.2017 14:23
@Incode, Причину то я удалил :) а вот остатки остались. Сколько не искал о переменных так и не нашёл. Вот плохо, что нельзя в базе использовать значения типа (.?*) - если не ошибаюсь регулярные выражения называются - тогда можно было бы все ссылки снести форматом <a href=(.?*)</a> . Если всё же есть - напишите - последователям будет полезно :)
sash 13.07.2017 17:16
Тоже у меня возникли проблемы с неправильными данными в базе.. Но я пошел легчайшею дорогой и просто изменяю регулярками выбранное перед вставкой на страницу. Без обновления самих записей, решил уже лучше не трогать, пусть живёт как память о славных деньках :)
Ваш комментарий:
X