mysql - update - “INSERT IGNORE” vs “INSERTAR… EN UNA ACTUALIZACIÓN CLAVE DUPLICADA”

mysql insert multiple rows

Question

Al ejecutar una INSERT 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 que implica una actualización innecesaria a algún costo, o
• INSERT IGNORE que implica una invitación a otros tipos de fallas para que se deslicen sin avisar.

¿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 a las otras filas?

Answer 1

Algo importante que agregar: cuando se usa INSERT IGNORE y tienes violaciones clave, ¡MySQL NO genera una advertencia!

Si intenta, por ejemplo, insertar 100 registros a la vez, con uno defectuoso, obtendría el modo interactivo:

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

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

Si su script necesita ser informado, si no se han agregado algunos registros (debido a violaciones clave), debe llamar a mysql_info () y analizarlo para el valor de "Duplicados".

Answer 2

En caso de que quiera ver qué 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 primaria 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ó demasiado trabajo adicional al establecer la columna igual a sí misma, sin necesidad de actualización

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

y ahora algunas pruebas de varias filas:

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. Borre 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

Así que ahí lo tienen. Como todo esto se realizó en una tabla nueva sin casi datos y no en producción, los tiempos de ejecución fueron microscópicos e irrelevantes. Cualquier persona con datos del mundo real sería más que bienvenida a contribuir.

Answer 3

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

Esencialmente, ambos comandos son versiones de sintaxis alternativa de los comandos estándar.

Answer 4

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

Answer 5

Recomendaría el uso de INSERT...ON DUPLICATE KEY UPDATE .

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

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

Si usas REPLACE , MySQL hace un DELETE seguido de un INSERT internamente, que tiene algunos efectos secundarios inesperados:

• Se asigna un nuevo ID de incremento automático.
• Las filas dependientes con claves externas se pueden eliminar (si utiliza claves externas en cascada) o, de lo contrario, evitar la REPLACE .
• Los disparadores que disparan en DELETE se ejecutan innecesariamente.
• Los efectos secundarios se propagan a los esclavos de replicación también.

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

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 se genera la nueva ID, pero no se usa en la fila modificada.

Ver demostración a continuación, probado con Percona Server 5.5.28. La variable de configuración innodb_autoinc_lock_mode=1 (el valor predeterminado):

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 declaración IODKU detecta el duplicado e invoca la actualización para cambiar el valor de u . Tenga en cuenta que AUTO_INCREMENT=3 indica que se generó un ID, pero no se usó en la fila.

Mientras que REPLACE elimina la fila original e inserta una nueva fila, genera y almacena un nuevo id 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 |
+----+------+

Answer 6

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

Answer 7

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 en INSERT..IGNORE.

Como se mencionó anteriormente, si usa INSERT..IGNORE, los errores que se producen al ejecutar la instrucció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 ajustarán a los valores más cercanos cuando se inserten (mientras que los valores no válidos causarán que la consulta se cancele si no se usó la palabra clave IGNORE).

Answer 8

Si desea insertar en la tabla y en el conflicto de la clave principal o el índice único, se actualizará la fila en conflicto en lugar de insertar esa fila.

Sintaxis:

insert into table1 set column1 = a, column2 = b on duplicate update column2 = c;

Ahora aquí, esta declaración de inserción puede parecer diferente a lo que has visto anteriormente. Esta declaración de inserción intenta insertar una fila en la tabla 1 con el valor de a y b en la columna columna 1 y columna 2 respectivamente.

Vamos a entender esta declaración en profundidad:

Por ejemplo: aquí column1 se define como la clave principal en table1.

Ahora, si en la tabla 1 no hay una fila que tenga el valor "a" en la columna1. Así que esta declaración insertará una fila en la tabla1.

Ahora si en table1 hay una fila que tiene el valor "a" en column2. Así que esta declaración actualizará el valor de columna2 de la fila con "c", donde el valor de columna1 es "a".

Entonces, si desea insertar una nueva fila, de lo contrario actualice esa fila en el conflicto de la clave principal o el índice único.
Lea más en este enlace

Answer 9

Si se usa insert ignore tener un SHOW WARNINGS; La declaración al final de su conjunto de consultas mostrará una tabla con todas las advertencias, incluidos los ID que fueron duplicados.

Answer 10

Replace en parece ser 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 cheque IF NOT EXISTS primero.