REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ...
      |
      VALUES row_constructor_list
    }

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {SELECT ... | TABLE table_name}

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

REPLACE работает точно так же, как INSERT, за исключением того, что если у старой строки в таблице есть такое же значение, как у новой строки для PRIMARY KEY или индекса UNIQUE, старая строка удаляется перед вставкой новой. См. Раздел 15.2.7, «Оператор INSERT».

REPLACE — это расширение MySQL стандарта SQL. Оно либо вставляет, либо удаляет и вставляет. Для другого расширения MySQL стандарта SQL — которое либо вставляет, либо обновляет — см. Раздел 15.2.7.2, «Оператор INSERT ... ON DUPLICATE KEY UPDATE».

DELAYED вставка и замена были устаревшими в MySQL 5.6. В MySQL 8.4, DELAYED не поддерживается. Сервер распознает, но игнорирует ключевое слово DELAYED, обрабатывает замену как незадержанную замену и генерирует предупреждение: REPLACE DELAYED больше не поддерживается. Оператор был преобразован в REPLACE. Ключевое слово DELAYED запланировано на удаление в будущей версии.

Значения для всех столбцов берутся из значений, указанных в операторе REPLACE. Любые отсутствующие столбцы устанавливаются в их значения по умолчанию, как и в случае с INSERT. Вы не можете ссылаться на значения из текущей строки и использовать их в новой строке. Если вы используете такое присваивание, как SET col_name = col_name + 1, ссылка на имя столбца в правой части рассматривается как DEFAULT(col_name), так что присваивание эквивалентно SET col_name = DEFAULT(col_name) + 1.

Вы можете указать значения столбцов, которые REPLACE пытается вставить, используя VALUES ROW().

Для использования REPLACE, у вас должны быть права INSERT и DELETE для таблицы.

Если сгенерированный столбец заменяется явно, единственное разрешенное значение — DEFAULT. Сведения о сгенерированных столбцах см. в Разделе 15.1.20.8, «CREATE TABLE и сгенерированные столбцы».

REPLACE поддерживает явное выделение раздела с помощью условия PARTITION со списком разделенных запятыми имен разделов, подразделов или обоих. Как и в случае с INSERT, если невозможно вставить новую строку в любой из этих разделов или подразделов, оператор REPLACE завершается с ошибкой Найдена строка, не соответствующая заданному набору разделов. Дополнительную информацию и примеры см. в Разделе 26.5, «Выделение раздела».

Оператор REPLACE возвращает счет, указывающий количество затронутых строк. Это сумма строк, удаленных и вставленных. Если счет равен 1 для однострочного оператора REPLACE, строка была вставлена, и строки не были удалены. Если счет больше 1, одна или несколько старых строк были удалены перед вставкой новой строки. Возможна ситуация, когда одна строка заменяет более одной старой строки, если таблица содержит несколько уникальных индексов, и новая строка дублирует значения для различных старых строк в разных уникальных индексах.

Счет затронутых строк упрощает определение, только добавила ли REPLACE строку или также заменила какие-либо строки: Проверьте, равен ли счет 1 (добавлена) или больше (заменена).

Если вы используете API C, счет затронутых строк можно получить с помощью функции.

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

MySQL использует следующий алгоритм для REPLACE (и LOAD DATA ... REPLACE):

Возможно, в случае ошибки дублирования ключа движок хранилища может выполнить REPLACE как обновление, а не удаление плюс вставка, но семантика остается той же. Нет видимых для пользователя эффектов, кроме, возможно, различий в том, как движок хранилища увеличивает значения переменных состояния Handler_xxx.

Поскольку результаты операторов REPLACE ... SELECT зависят от порядка строк из SELECT, и этот порядок не всегда может быть гарантирован, при протоколировании этих операторов для источника и реплики возможны расхождения. По этой причине операторы REPLACE ... SELECT помечены как небезопасные для репликации на основе операторов. Такие операторы генерируют предупреждение в журнале ошибок при использовании режима репликации на основе операторов и записываются в двоичный журнал с использованием формата на основе строк при использовании режима MIXED. См. также Раздел 19.2.1.1, «Преимущества и недостатки репликации на основе операторов и строк».

MySQL 8.4 поддерживает TABLE, а также SELECT с REPLACE, так же как и с INSERT. См. Раздел 15.2.7.1, «Оператор INSERT ... SELECT», для получения дополнительной информации и примеров.

При изменении существующей неразделенной таблицы для поддержки разбиения или при изменении разбиения уже разбиеной таблицы можно рассмотреть возможность изменения первичного ключа таблицы (см. Раздел 26.6.1, «Ключи разбиения, первичные ключи и уникальные ключи»). Следует учитывать, что, если вы это сделаете, результаты операторов REPLACE могут быть затронуты, точно так же, как и в случае изменения первичного ключа неразбитой таблицы. Рассмотрим таблицу, созданную следующим оператором CREATE TABLE:

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

При создании этой таблицы и выполнении операторов в mysql-клиенте результат будет следующим:

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

Теперь мы создаем вторую таблицу, почти идентичную первой, за исключением того, что первичный ключ теперь охватывает 2 столбца, как показано здесь (выделенный текст):

CREATE TABLE test2 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id, ts)
);

При запуске на test2 тех же двух операторов REPLACE, что и на исходной test таблице, мы получаем другой результат:

mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.05 sec)

mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM test2;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Old  | 2014-08-20 18:47:00 |
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
2 rows in set (0.00 sec)

Это связано с тем, что при запуске на test2, значения и столбца id, и ts должны совпадать со значениями существующей строки, чтобы строка была заменена; в противном случае строка вставляется.