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

Je vous conseille d'utiliser 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:

• D'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 bien empêcher le REPLACE.
• Les déclencheurs d'incendie sur DELETE sont exécutées inutilement.
• Effets secondaires sont propagées à des répliques trop.

correction: les deux REPLACE et INSERT...ON DUPLICATE KEY UPDATE sont non-standard, propriétaire des inventions propres à MySQL. ANSI SQL 2003 définit un MERGE déclaration qui peut résoudre le même besoin (et plus), mais MySQL ne prend pas en charge la 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 qui INSERT...ON DUPLICATE KEY UPDATE provoque une nouvelle auto-incrément id alloués. Il est vrai que la nouvelle carte d'identité 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 variable de configuration innodb_autoinc_lock_mode=1 (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

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

Alors que REPLACE ne supprimez la ligne d'origine et insère une nouvelle ligne, générant et d'enregistrer un 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 |
+----+------+

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 une défaillant, 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 le Documentation de 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 de l'analyser pour les "Doublons" de la valeur.

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é).

Remplacer Dans qui semble être une option. Ou vous pouvez vérifier avec

IF NOT EXISTS(QUERY) Then INSERT

Cela permettra d'insérer ou de supprimer puis l'insérer. J'ai tendance à aller pour un IF NOT EXISTS vérifier d'abord.

Danger potentiel d'INSÉRER IGNORER.
Si vous essayez d'insérer une valeur VARCHAR plus longue de la colonne a été défini avec la valeur sera tronqué et inséré, MÊME SI le mode strict est activé.

INSERT...ON DUPLICATE KEY UPDATE est préférable d'éviter toute erreur de gestion des Exceptions.

Cette solution fonctionne lorsque vous avez **1 contrainte unique** seulement

Dans mon cas, je sais que col1 et col2 faire un unique indice composite.

Il garde la trace de l'erreur, mais ne jetez pas une exception sur un double.
Concernant les performances, la mise à jour par la même valeur est efficace comme MySQL avis cela et ne pas le mettre à jour

INSERT INTO table
  (col1, col2, col3, col4)
VALUES
  (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
    col1 = VALUES(col1),
    col2 = VALUES(col2)

L'idée d'utiliser cette approche est venu à partir des commentaires à phpdelusions.net/pdo.