MySQL: insert x insert ignore x replace

Alguns detalhes que muitas vezes são ignorados podem facilitar muito a vida no MySQL.


INSERT

Talvez o INSERT seja o segundo comando SQL mais conhecido, perdendo apenas para o SELECT. Ele insere novos registros em uma tabela existente. Caso o INSERT esteja tentando inserir um registro que tenha a mesma chave primária (primary key) ou índice único (unique index) de outro já existente na tabela, ocorre um erro chamado erro de chave duplicada (duplicate-key error) e a execução é abortada.

Se esse erro ocorrer no meio de um script SQL, a execução do script é abortada.


REPLACE

Funciona exatamente como o INSERT, porém se o registro que está sendo inserido tem a mesma chave primária (primary key) ou índice único (unique index) de outro já existente na tabela, esse registro já existente é apagado antes do novo ser inserido. E não ocorre erro.

INSERT IGNORE

A cláusula IGNORE altera um pouco o comportamento do INSERT:

Se o registro que está sendo inserido tem a mesma chave primária (primary key) ou índice único (unique index) de outro já existente na tabela, o INSERT IGNORE é ignorado e não ocorre erro.


Tentativas de inserir registros com valores inválidos não causam erros. Os valores serão ajustados para valores mais aceitáveis e inseridos. Warnings são produzidos, mas a execução não é abortada.

Resumindo

DELETE FROM `table`;

INSERT INTO `table` (`id`, `name`) VALUES (1, "Adriana");
INSERT INTO `table` (`id`, `name`) VALUES (1, "Pedro");
INSERT INTO `table` (`id`, `name`) VALUES (2, "Rafael");

O primeiro INSERT será executado, o segundo dará erro e a execução será abortada (pois já foi inserido um registro com id = 1), o terceiro nem será executado.

Resultado:

+----+---------+
| id | name    |
+----+---------+
|  1 | Adriana |
+----+---------+

DELETE FROM `table`;
REPLACE INTO `table` (`id`, `name`) VALUES (1, "Adriana");
REPLACE INTO `table` (`id`, `name`) VALUES (1, "Pedro");
REPLACE INTO `table` (`id`, `name`) VALUES (2, "Rafael");

O primeiro REPLACE será executado inserindo normalmente o registro como o INSERT faria. O segundo apagará o registro com id = 1 e adicionará outro registro com id = 1 sem erros. O terceiro REPLACE também será executado normalmente.

Resultado:

+----+--------+
| id | name   |
+----+--------+
|  1 | Pedro  |
|  2 | Rafael |
+----+--------+

DELETE FROM `table`;
INSERT IGNORE INTO `table` (`id`, `name`) VALUES (1, "Adriana");
INSERT IGNORE INTO `table` (`id`, `name`) VALUES (1, "Pedro");
INSERT IGNORE INTO `table` (`id`, `name`) VALUES (2, "Rafael");

O primeiro INSERT IGNORE vai inserir o registro exatamente como o INSERT sozinho faria. O segundo vai tentar inserir outro registro, mas como a chave está duplicada, será ignorado sem erros. O terceiro INSERT IGNORE também será executado normalmente.

Resultado:

+----+---------+
| id | name    |
+----+---------+
|  1 | Adriana |
|  2 | Rafael  |
+----+---------+