"INSÉRER IGNORER" vs "INSÉRER ... SUR un DOUBLE de la CLÉ de mise à JOUR"

Question

Lors de l'exécution d'un INSERT déclaration avec de nombreuses lignes, je veux sauter en double entrées qui pourrait entraîner une défaillance. Après quelques recherches, mes options semblent être l'utilisation de:

• ON DUPLICATE KEY UPDATE ce qui implique un jour inutile à un certain coût, ou
• INSERT IGNORE ce qui implique une invitation pour d'autres types de non-glissement dans l'improviste.

Suis-je le droit dans ces 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?

Answer 1

Je vous conseille d'utiliser l' INSERT...ON DUPLICATE KEY UPDATE.

Si vous utilisez INSERT IGNORE, la ligne ne sera pas réellement être inséré si elle aboutit à un double de la clé. Mais la déclaration ne génère pas une erreur. Il génère un avertissement à la place. Ces cas comprennent:

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

Si vous utilisez REPLACE, MySQL est en fait un DELETE suivie par un INSERT en interne, qui a des effets secondaires inattendus:

• Une nouvelle auto-incrément ID est attribué.
• Lignes dépendantes avec les clés étrangères peuvent être supprimés (si vous utilisez une cascade de clés étrangères) ou encore empêcher l' REPLACE.
• Les déclencheurs d'incendie sur DELETE sont exécutées inutilement.
• Les effets secondaires sont propagées aux esclaves de réplication.

correction: les deux REPLACE et INSERT...ON DUPLICATE KEY UPDATE sont non-standard, propriétaire des inventions propres à MySQL. ANSI SQL 2003 définit MERGE déclaration qui peut résoudre le même besoin (et plus), mais MySQL ne prend pas en charge l' MERGE déclaration.

---

Un utilisateur a tenté de modifier ce post (le montage a été rejeté par les modérateurs). L'édition essayé d'ajouter une revendication INSERT...ON DUPLICATE KEY UPDATE provoque une nouvelle auto-incrément id alloués. Il est vrai que le nouveau code n'est généré, mais il n'est pas utilisé dans la ligne modifiée.

Voir démonstration ci-dessous, testé avec Percona Server 5.5.28. La configuration de la variable innodb_autoinc_lock_mode=1 (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

Le ci-dessus montre que la IODKU déclaration détecte les doublons, et appelle la mise à jour pour modifier la valeur de u. Remarque l' AUTO_INCREMENT=3 indique un id a été généré, mais pas utilisé dans la ligne.

Alors qu' REPLACE ne supprimez la ligne d'origine et insère une nouvelle ligne, la génération et le stockage d'une nouvelle auto-incrément id:

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

Answer 2

Dans le cas où vous voulez voir ce que tout ceci signifie, ici, est 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

notez que la sauvé trop de travail supplémentaire par le réglage de la colonne égale à elle-même, aucune mise à jour nécessaire

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

et maintenant, certains plusieurs lignes de tests:

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

pas d'autres messages ont été générés dans la console, et il dispose maintenant de ces 4 valeurs dans le tableau de données. J'ai supprimé tout à l'exception de (1,1) donc j'ai pu tester de la 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 là vous l'avez. Depuis cet été toutes réalisées sur une nouvelle table avec presque pas de données et non pas dans la production, à la fois pour l'exécution ont été microscopique et hors de propos. N'importe qui avec des données du monde réel serait plus que le bienvenu pour participer.

Answer 3

Quelque chose d'important à ajouter: Lors de l'utilisation d'INSÉRER IGNORER et vous avez des violations de clé, MySQL n'est 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 le même, à tort, décrit dans l'officiel de Mysql Docoumentation.

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 de l'analyser pour les "Doublons" de la valeur.

Answer 4

J'utilise couramment INSERT IGNORE, et il semble que c'est exactement le genre de comportement que vous cherchez. Tant que vous savez que les lignes qui serait la cause de l'indice des conflits ne peut être inséré et vous planifiez votre programme en conséquence, il ne devrait pas causer d'ennuis.

Answer 5

Je sais que c'est vieux, mais je vais ajouter cette remarque dans le cas où quelqu'un d'autre (comme moi) arrive à cette page, tout en essayant de trouver des informations sur l'INSERTION..IGNORER.

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

Une chose qui n'est pas explicitement mentionné, c'est que l'INSERTION..IGNORER entraînera des valeurs non valides seront ajustés à la plus proche des valeurs lors de l'insertion (alors que les valeurs non valides serait la cause de la requête pour annuler si l'IGNORER le mot clé n'a pas été utilisé).