Comment 'insérer si non existe' dans MySQL?

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

j'ai une table avec 14 millions de disques. Si je veux ajouter plus de données dans le même format, y a-t-il un moyen de s'assurer que l'enregistrement que je veux insérer n'existe pas déjà sans utiliser une paire de requêtes (par exemple, une requête à vérifier et une à insérer Est-ce que le résultat est vide)?

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

il semble qu'avec simplement une contrainte, quand j'émets l'insert via php, le script croque.

687
demandé sur warren 2009-09-01 12:56:41
la source

9 ответов

utiliser INSERT IGNORE INTO table

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

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


poste de bogdan.org.ua selon Google webcache :

18 octobre 2007

pour commencer: à partir de la dernière MySQL, la syntaxe présentée dans le titre n'est pas possible. Mais il y a plusieurs façons très faciles d'accomplir ce qui est prévu en utilisant la fonctionnalité existante.

il y a 3 solutions possibles: en utilisant INSERT ignorer, remplacer, ou INSERT ... ON DUPLICATE KEY UPDATE.

Imaginez 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;

imaginez maintenant que nous avoir un pipeline d'importation automatique transcriptions méta-données de Ensembl, et que pour diverses raisons le pipeline peut être rompu à toute étape de l'exécution. Nous devons donc veiller à ce que deux choses: 1) exécutions répétées de la canalisation de ne pas détruire notre base de données, et 2) les exécutions répétées ne mourront pas en raison de " dupliquer clé primaire’ erreurs.

Méthode 1: Remplacer

c'est très simple:

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

si le document existe, il sera réécrit; s'il n'existe pas encore existe pas, il sera créé. Cependant, l'utilisation de cette méthode n'est pas efficace pour notre cas: nous n'avons pas besoin d'écraser les enregistrements existants, c'est bien juste de les ignorer.

Méthode 2: en utilisant INSERT ignorer aussi 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 le base de données, il sera silencieusement sauté (ignoré). (Pour être plus précis, voici une citation du manuel de référence MySQL :" si vous utilisez L'ignorer mot-clé, les erreurs qui se produisent lors de l'exécution de L'instruction INSERT sont traité plutôt comme des avertissements. Par exemple, sans ignorer, une rangée qui duplique un indice UNIQUE existant ou une valeur clé primaire dans le tableau provoque une erreur double-clé et l'instruction est annulée.".) Si le enregistrement n'existe pas encore, il sera créé.

cette seconde méthode présente plusieurs faiblesses potentielles, comprendre non-avortement de la requête en cas de tout autre problème se produit (voir le manuel.) Ainsi, il doit être utilisé s'il a déjà été testé sans le IGNORE le mot clé.

il y a une autre option: Utiliser INSERT ... sur la mise à jour de la clé dupliquée syntaxe, et dans la partie mise à jour, ne rien faire. (vide), comme calculer 0+0 (Geoffray suggère de faire le l'affectation id=id pour le moteur D'optimisation MySQL pour ignorer ceci opération.) L'avantage de cette méthode est qu'elle n'ignore en double les principaux événements, et encore abandonne sur d'autres erreurs.

comme dernier avertissement: ce billet s'inspire de Xaprb. J'aimerais vous conseillons également de consultez son autre post sur l'écriture de requêtes SQL flexibles.

694
répondu knittl 2015-07-28 12:30:28
la source
INSERT INTO `table` (value1, value2) 
SELECT 'stuff for value1', 'stuff for value2' FROM `table` 
WHERE NOT EXISTS (SELECT * FROM `table` 
      WHERE value1='stuff for value1' AND value2='stuff for value2') 
LIMIT 1 

alternativement, l'énoncé SELECT externe peut se référer à DUAL afin de gérer le cas où la table est initialement vide:

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 
158
répondu Server 2014-06-04 23:52:45
la source

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


exemple de sur la mise à jour de la clé 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] ... ]
47
répondu Zed 2015-09-16 20:36:16
la source

