Pregunta "INSERT IGNORE" versus "INSERT ... EN ACTUALIZACIÓN DE LLAVE DUPLICADA"


Mientras se ejecuta una INSERT declaración con muchas filas, quiero omitir las entradas duplicadas que de lo contrario causarían un error. Después de algunas investigaciones, mis opciones parecen ser el uso de:

  • ON DUPLICATE KEY UPDATE lo que implica una actualización innecesaria a algún costo, o
  • INSERT IGNORE lo que implica una invitación a otros tipos de fallas para entrar sin previo aviso.

¿Estoy en lo cierto en estas suposiciones? ¿Cuál es la mejor manera de simplemente omitir las filas que podrían causar duplicados y simplemente continuar en las otras filas?


735
2018-02-14 05:24


origen


Respuestas:


Yo recomendaría usar INSERT...ON DUPLICATE KEY UPDATE.

Si utiliza INSERT IGNORE, entonces la fila no se insertará realmente si resulta en una clave duplicada. Pero la declaración no generará un error. Genera una advertencia en su lugar. Estos casos incluyen:

  • Insertar una clave duplicada en columnas con PRIMARY KEY o UNIQUE restricciones
  • Insertar un NULL en una columna con un NOT NULL restricción.
  • Insertar una fila en una tabla particionada, pero los valores que inserta no se asignan a una partición.

Si utiliza REPLACE, MySQL realmente hace un DELETE seguido por un INSERT internamente, que tiene algunos efectos secundarios inesperados:

  • Se asigna una nueva identificación de auto incremento.
  • Las filas dependientes con claves externas pueden eliminarse (si usa cascadas de claves externas) o evitar el REPLACE.
  • Disparadores que disparan DELETE se ejecutan innecesariamente.
  • Los efectos secundarios también se propagan a los esclavos de replicación.

corrección: ambos REPLACE y INSERT...ON DUPLICATE KEY UPDATE son invenciones patentadas no estándar específicas de MySQL. ANSI SQL 2003 define un MERGE declaración que puede resolver la misma necesidad (y más), pero MySQL no admite la MERGE declaración.


Un usuario intentó editar esta publicación (la edición fue rechazada por los moderadores). La edición intentó agregar un reclamo que INSERT...ON DUPLICATE KEY UPDATE hace que se asigne un nuevo ID de incremento automático. Es cierto que la nueva identificación es generado, pero no se usa en la fila modificada.

Consulte la demostración a continuación, probada con Percona Server 5.5.28. La variable de configuración innodb_autoinc_lock_mode=1 (el valor por defecto):

mysql> create table foo (id serial primary key, u int, unique key (u));
mysql> insert into foo (u) values (10);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   10 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

mysql> insert into foo (u) values (10) on duplicate key update u = 20;
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

Lo anterior demuestra que la instrucción IODKU detecta el duplicado e invoca la actualización para cambiar el valor de u. Nota la AUTO_INCREMENT=3 indica que se generó una identificación, pero no se usó en la fila.

Mientras REPLACE elimina la fila original e inserta una nueva fila, generando y almacenar una nueva identificación de incremento automático:

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  3 |   20 |
+----+------+

896
2018-02-14 05:51



En caso de que quiera ver lo que significa todo esto, aquí hay un golpe por golpe de todo:

CREATE TABLE `users_partners` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `pid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`,`pid`),
  KEY `partner_user` (`pid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

La clave principal se basa en ambas columnas de esta tabla de referencia rápida. Una clave principal requiere valores únicos.

Vamos a empezar:

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...1 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1);
...0 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1) ON DUPLICATE KEY UPDATE uid=uid
...0 row(s) affected

tenga en cuenta que lo anterior ahorró mucho trabajo adicional al establecer la columna igual a sí misma, no se necesita actualizar realmente

REPLACE INTO users_partners (uid,pid) VALUES (1,1)
...2 row(s) affected

y ahora algunas pruebas de filas múltiples:

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...3 row(s) affected

no se generaron otros mensajes en la consola, y ahora tiene esos 4 valores en los datos de la tabla. Eliminé todo excepto (1,1) para poder probar desde el mismo campo de juego

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4) ON DUPLICATE KEY UPDATE uid=uid
...3 row(s) affected

REPLACE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...5 row(s) affected

Entonces ahí lo tienes. Como todo esto se realizó en una mesa nueva con casi ningún dato y sin producción, los tiempos de ejecución fueron microscópicos e irrelevantes. Cualquiera con datos del mundo real sería más que bienvenido a contribuir.


156
2017-10-21 18:23



Algo importante que agregar: ¡Cuando utilizas INSERT IGNORE y tienes violaciones de claves, MySQL NO genera una advertencia!

Si intentas, por ejemplo, insertar 100 registros a la vez, con uno defectuoso, obtendrás el modo interactivo:

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

Como ves: ¡Sin advertencias! Este comportamiento incluso se describe erróneamente en la documentación oficial de Mysql.

Si su secuencia de comandos necesita ser informada, si algunos registros no se han agregado (debido a violaciones de claves), debe llamar a mysql_info () y analizar el valor de "Duplicados".


37
2018-04-21 10:04



Sé que esto es antiguo, pero agregaré esta nota en caso de que alguien más (como yo) llegue a esta página al intentar encontrar información sobre INSERT..IGNORE.

Como se mencionó anteriormente, si usa INSERT..IGNORE, los errores que ocurren mientras se ejecuta la declaración INSERT se tratan como advertencias.

Una cosa que no se menciona explícitamente es que INSERT..IGNORE causará que los valores no válidos se ajusten a los valores más cercanos cuando se inserten (mientras que los valores no válidos harían que la consulta abortara si no se usó la palabra clave IGNORE).


16
2017-09-16 14:48



Uso rutinariamente INSERT IGNORE, y suena exactamente el tipo de comportamiento que estás buscando también. Siempre que sepa que las filas que causarían conflictos de índice no se insertarán y planifica su programa en consecuencia, no debería causar ningún problema.


14
2018-02-14 05:53



ON DUPLICATE KEY UPDATE no es De Verdad en el estándar. Es casi tan estándar como REPLACE. Ver SQL MERGE.

Básicamente, ambos comandos son versiones de sintaxis alternativas de comandos estándar.


7
2018-02-14 05:57



Replace En parece una opción. O puede consultar con

IF NOT EXISTS(QUERY) Then INSERT

Esto insertará o eliminará y luego insertará. Tiendo a ir por un IF NOT EXISTS verifica primero


5
2018-02-14 05:34



Peligro potencial de INSERT IGNORE. Si está intentando insertar el valor VARCHAR más tiempo, la columna se definió con - el valor se truncará e insertará INCLUSO SI el modo estricto está habilitado.


2
2017-10-19 23:33