Замена текста в MySQL через REPLACE(): одно и несколько слов

Введение

Бренд сменил название, сайт переехал на другой домен, в сотнях карточек товаров одна и та же опечатка — текст нужно поправить сразу во множестве строк. Выгружать данные в скрипт не обязательно: хватит UPDATE со строковой функцией REPLACE().

Запрос меняет множество строк разом, и без резервной копии прежние значения не вернуть. Минимальная страховка — mysqldump нужной таблицы или транзакция, о которой ниже.

Как устроена функция REPLACE()

REPLACE(str, from_str, to_str)

• str — строка, в которой ищем (обычно имя колонки);
• from_str — искомый фрагмент;
• to_str — чем его заменить.

На выходе получается копия str, где каждое вхождение from_str заменено на to_str. Стоит одному из аргументов оказаться NULL — и весь результат будет NULL. Функция лишь вычисляет новое значение — в таблицу его записывает UPDATE.

Одноимённый оператор REPLACE INTO к функции отношения не имеет. Это разновидность INSERT: он удаляет строку с совпадающим первичным ключом или уникальным индексом и вставляет на её место новую.

Замена одного слова

Допустим, в каталоге товаров слово «диск» в названиях нужно поменять на «накопитель»:

UPDATE products
SET name = REPLACE(name, 'диск', 'накопитель')
WHERE name LIKE '%диск%';

Формально WHERE здесь не обязателен: там, где слова нет, REPLACE() вернёт значение без изменений. И всё же условие лучше писать всегда:

• UPDATE затронет только строки, где слово есть. Это важно для триггеров BEFORE/AFTER UPDATE: MySQL вызывает их для каждой обработанной строки, даже если значение не изменилось;
• тем же условием удобно заранее проверить выборку через SELECT;
• большую таблицу проще обновлять порциями по диапазону ключей: WHERE id BETWEEN 1 AND 10000 AND name LIKE '%диск%' и так далее.

Частый случай — переезд на новый домен и обновление ссылок в записях:

UPDATE posts
SET content = REPLACE(content, 'http://old.ru', 'https://new.ru')
WHERE content LIKE '%http://old.ru%';

Предпросмотр и транзакция

Тот же вызов REPLACE() внутри SELECT покажет новые значения рядом со старыми, ничего не записав:

SELECT id, name, REPLACE(name, 'диск', 'накопитель') AS preview
FROM products
WHERE name LIKE '%диск%'
LIMIT 20;

Для таблиц InnoDB есть второй уровень защиты — выполнить UPDATE в транзакции, проверить строки и лишь затем зафиксировать или откатить:

START TRANSACTION;

UPDATE products
SET name = REPLACE(name, 'диск', 'накопитель')
WHERE name LIKE '%диск%';

SELECT id, name FROM products WHERE name LIKE '%накопитель%' LIMIT 20;

-- результат устраивает — фиксируем
COMMIT;
-- если нет — отменяем: ROLLBACK;

Бэкап транзакция не заменяет: пока она открыта, изменённые строки заблокированы, поэтому затягивать её не стоит, а после COMMIT пути назад нет.

Замена в нескольких полях

В SET можно перечислить несколько присваиваний и заменить слово в нескольких колонках за один проход. Условия в WHERE объединяются через OR:

UPDATE site_content
SET pagetitle   = REPLACE(pagetitle, 'диск', 'альбом'),
    longtitle   = REPLACE(longtitle, 'диск', 'альбом'),
    introtext   = REPLACE(introtext, 'диск', 'альбом'),
    content     = REPLACE(content, 'диск', 'альбом'),
    description = REPLACE(description, 'диск', 'альбом')
WHERE pagetitle LIKE '%диск%'
   OR longtitle LIKE '%диск%'
   OR introtext LIKE '%диск%'
   OR content LIKE '%диск%'
   OR description LIKE '%диск%';

Если слово нашлось, скажем, только в content, остальные колонки этой строки останутся как были: для них REPLACE() просто вернёт исходное значение.

Несколько слов одним запросом: цепочка REPLACE

Функции, принимающей список пар «что — на что», в MySQL нет. Выход — вложенные вызовы: внутренний REPLACE() отдаёт строку внешнему, тот делает свою замену.

UPDATE products
SET name = REPLACE(REPLACE(REPLACE(name,
        'диск', 'накопитель'),
        'флешка', 'USB-накопитель'),
        'Самсунг', 'Samsung')
WHERE name LIKE '%диск%'
   OR name LIKE '%флешка%'
   OR name LIKE '%Самсунг%';

Вычисление идёт от самого внутреннего вызова к внешнему, и от этого порядка зависит результат. Когда то, что вставила одна замена, совпадает с тем, что ищет следующая, текст будет заменён повторно:

-- задача: кот → кошка, кошка → собака
SELECT REPLACE(REPLACE('кот и кошка', 'кот', 'кошка'), 'кошка', 'собака');
-- собака и собака

SELECT REPLACE(REPLACE('кот и кошка', 'кошка', 'собака'), 'кот', 'кошка');
-- кошка и собака

