Question "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"


Pendant l'exécution d'un INSERT déclaration avec plusieurs lignes, je veux sauter les entrées en double qui, autrement, provoqueraient un échec. Après quelques recherches, mes options semblent être l'utilisation de:

  • ON DUPLICATE KEY UPDATE ce qui implique une mise à jour inutile à un certain coût, ou
  • INSERT IGNORE ce qui implique une invitation à d'autres types d'échecs à glisser à l'improviste.

Ai-je raison dans ces hypothèses? Quelle est la meilleure façon d'ignorer simplement les lignes qui pourraient causer des doublons et de continuer sur les autres lignes?


735
2018-02-14 05:24


origine


Réponses:


Je recommanderais d'utiliser INSERT...ON DUPLICATE KEY UPDATE.

Si tu utilises INSERT IGNORE, alors la ligne ne sera pas réellement insérée si elle aboutit à une clé dupliquée. Mais la déclaration ne générera pas d'erreur. Il génère un avertissement à la place. Ces cas comprennent:

  • Insérer une clé dupliquée dans des colonnes avec PRIMARY KEY ou UNIQUE contraintes.
  • Insérer une valeur NULL dans une colonne avec un NOT NULL contrainte.
  • Insertion d'une ligne dans une table partitionnée, mais les valeurs que vous insérez ne correspondent pas à une partition.

Si tu utilises REPLACE, MySQL fait un DELETE suivi d'un INSERT en interne, qui a des effets secondaires inattendus:

  • Un nouvel identifiant d'incrémentation automatique est alloué.
  • Les lignes dépendantes avec des clés étrangères peuvent être supprimées (si vous utilisez des clés étrangères en cascade) ou bien empêcher le REPLACE.
  • Les déclencheurs qui tirent sur DELETE sont exécutés inutilement.
  • Les effets secondaires sont également propagées aux esclaves de réplication.

correction: tous les deux REPLACE et INSERT...ON DUPLICATE KEY UPDATE sont des inventions propriétaires non standard spécifiques à MySQL. ANSI SQL 2003 définit un MERGE déclaration qui peut résoudre le même besoin (et plus), mais MySQL ne supporte pas le MERGE déclaration.


Un utilisateur a essayé d'éditer ce post (l'édition a été rejetée par les modérateurs). L'éditeur a essayé d'ajouter une revendication INSERT...ON DUPLICATE KEY UPDATE provoque l'attribution d'un nouvel identifiant d'incrémentation automatique. C'est vrai que le nouvel identifiant est généré, mais il n'est pas utilisé dans la ligne modifiée.

Voir la démonstration ci-dessous, testée avec Percona Server 5.5.28. La variable de configuration innodb_autoinc_lock_mode=1 (le défaut):

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

Ce qui précède démontre que l'instruction IODKU détecte le doublon et appelle la mise à jour pour modifier la valeur de u. Noter la AUTO_INCREMENT=3 indique qu'un ID a été généré, mais pas utilisé dans la ligne.

Tandis que REPLACE supprime la ligne d'origine et insère une nouvelle ligne, générant et stocker un nouvel identifiant d'auto-incrémentation:

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



Dans le cas où vous voulez voir ce que tout cela signifie, voici un coup par coup de tout:

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 clé primaire est basée sur les deux colonnes de ce tableau de référence rapide. Une clé primaire nécessite des valeurs uniques.

Commençons:

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

note, ce qui précède a enregistré trop de travail supplémentaire en définissant la colonne égal à lui-même, aucune mise à jour réellement nécessaire

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

et maintenant quelques tests sur plusieurs lignes:

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

aucun autre message n'a été généré dans la console, et il a maintenant ces 4 valeurs dans les données de la table. J'ai tout supprimé sauf (1,1) pour pouvoir tester sur le même terrain

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

