Массовое дублирование записей с заменой подстроки в SQL: Использование INSERT SELECT REPLACE

В задачах администрирования баз данных, связанных с миграцией или тестированием конфигураций (например, в системах на базе OpenCart, использующих таблицу oc_setting), часто требуется не просто обновить существующие записи, а создать их дубликаты с измененными идентификаторами или префиксами.

Цель данной процедуры — создание новых записей, являющихся точной копией существующих, но с заменой определенной подстроки (например, версии модуля) в ключевых полях.

Методология: SQL INSERT INTO с функцией REPLACE

Для достижения этой цели используется комбинация команд INSERT INTO ... SELECT с функцией REPLACE(), что позволяет извлекать данные и модифицировать их в одном запросе перед вставкой в целевую таблицу.

Пример: Копирование настроек из oc_setting

Рассмотрим сценарий копирования настроек, содержащих префикс 'singleclick6' в таблице oc_setting, с заменой этого префикса на 'singleclick9' в новых записях.

При условии, что столбец id настроен как AUTO_INCREMENT, запрос на вставку будет выглядеть следующим образом:

INSERT INTO oc_setting (`store_id`,   `code`, `key`, `value`, `serialized`)
SELECT
    store_id,
    REPLACE(code, 'singleclick6', 'singleclick9'),
    REPLACE(`key`, 'singleclick6', 'singleclick9'),
    REPLACE(value, 'singleclick6', 'singleclick9'),
    serialized
FROM oc_setting
WHERE code LIKE '%singleclick6%' OR `key` LIKE '%singleclick6%' OR value LIKE '%singleclick6%';
    

Детали реализации:

• `INSERT INTO oc_setting (...)`: Указывает целевую таблицу и перечисляет столбцы для вставки. Столбцы, не участвующие в модификации (например, store_id, serialized), копируются напрямую.
• `REPLACE(column, 'old_value', 'new_value')`: Эта функция применяется к столбцам code, key и value. Она гарантирует, что вставляемые значения будут содержать замену 'singleclick6' на 'singleclick9'.
• `WHERE` условие: Фильтрация данных гарантирует, что операция затрагивает только те исходные записи, которые содержат искомый префикс, минимизируя время выполнения и риск побочных эффектов.

Критические замечания по безопасности и выполнению

1. Резервное копирование: Выполнение любых операций INSERT или UPDATE без предварительной резервной копии базы данных категорически не рекомендуется.
2. Обработка первичного ключа (ID): Если столбец id не является AUTO_INCREMENT, необходимо модифицировать SELECT часть, чтобы генерировать уникальные идентификаторы для новых записей, чтобы избежать конфликтов.
3. Производительность: Для очень больших таблиц рекомендуется выполнить SELECT часть запроса отдельно, чтобы убедиться в корректности выборки, прежде чем выполнять операцию INSERT.