Comment "insérer s'il n'existe pas" dans MySQL ?

Question

J'ai commencé par googler, et j'ai trouvé ceci article qui parle des tables mutex.

J'ai une table contenant environ 14 millions d'enregistrements. Si je veux ajouter des données supplémentaires dans le même format, existe-t-il un moyen de m'assurer que l'enregistrement que je veux insérer n'existe pas déjà sans utiliser une paire de requêtes (c'est-à-dire une requête pour vérifier et une autre pour insérer si le jeu de résultats est vide) ?

Est-ce qu'un unique contrainte sur un champ garantissant la insert échouera s'il est déjà là ?

Il semble qu'avec simplement une contrainte, lorsque j'émets l'insertion via php, le script croque.

Answer 1

Utiliser INSERT IGNORE INTO table

voir http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html

il y a aussi INSERT … ON DUPLICATE KEY UPDATE vous pouvez trouver des explications sur dev.mysql.com

---

Poste de bogdan.org.ua en fonction de Le cache web de Google :

18 octobre 2007

Pour commencer : à partir de la dernière version de MySQL, la syntaxe présentée dans le titre n'est pas possible. Mais il existe plusieurs moyens très faciles d'accomplir ce qui est ce qui est attendu en utilisant la fonctionnalité existante.

Il y a 3 solutions possibles : utiliser INSERT IGNORE, REPLACE, ou INSERT ON DUPLICATE KEY UPDATE.

Imaginons que nous ayons une table :