N'importe quelle contrainte simple devrait faire le travail, si une exception est acceptable. Exemples:

  • clé primaire si non de substitution
  • contrainte unique sur une colonne
  • multi-colonne unique constraint

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

mais je donne néanmoins cette réponse, parce qu'il semblent combler votre besoin. (Sinon, il peut déclencher la mise à jour de vos exigences, ce qui serait "une bonne chose"(TM) également).

modifié : si un insert casse 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 ...

24
répondu KLE 2009-09-01 20:15:27
la source
REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Si le dossier existe déjà, il sera écrasé; si elle n'existe pas encore, il sera créé.

18
répondu Rocio 2012-07-06 18:35:08
la source

Voici une fonction PHP qui insérera une ligne seulement si toutes les valeurs de colonnes spécifiées n'existent pas déjà dans le tableau.

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

  • si la table est vide, la ligne sera ajoutée.

  • S'il existe une rangée où toutes les colonnes spécifiées ont les valeurs spécifiées, la rangée 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'usage:

<?php
insert_unique('mytable', array(
  'mycolumn1' => 'myvalue1',
  'mycolumn2' => 'myvalue2',
  'mycolumn3' => 'myvalue3'
  )
);
?>
18
répondu Jrm 2014-12-02 21:33:09
la source

essayez ce qui suit:

IF (SELECT COUNT(*) FROM beta WHERE name = 'John' > 0)
  UPDATE alfa SET c1=(SELECT id FROM beta WHERE name = 'John')
ELSE
BEGIN
  INSERT INTO beta (name) VALUES ('John')
  INSERT INTO alfa (c1) VALUES (LAST_INSERT_ID())
END
16
répondu Jeb's 2013-05-10 14:18:28
la source

, Essayez:

// Check if exist cod = 56789
include "database.php";

$querycheck = mysql_query ("SELECT * FROM `YOURTABLE` WHERE `xxx` = '56789';");
$countrows = mysql_num_rows($querycheck);
if($countrows == '1')
{
  // Exist 
}
else
{
 // .... Not exist
}

ou vous pouvez faire:

// Check if exist cod = 56789
include "database.php";

$querycheck = mysql_query ("SELECT * FROM `YOURTABLE` WHERE `xxx` = '56789';");
$countrows = mysql_num_rows($querycheck);
while($result = mysql_fetch_array($querycheck))
{
    $xxx = $result['xxx'];
    if($xxx == '56789')
    {
      // Exist
    }
    else
    {
      // Not exist
    }
}

Cette méthode est rapide et facile. Pour améliorer la vitesse de la requête dans vos grandes colonnes D'INDEX 'xxx' ( dans mon exemple ).

4
répondu Andrea php 2015-09-25 19:17:45
la source

il y a plusieurs réponses qui couvrent comment résoudre cela si vous avez un UNIQUE index que vous pouvez vérifier avec ON DUPLICATE KEY ou INSERT IGNORE . Ce n'est pas toujours le cas, et que UNIQUE a une contrainte de longueur (1000 octets) vous pourriez ne pas être en mesure de changer cela. Par exemple, j'ai dû travailler avec des métadonnées dans WordPress ( wp_postmeta ).

Je l'ai finalement résolu avec deux requêtes:

UPDATE wp_postmeta SET meta_value = ? WHERE meta_key = ? AND post_id = ?;
INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT DISTINCT ?, ?, ? FROM wp_postmeta WHERE NOT EXISTS(SELECT * FROM wp_postmeta WHERE meta_key = ? AND post_id = ?);

la requête 1 est une requête régulière UPDATE requête sans effet lorsque l'ensemble de données en question n'est pas là. La requête 2 est un INSERT qui dépend d'un NOT EXISTS , c'est-à-dire que le INSERT n'est exécuté que lorsque l'ensemble de données n'existe pas.

3
répondu wortwart 2016-08-18 21:38:12
la source

Autres questions sur php sql performance mysql primary-key