REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_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 ...

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

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

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

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

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

Чтобы использовать REPLACE, необходимо иметь как привилегии INSERT, так и DELETE для таблицы.

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

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

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

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

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

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

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

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

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

При модификации существующей неразбитой таблицы для поддержки разбиения или при изменении разбиения уже разбиеной таблицы можно рассмотреть возможность изменения первичного ключа таблицы (см. Раздел 22.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 должны совпадать со значениями существующей строки для замены строки; в противном случае строка вставляется.

Оператор REPLACE, влияющий на разбиение таблицы с помощью движка хранения, такого как MyISAM, использующего блокировки на уровне таблицы, блокирует только те партиции, содержащие строки, соответствующие оператору REPLACE, предложению WHERE, если ни один из столбцов партиционирования не обновляется; в противном случае блокируется вся таблица. (Для движков хранения, таких как InnoDB, использующих блокировки на уровне строк, блокировка партиций не происходит.) Более подробную информацию см. в Разделе 22.6.4, «Разбиение и блокировка».