Donc là vous l'avez. Puisque tout cela a été effectué sur une nouvelle table avec presque aucune donnée et pas en production, les temps d'exécution étaient microscopiques et non pertinents. N'importe qui avec des données réelles serait plus que bienvenu pour y contribuer.


156
2017-10-21 18:23



Quelque chose d'important à ajouter: Lorsque vous utilisez INSERT IGNORE et que vous avez des violations de clé, MySQL ne déclenche PAS d'avertissement!

Si vous essayez par exemple d'insérer 100 enregistrements à la fois, avec un enregistrement défectueux, vous obtiendrez en mode interactif:

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

Comme vous le voyez: Pas d'avertissement! Ce comportement est même décrit à tort dans la documentation officielle Mysql.

Si votre script doit être informé, si certains enregistrements n'ont pas été ajoutés (en raison de violations de clé), vous devez appeler mysql_info () et l'analyser pour la valeur "Duplicates".


37
2018-04-21 10:04



Je sais que c'est vieux, mais je vais ajouter cette note au cas où quelqu'un d'autre (comme moi) arrive à cette page en essayant de trouver des informations sur INSERT..IGNORE.

Comme mentionné ci-dessus, si vous utilisez INSERT..IGNORE, les erreurs qui se produisent lors de l'exécution de l'instruction INSERT sont traitées comme des avertissements à la place.

Une chose qui n'est pas explicitement mentionnée est que INSERT..IGNORE provoquera l'ajustement des valeurs invalides aux valeurs les plus proches lorsqu'elles sont insérées (alors que les valeurs invalides entraîneraient l'abandon de la requête si le mot-clé IGNORE n'était pas utilisé).


16
2017-09-16 14:48



J'utilise régulièrement INSERT IGNORE, et cela ressemble exactement au genre de comportement que vous recherchez également. Tant que vous savez que les lignes qui provoqueraient des conflits d'index ne seront pas insérées et que vous planifiez votre programme en conséquence, cela ne devrait causer aucun problème.


14
2018-02-14 05:53



SUR LA MISE À JOUR DUPLICATE KEY n'est pas vraiment dans la norme. C'est à peu près aussi standard que REPLACE. Voir SQL MERGE.

Essentiellement, les deux commandes sont des versions de syntaxe alternative des commandes standard.


7
2018-02-14 05:57



Replace Dans semble comme une option. Ou vous pouvez vérifier avec

IF NOT EXISTS(QUERY) Then INSERT

Cela va insérer ou supprimer puis insérer. J'ai tendance à aller pour un IF NOT EXISTS vérifiez d'abord.


5
2018-02-14 05:34



Danger potentiel de INSERT IGNORE. Si vous essayez d'insérer la valeur VARCHAR plus longtemps, la colonne a été définie avec - la valeur sera tronquée et insérée MÊME SI le mode strict est activé.


2
2017-10-19 23:33



Si vous souhaitez insérer dans la table et sur le conflit de la clé primaire ou de l'index unique, il mettra à jour la ligne en conflit au lieu d'insérer cette ligne.

Syntaxe:

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

Maintenant, ici, cette instruction d'insertion peut sembler différente de ce que vous avez vu précédemment. Cette instruction insert essaye d'insérer une ligne dans la table1 avec la valeur de a et b dans la colonne column1 et column2 respectivement.

Comprenons cette déclaration en profondeur:

Par exemple: ici colonne1 est définie comme la clé primaire dans la table1.

Maintenant, si dans la table1 il n'y a pas de ligne ayant la valeur "a" dans la colonne1. Cette instruction va donc insérer une ligne dans la table1.

Maintenant si dans la table1 il y a une ligne ayant la valeur "a" dans la colonne2. Cette instruction va donc mettre à jour la valeur column2 de la ligne avec "c" où la valeur de column1 est "a".

Donc, si vous voulez insérer une nouvelle ligne sinon mettez à jour cette ligne sur le conflit de la clé primaire ou de l'index unique.
En savoir plus sur ce lien


1
2017-12-07 21:00