REPLACE

Синтаксис

REPLACE [LOW_PRIORITY | DELAYED]
 [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
 {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[RETURNING select_expr 
      [, select_expr ...]]

Или:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [PARTITION (partition_list)]
    SET col={expr | DEFAULT}, ...
[RETURNING select_expr 
      [, select_expr ...]]

Или:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
    SELECT ...
[RETURNING select_expr 
      [, select_expr ...]]

Описание

REPLACE работает точно так же, как INSERT, за исключением того, что если старая строка в таблице имеет такое же значение, как новая строка для PRIMARY KEY или UNIQUE индекса, старая строка удаляется перед вставкой новой. Если таблица имеет более одного UNIQUE ключа, возможно, что новая строка конфликтует с более чем одной строкой. В этом случае все конфликтующие строки будут удалены.

Имя таблицы можно указать в форме db_name.tbl_name или, если выбрана база данных по умолчанию, в форме tbl_name (см. Квалификаторы идентификаторов). Это позволяет использовать REPLACE ... SELECT для копирования строк между различными базами данных.

В основном, он работает так:

BEGIN;
SELECT 1 FROM t1 WHERE key=# FOR UPDATE;
IF found-row
  DELETE FROM t1 WHERE key=# ;
ENDIF
INSERT INTO t1 VALUES (...);
END;

Вышесказанное можно заменить на:

REPLACE INTO t1 VALUES (...)

REPLACE является расширением MariaDB/MySQL стандарта SQL. Оно либо вставляет, либо удаляет и вставляет. Для других расширений MariaDB/MySQL стандарта SQL, которые также обрабатывают дублирующие значения, см. IGNORE и INSERT ON DUPLICATE KEY UPDATE.

Обратите внимание, что если таблица не имеет PRIMARY KEY или UNIQUE индекса, использование оператора REPLACE не имеет смысла. Он становится эквивалентным INSERT, поскольку нет индекса, который можно использовать для определения дублирования новой строки.

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

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

Существует несколько нюансов, о которых следует знать перед использованием REPLACE:

• Если есть поле AUTO_INCREMENT, будет сгенерировано новое значение.
• Если есть внешние ключи, ON DELETE действие будет активировано REPLACE.
• Триггеры на DELETE и INSERT будут активированы REPLACE.

Чтобы избежать некоторых из этих поведений, вы можете использовать INSERT ... ON DUPLICATE KEY UPDATE.

Этот оператор активирует триггеры INSERT и DELETE. Подробности см. в Обзоре триггеров.

РАЗДЕЛЕНИЕ

Подробности см. в разделе Оптимизация и выбор разделов.

REPLACE RETURNING

Примеры

Простой оператор REPLACE

REPLACE INTO t2 VALUES (1,'Leopard'),(2,'Dog') RETURNING id2, id2+id2 
as Total ,id2|id2, id2&&id2;
+-----+-------+---------+----------+
| id2 | Total | id2|id2 | id2&&id2 |
+-----+-------+---------+----------+
|   1 |     2 |       1 |        1 |
|   2 |     4 |       2 |        1 |
+-----+-------+---------+----------+

Использование хранимых функций в RETURNING

DELIMITER |
CREATE FUNCTION f(arg INT) RETURNS INT
    BEGIN
      RETURN (SELECT arg+arg);
    END|

DELIMITER ;
PREPARE stmt FROM "REPLACE INTO t2 SET id2=3, animal2='Fox' RETURNING f2(id2),
UPPER(animal2)";

EXECUTE stmt;
+---------+----------------+
| f2(id2) | UPPER(animal2) |
+---------+----------------+
|       6 | FOX            |
+---------+----------------+

Подзапросы в операторе

REPLACE INTO t1 SELECT * FROM t2 RETURNING (SELECT id2 FROM t2 WHERE 
id2 IN (SELECT id2 FROM t2 WHERE id2=1)) AS new_id;
+--------+
| new_id |
+--------+
|      1 |
|      1 |
|      1 |
|      1 |
+--------+

Подзапросы в операторе RETURNING, возвращающие более одной строки или столбца, использовать нельзя.

Агрегатные функции нельзя использовать в операторе RETURNING. Поскольку агрегатные функции работают с набором значений, и если целью является получение количества строк, можно использовать ROW_COUNT() с SELECT, или его можно использовать в REPLACE...SELECT...RETURNING, если таблица в операторе RETURNING не совпадает с таблицей REPLACE.

Примеры

Простой оператор REPLACE

REPLACE INTO t2 VALUES (1,'Leopard'),(2,'Dog') RETURNING id2, id2+id2 
as Total ,id2|id2, id2&&id2;
+-----+-------+---------+----------+
| id2 | Total | id2|id2 | id2&&id2 |
+-----+-------+---------+----------+
|   1 |     2 |       1 |        1 |
|   2 |     4 |       2 |        1 |
+-----+-------+---------+----------+

Использование хранимых функций в RETURNING

DELIMITER |
CREATE FUNCTION f(arg INT) RETURNS INT
    BEGIN
      RETURN (SELECT arg+arg);
    END|

DELIMITER ;
PREPARE stmt FROM "REPLACE INTO t2 SET id2=3, animal2='Fox' RETURNING f2(id2),
UPPER(animal2)";

EXECUTE stmt;
+---------+----------------+
| f2(id2) | UPPER(animal2) |
+---------+----------------+
|       6 | FOX            |
+---------+----------------+

Подзапросы в операторе

REPLACE INTO t1 SELECT * FROM t2 RETURNING (SELECT id2 FROM t2 WHERE 
id2 IN (SELECT id2 FROM t2 WHERE id2=1)) AS new_id;
+--------+
| new_id |
+--------+
|      1 |
|      1 |
|      1 |
|      1 |
+--------+

Подзапросы в операторе RETURNING, возвращающие более одной строки или столбца, использовать нельзя.

Агрегатные функции нельзя использовать в операторе RETURNING. Поскольку агрегатные функции работают с набором значений, и если целью является получение количества строк, можно использовать ROW_COUNT() с SELECT, или его можно использовать в REPLACE...SELECT...RETURNING, если таблица в операторе RETURNING не совпадает с таблицей REPLACE.

См. также

• INSERT
• Операторы HIGH_PRIORITY и LOW_PRIORITY
• INSERT DELAYED для получения информации об операторе DELAYED