"INSERT IGNORE" vs " INSERT ... ON DUPLICATE KEY UPDATE"
lors de l'exécution d'une instruction INSERT avec de nombreuses lignes, je veux sauter les entrées en double qui causeraient autrement une défaillance. Après quelques recherches, mes options semblent être l'utilisation de:
-
ON DUPLICATE KEY UPDATEce qui implique une mise à jour inutile à un certain coût, ou -
INSERT IGNOREce qui implique une invitation à d'autres types de non-glissade inopinée.
hypothèses? Quelle est la meilleure façon de simplement ignorer les lignes qui pourrait provoquer des doublons et juste continuer sur les autres lignes?
10 réponses
je recommande d'utiliser INSERT...ON DUPLICATE KEY UPDATE .
si vous utilisez 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 plutôt un avertissement. Ces affaires comprennent:
- insérant une clé double dans les colonnes avec les contraintes
PRIMARY KEYouUNIQUE. - insérant un NULL dans une colonne avec un
NOT NULLcontrainte. - insérant une ligne à une table partitionnée, mais les valeurs que vous insérez ne sont pas mappées à une partition.
si vous utilisez REPLACE , MySQL fait en fait un DELETE suivi d'un INSERT à l'interne, qui a certains effets secondaires inattendus:
- un nouvel ID d'incrément automatique est attribué.
- les lignes dépendantes avec des clés étrangères peuvent être supprimées (si vous utilisez la cascade clés étrangères) ou bien empêcher le
REPLACE. - déclenche que le feu sur
DELETEsont exécutés inutilement. - les effets secondaires sont propagés aux esclaves de réplication aussi.
" correction: les deux REPLACE et INSERT...ON DUPLICATE KEY UPDATE sont des inventions exclusives non standard spécifiques à MySQL. ANSI SQL 2003 définit une déclaration MERGE qui peut résoudre le même besoin( et plus), mais MySQL ne supporte pas la déclaration MERGE .
un utilisateur a essayé de modifier ce post (l'édition a été rejetée par les modérateurs). L'édition essayé d'ajouter une revendication que INSERT...ON DUPLICATE KEY UPDATE provoque une nouvelle auto-incrément id alloués. Il est vrai que le nouvel id est généré , mais il n'est pas utilisé dans la rangée modifiée.
voir démonstration ci-dessous, testé avec le serveur Percona 5.5.28. Configuration variable innodb_autoinc_lock_mode=1 (la valeur par 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 la déclaration IODKU détecte le duplicata, et invoque la mise à jour pour changer la valeur de u . Note le AUTO_INCREMENT=3 indique qu'un id a été généré, mais pas utilisé dans la rangée.
attendu que REPLACE supprime la ligne d'origine et insère une nouvelle ligne, générant et stockant un nouvel identifiant d'incrément automatique:
mysql> select * from foo;
+----+------+
| id | u |
+----+------+
| 1 | 20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u |
+----+------+
| 3 | 20 |
+----+------+
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
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, le ci-dessus a sauvé trop de travail supplémentaire en mettant la colonne égale à 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 multiples essais en ligne:
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 elle a maintenant ces 4 valeurs dans les données de tableau. J'ai tout supprimé sauf (1,1) pour pouvoir tester sur le même terrain de jeu
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 voilà. Comme tout cela a été effectué sur une nouvelle table avec presque aucune donnée et pas en production, les délais d'exécution étaient microscopiques et sans importance. N'importe qui avec des données du monde réel serait plus que bienvenue de contribuer.
quelque chose d'important à ajouter: lorsque vous utilisez INSERT ignorer et vous avez des violations clés, MySQL ne soulève pas un avertissement!
Si vous essayez par exemple d'insérer 100 enregistrements à la fois, avec un 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 de mise en garde! Ce comportement est même décrit à tort dans le Mysql Documentation.
si votre script a besoin d'être informé, si certains enregistrements n'ont pas été ajoutés (en raison de violations des clés), vous devez appeler mysql_info() et le Parser pour la valeur" Duplicates".
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..IGNORER.
comme indiqué ci-dessus, si vous utilisez INSERT..Ignorer, 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 mentionnée explicitement est cet INSERT..Ignorer entraînera des valeurs invalides qui seront ajustées aux valeurs les plus proches lorsqu'elles seront insérées. (alors que les valeurs invalides provoqueraient l'abandon de la requête si le mot-clé IGNORE n'était pas utilisé).
j'utilise couramment INSERT IGNORE , et ça ressemble exactement au genre de comportement que vous recherchez aussi. Tant que vous savez que les lignes qui causeraient des conflits d'index ne seront pas insérées et que vous planifiez votre programme en conséquence, il ne devrait pas causer de problème.
Replace on dirait une option. Ou vous pouvez vérifier avec
IF NOT EXISTS(QUERY) Then INSERT
insérer ou supprimer puis insérer. J'ai tendance à faire un IF NOT EXISTS d'abord.
danger potentiel D'insertion ignorer. 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é.
si vous utilisez insert ignore ayant un énoncé SHOW WARNINGS; à la fin de votre jeu de requête affichera une table avec tous les Avertissements, y compris quels IDs étaient les doublons.
si vous voulez insérer dans le tableau 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 déclaration insert peut sembler différente de ce que vous avez vu plus tôt. Cette instruction insère essayer d'insérer une ligne dans le Tableau1 avec la valeur de a et b dans la colonne Colonne1 et Colonne2 respectivement.
comprenons cette déclaration en profondeur:
par exemple: ici, la Colonne1 est définie comme la clé primaire dans le Tableau1.
maintenant si dans le Tableau1 il n'y a pas de ligne ayant la valeur "a" dans la Colonne1. Cette déclaration insérera donc une ligne dans le Tableau1.
Maintenant, si dans table1 il y a une ligne ayant la valeur "a" dans colonne2. Cet énoncé va donc mettre à jour la valeur de la colonne 2 de la rangée avec "c" où la valeur de la colonne 1 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.
pour en savoir plus sur ce lien