"INSERTAR IGNORAR" frente a "INSERTAR... EN ACTUALIZACIÓN DE CLAVE DUPLICADA"
Al ejecutar una INSERTdeclaración con muchas filas, quiero omitir entradas duplicadas que de otro modo causarían fallas. Después de investigar un poco, mis opciones parecen ser el uso de:
ON DUPLICATE KEY UPDATElo que implica una actualización innecesaria a algún costo, oINSERT IGNOREimplica una invitación a que otros tipos de fracasos se introduzcan 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 continuar con las otras filas?
Recomendaría usar INSERT...ON DUPLICATE KEY UPDATE.
Si usa INSERT IGNORE, la fila en realidad no se insertará si da como resultado una clave duplicada. Pero la declaración no generará un error. En su lugar, genera una advertencia. Estos casos incluyen:
- Insertar una clave duplicada en columnas con restricciones
PRIMARY KEYoUNIQUE. - Insertar un NULL en una columna con una
NOT NULLrestricción. - Insertar una fila en una tabla particionada, pero los valores que inserta no se asignan a una partición.
Si usa REPLACE, MySQL en realidad hace un DELETEseguido de uno INSERTinternamente, lo que tiene algunos efectos secundarios inesperados:
- Se asigna una nueva ID de incremento automático.
- Las filas dependientes con claves externas se pueden eliminar (si usa claves externas en cascada) o evitar el archivo
REPLACE. - Los disparadores que se activan
DELETEse ejecutan innecesariamente. - Los efectos secundarios también se propagan a las réplicas.
Corrección: ambos REPLACEy INSERT...ON DUPLICATE KEY UPDATEson inventos propietarios no estándar específicos de MySQL. ANSI SQL 2003 define una MERGEdeclaración que puede resolver la misma necesidad (y más), pero MySQL no admite la MERGEdeclaración.
Un usuario intentó editar esta publicación (los moderadores rechazaron la edición). La edición intentó agregar un reclamo que INSERT...ON DUPLICATE KEY UPDATEhace que se asigne una nueva identificación de incremento automático. Es cierto que se genera la nueva identificación , pero no se usa en la fila modificada.
Vea la demostración a continuación, probada con Percona Server 5.5.28. La variable de configuración innodb_autoinc_lock_mode=1(la predeterminada):
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=3indica que se generó una identificación, pero no se usó en la fila.
Mientras que REPLACEelimina la fila original e inserta una nueva fila, generando y almacenando 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 |
+----+------+
En caso de que quieras ver lo que significa todo esto, aquí tienes un detalle 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ó demasiado trabajo adicional al establecer la columna igual a sí misma, en realidad no se necesita ninguna 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. 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
Ahí lo tienes. Dado que todo esto se realizó en una mesa nueva casi sin datos y no en producción, los tiempos de ejecución fueron microscópicos e irrelevantes. Cualquiera que tenga datos del mundo real sería más que bienvenido a contribuir con ellos.
Algo importante que agregar: cuando usa INSERT IGNORE y tiene violaciones de claves, ¡MySQL NO genera una advertencia!
Si intenta, por ejemplo, insertar 100 registros a la vez, y uno de ellos es defectuoso, obtendrá 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 analizarlo para obtener el valor "Duplicados".
Lo uso habitualmente INSERT IGNOREy también parece exactamente el tipo de comportamiento que estás buscando. Siempre que sepa que las filas que podrían causar conflictos de índice no se insertarán y planifique su programa en consecuencia, no debería causar ningún problema.
Como se mencionó anteriormente, si usa INSERT..IGNORE, los errores que ocurren al ejecutar la instrucción INSERT se tratan como advertencias.
Una cosa que no se menciona explícitamente es que INSERT..IGNORE provocará que los valores no válidos se ajusten a los valores más cercanos cuando se inserten (mientras que los valores no válidos provocarían que la consulta se cancele si no se utiliza la palabra clave IGNORE).