CREATE TABLE `transcripts` (
`ensembl_transcript_id` varchar(20) NOT NULL,
`transcript_chrom_start` int(10) unsigned NOT NULL,
`transcript_chrom_end` int(10) unsigned NOT NULL,
PRIMARY KEY (`ensembl_transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Imaginons maintenant que nous ayons un pipeline automatique qui importe des transcriptions des méta-données d'Ensembl, et que, pour diverses raisons, le pipeline peut être interrompu à n'importe quelle étape de l'exécution. Ainsi, nous devons assurer deux choses :

1. les exécutions répétées du pipeline ne détruiront pas notre base de données

2. les exécutions répétées ne mourront pas à cause d'erreurs de "duplicate clé primaire ".

Méthode 1 : utiliser REPLACE

C'est très simple :

REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Si l'enregistrement existe, il sera écrasé ; s'il n'existe pas encore, il sera créé. il n'existe pas encore, il sera créé. Cependant, l'utilisation de cette méthode n'est pas efficace efficace dans notre cas : nous n'avons pas besoin d'écraser les enregistrements existants, il est simplement les ignorer.

Méthode 2 : utiliser INSERT IGNORE Egalement très simple :

INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Ici, si le 'ensembl_transcript_id' est déjà présent dans la base de données base de données, il sera silencieusement sauté (ignoré). (Pour être plus précis voici une citation du manuel de référence de MySQL : "Si vous utilisez le mot-clé IGNORE les erreurs qui surviennent lors de l'exécution de l'instruction INSERT sont plutôt sont traitées comme des avertissements. Par exemple, sans IGNORE, une ligne qui duplique un index UNIQUE ou une valeur PRIMARY KEY existant dans la table provoque une erreur de clé dupliquée et l'instruction est interrompue"). Si l'enregistrement n'existe pas encore, il sera créé.

Cette deuxième méthode présente plusieurs faiblesses potentielles, notamment la non-interruption de la requête en cas de problème (voir le manuel). Elle doit donc être utilisée si elle a été testée au préalable sans le mot clé IGNORE.

Méthode 3 : utiliser INSERT ON DUPLICATE KEY UPDATE :

La troisième option consiste à utiliser INSERT … ON DUPLICATE KEY UPDATE syntaxe, et dans la partie UPDATE, il suffit de ne rien faire et d'effectuer une opération vide de sens. (vide), comme le calcul de 0+0 (Geoffray suggère de faire l'affectation de l'id=id pour que le moteur d'optimisation de MySQL ignore cette opération). l'affectation de l'id=id pour que le moteur d'optimisation de MySQL ignore cette opération). L'avantage de cette méthode est qu'elle ignore seulement les événements de clés dupliquées duplicate key events, et s'arrête toujours en cas d'autres erreurs.

Dernière remarque : ce billet a été inspiré par Xaprb. Je vous conseille également de de consulter son autre billet sur l'écriture de requêtes SQL flexibles.

Answer 2

Solution :

INSERT INTO `table` (`value1`, `value2`) 
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL 
WHERE NOT EXISTS (SELECT * FROM `table` 
      WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1) 

Explication :

La requête la plus interne

SELECT * FROM `table` 
      WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1

utilisé comme WHERE NOT EXISTS -condition détecte s'il existe déjà une ligne avec les données à insérer. Après avoir trouvé une ligne de ce type, la requête peut s'arrêter, d'où la condition LIMIT 1 (micro-optimisation, peut être omise).

La requête intermédiaire

SELECT 'stuff for value1', 'stuff for value2' FROM DUAL

représente les valeurs à insérer. DUAL fait référence à une table spéciale à une ligne et une colonne présente par défaut dans toutes les bases de données Oracle (cf. https://en.wikipedia.org/wiki/DUAL_table ). Sur un serveur MySQL version 5.7.26, j'ai obtenu une requête valide en omettant les éléments suivants FROM DUAL mais les versions plus anciennes (comme 5.5.60) semblent nécessiter l'utilisation de l'option FROM informations. En utilisant WHERE NOT EXISTS la requête intermédiaire renvoie un ensemble de résultats vide si la requête la plus interne a trouvé des données correspondantes.

La requête externe

INSERT INTO `table` (`value1`, `value2`) 

insère les données, si elles sont retournées par la requête intermédiaire.

Answer 3

sur la mise à jour de la clé dupliquée ou insérer ignorer peuvent être des solutions viables avec MySQL.

---

Exemple de sur la mise à jour de la clé dupliquée mise à jour basée sur mysql.com

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

---

Exemple de insérer ignorer basé sur mysql.com

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Ou :

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Ou :

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Answer 4

Toute contrainte simple devrait faire l'affaire, si une exception est acceptable. Exemples :

• clé primaire si non substituable
• contrainte unique sur une colonne
• contrainte unique multi-colonnes

Je suis désolé si cela semble si simple. Je sais que ça a l'air mauvais face au lien que vous partagez avec nous. ;-(

Mais je n'ai pas hésité à donner cette réponse, car elle semble répondre à votre besoin. (Si ce n'est pas le cas, cela pourrait déclencher une mise à jour de vos exigences, ce qui serait également "une bonne chose"(TM)).

Modifié : Si une insertion devait casser la contrainte unique de la base de données, une exception est lancée au niveau de la base de données, relayée par le pilote. Cela arrêtera certainement votre script, avec un échec. Il doit être possible en PHP d'adresser ce cas ...

Answer 5

Voici une fonction PHP qui insère une ligne uniquement si toutes les valeurs des colonnes spécifiées n'existent pas déjà dans la table.

• Si l'une des colonnes diffère, la ligne sera ajoutée.

• Si le tableau est vide, la ligne sera ajoutée.

• Si une ligne existe et que toutes les colonnes spécifiées ont les valeurs indiquées, la ligne ne sera pas ajoutée.

  function insert_unique($table, $vars)
  {
    if (count($vars)) {
      $table = mysql_real_escape_string($table);
      $vars = array_map('mysql_real_escape_string', $vars);
  
      $req = "INSERT INTO `$table` (`". join('`, `', array_keys($vars)) ."`) ";
      $req .= "SELECT '". join("', '", $vars) ."' FROM DUAL ";
      $req .= "WHERE NOT EXISTS (SELECT 1 FROM `$table` WHERE ";
  
      foreach ($vars AS $col => $val)
        $req .= "`$col`='$val' AND ";
  
      $req = substr($req, 0, -5) . ") LIMIT 1";
  
      $res = mysql_query($req) OR die();
      return mysql_insert_id();
    }
  
    return False;
  }

Exemple d'utilisation :

<?php
insert_unique('mytable', array(
  'mycolumn1' => 'myvalue1',
  'mycolumn2' => 'myvalue2',
  'mycolumn3' => 'myvalue3'
  )
);
?>