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


J'ai commencé par googler, et j'ai trouvé ça article qui parle de tables mutex.

J'ai une table avec ~ 14 millions de dossiers. Si je veux ajouter plus de données dans le même format, existe-t-il un moyen de s'assurer que l'enregistrement que je veux insérer n'existe pas sans utiliser une paire de requêtes (ie, une requête à vérifier et une à insérer est l'ensemble de résultats vide)?

Fait un unique contrainte sur un champ garantir la insert va échouer si c'est déjà là?

Il semble que seulement une contrainte, quand je publie l'insertion via php, le script croque.


672
2017-09-01 08:56


origine


Réponses:


utilisation 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 syntaxe, vous pouvez trouver des explications sur dev.mysql.com


Message de bogdan.org.ua selon Webcache de Google:

18 octobre 2007

Pour commencer: à partir du dernier MySQL, la syntaxe présentée dans le titre n'est pas   possible. Mais il y a plusieurs façons très simples d'accomplir ce qui est   prévu en utilisant les fonctionnalités existantes.

Il existe 3 solutions possibles: utiliser INSERT IGNORE, REPLACE ou   INSÉRER ... SUR LA MISE À JOUR DU CLÉ DUPLICAT.

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;

Maintenant, imaginez que nous avons un transcript automatique d'importation de pipeline   méta-données de Ensembl, et que, pour diverses raisons, le pipeline   pourrait être brisé à n'importe quelle étape de l'exécution. Ainsi, nous devons assurer deux   choses: 1) des exécutions répétées du pipeline ne détruira pas notre   base de données, et 2) les exécutions répétées ne mourront pas en raison de 'dupliquer   erreurs de clé primaire.

Méthode 1: en utilisant REMPLACER

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é; si ce n'est pas encore   existe, 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, ça va   juste pour les ignorer.

Méthode 2: en utilisant INSERT IGNORE 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 ignoré silencieusement (ignoré). (Être plus précis,   Voici une citation du manuel de référence de MySQL: "Si vous utilisez le IGNORE   mot-clé, les erreurs qui se produisent lors de l'exécution de l'instruction INSERT sont   traités comme des avertissements à la place. Par exemple, sans IGNORE, une ligne qui   duplique un index UNIQUE existant ou une valeur PRIMARY KEY dans la table   provoque une erreur de clé dupliquée et l'instruction est annulée. ".) Si le   l'enregistrement n'existe pas encore, il sera créé.

Cette seconde méthode présente plusieurs faiblesses potentielles, notamment   non-avortement de la requête au cas où un autre problème surviendrait (voir   Manuel). Ainsi, il devrait être utilisé s'il a déjà été testé sans   IGNORE mot-clé.

Il y a une autre option: utiliser INSERT ... ON UPDATE KEY UPDATE   syntaxe, et dans la partie UPDATE ne font rien faire un peu de sens   opération (vide), comme le calcul 0 + 0 (Geoffray suggère de faire le   id = id assignation pour que le moteur d'optimisation MySQL ignore cette   opération). L'avantage de cette méthode est qu'elle ignore seulement les doublons   événements clés, et encore abandonne sur d'autres erreurs.

Comme un avis final: ce poste a été inspiré par Xaprb. Je conseillerais aussi de   consulter son autre article sur l'écriture de requêtes SQL flexibles.


681
2017-09-01 09:02



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'extérieur SELECT déclaration 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 

154
2018-06-11 18:38



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


Exemple de sur la mise à jour de clé en double 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] ... ]

46
2017-09-01 09:05



Toute contrainte simple devrait faire le travail, si une exception est acceptable. Exemples :

  • clé primaire si pas substitut
  • contrainte unique sur une colonne
  • contrainte unique multi-colonnes

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

Mais je donne toujours cette réponse, car elle semble répondre à vos besoins. (Si ce n'est pas le cas, cela pourrait déclencher la mise à jour de vos exigences, ce qui serait également "une bonne chose").

Édité: Si une insertion brise 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. Il va certainement arrêter votre script, avec un échec. Il doit être possible en PHP d'adresser cette affaire ...


23
2017-09-01 09:01



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


17
2017-07-06 14:35



Voici une fonction PHP qui insérera une ligne uniquement si toutes les valeurs de 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 la table est vide, la ligne sera ajoutée.

  • S'il existe une ligne où toutes les colonnes spécifiées ont les valeurs spécifié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'
  )
);
?>

17
2018-03-06 19:56



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
2018-05-10 09:58



Replace pourrait travailler pour vous.


11
2017-09-01 09:02



Essayer:

// 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 votre grande table INDEX colonnes 'xxx' (Dans mon exemple).


5
2017-09-25 16:17



Il y a plusieurs réponses qui couvrent comment résoudre ceci si vous avez un UNIQUE indice que vous pouvez vérifier avec ON DUPLICATE KEY ou INSERT IGNORE. Ce n'est pas toujours le cas, et comme 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 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 INSERT n'est exécuté que lorsque l'ensemble de données n'existe pas.


3
2017-08-18 18:38