Отсюда практическое правило: первыми ставить замены тех слов, которые могут появиться в тексте после других замен. А если два значения нужно поменять местами (A ↔ B), без промежуточного маркера не обойтись — временной строки, которой гарантированно нет в данных.

Регистр и частичные совпадения

Неожиданный результат REPLACE() обычно объясняется одной из двух особенностей.

Регистр. По документации MySQL, REPLACE() сравнивает подстроки с учётом регистра независимо от collation колонки. Значит, слово «Диск» с заглавной буквы в начале предложения не заменится. Условие LIKE при этом ведёт себя иначе: с collation utf8mb4_0900_ai_ci регистр ему безразличен, и WHERE name LIKE '%диск%' отберёт в том числе «Дискета», хотя REPLACE() в этой строке ничего не поменяет. Предпросмотр это расхождение покажет. Заглавный вариант проще обработать отдельным звеном цепочки: REPLACE(REPLACE(name, 'диск', 'накопитель'), 'Диск', 'Накопитель').

Части слов. Функция ищет подстроку, о границах слов она ничего не знает. Поэтому «дискета» превратится в «накопительета». Чтобы заменять только слово целиком, понадобится REGEXP_REPLACE() и граница слова \b в шаблоне:

UPDATE products
SET description = REGEXP_REPLACE(description, '\\bдиск\\b', 'накопитель', 1, 0, 'c')
WHERE description REGEXP '\\bдиск\\b';

Полная сигнатура — REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]]). В примере 1 означает поиск с первого символа, 0 — замену всех совпадений, а 'c' включает учёт регистра ('i' его отключает).

На что обратить внимание:

• функция появилась в MySQL 8.0.4 (работает на библиотеке ICU) и в MariaDB 10.0.5 (на PCRE);
• обратный слэш внутри строкового литерала SQL нужно удваивать: '\\bдиск\\b'. С '\bдиск\b' MySQL примет \b за управляющую последовательность, и замена молча не произойдёт;
• с регистром REGEXP_REPLACE() по умолчанию обходится не так, как REPLACE(): она следует collation. При *_ci под замену попадёт и «Диск», а на его месте окажется «накопитель» со строчной буквы. Чтобы не зависеть от collation, match_type лучше указывать явно;
• в MariaDB у функции всего три аргумента. Регистр там включают флагом в самом шаблоне — '(?-i)\\bдиск\\b', а на группы в строке замены ссылаются через \\1 (в MySQL для этого служит $1).

Работу \b с кириллицей проверили на MySQL 8.0.46, 8.4.11 и MariaDB 10.8: отдельное слово «диск» заменяется, а «дискета» не затрагивается.

Общее ограничение у обеих функций одно: для них содержимое колонки — просто строка. В HTML замена доберётся до атрибутов и адресов ссылок, а в JSON-колонке способна сделать документ невалидным. Для таких полей предпросмотр особенно важен.

Замена из Laravel

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

use Illuminate\Support\Facades\DB;

$old = 'диск';
$new = 'накопитель';

// экранируем % и _ для LIKE
$like = '%' . addcslashes($old, '%_\\') . '%';

$affected = DB::update(
    'UPDATE products SET name = REPLACE(name, ?, ?) WHERE name LIKE ?',
    [$old, $new, $like],
);

Склеивать запрос из строк с $old и $new нельзя: так открывается дорога SQL-инъекции. Query builder тоже умеет делать такую замену, однако DB::raw() внутри update() не связывает плейсхолдеры. Поэтому этот вариант подходит лишь для фиксированных литералов, но не для данных от пользователя:

// только константы, без пользовательского ввода
DB::table('products')
    ->where('name', 'like', '%диск%')
    ->update(['name' => DB::raw("REPLACE(name, 'диск', 'накопитель')")]);

Оба способа идут в базу мимо Eloquent. Если на изменение модели завязаны события и обсерверы, сброс кэша или переиндексация Scout, ничего из этого не выполнится. Тогда записи обходят пакетами через chunkById() и правят текст в PHP — медленнее, зато логика приложения отработает как при обычном сохранении:

use App\Models\Product;

Product::query()
    ->where('name', 'like', '%диск%')
    ->chunkById(200, function ($products) {
        foreach ($products as $product) {
            $product->name = str_replace('диск', 'накопитель', $product->name);
            $product->save(); // сработают события модели
        }
    });

Разовую замену удобно оформить миграцией данных: она останется в истории проекта и одинаково выполнится на всех окружениях.

Итог

Безопасная массовая замена текста в MySQL укладывается в четыре шага:

1. Сделать бэкап таблицы.
2. Посмотреть результат заранее: SELECT … REPLACE(…) AS preview … LIMIT 20.
3. Открыть транзакцию (InnoDB) и выполнить UPDATE с условием WHERE: несколько слов — цепочкой REPLACE с продуманным порядком, целые слова — через REGEXP_REPLACE().
4. Выборочно проверить изменённые строки и только потом сделать COMMIT (или ROLLBACK).

Похожие

• Как отключить проверку внешних ключей в MySQL и Laravel
• Подключение к Yandex Cloud MySQL на Laravel
• Все шпаргалки по MySQL