J'ai commencé par googler, et j'ai trouvé cet [article][1] qui parle des tables mutex.
J'ai une table contenant environ 14 millions d'enregistrements. 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 (c'est-à-dire une requête pour vérifier et une autre pour insérer si le jeu de résultats est vide) ?
Une contrainte "unique" sur un champ garantit-elle que l'insertion échouera si l'enregistrement existe déjà ?
Il semble qu'avec seulement une contrainte, lorsque je lance l'insertion via php, le script se bloque.
[1] : http://www.xaprb.com/blog/2005/09/25/insert-if-not-exists-queries-in-mysql/
utiliser INSERT IGNORE INTO table
.
voir http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html
il existe aussi la syntaxe INSERT ... ON DUPLICATE KEY UPDATE
, vous pouvez trouver des explications sur dev.mysql.com
Poste de bogdan.org.ua selon [webcache de Google][1]:
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 façons très simples d'accomplir ce qui est attendu en utilisant les fonctionnalités existantes. Mais il existe plusieurs moyens très simples d'accomplir ce qui est attendu en utilisant les fonctionnalités existantes.
Il y a 3 solutions possibles : utiliser INSERT IGNORE, REPLACE, ou > insérer ... sur une clé dupliquée. 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) non signé 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 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. peut être interrompu à n'importe quelle étape de l'exécution. Ainsi, nous devons garantir deux choses : 1) des exécutions répétées du pipeline ne détruiront pas notre > base de données, et 2) des exécutions répétées du pipeline ne détruiront pas notre base de données, et 2) les exécutions répétées ne mourront pas à cause d'erreurs de 'duplicate duplicate > primary key' errors.
Méthode 1 : utilisation de REPLACE
C'est très simple :
REPLACE INTO
transcripts
SETensembl_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éé. s'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 suffit de les sauter. simplement les ignorer.
Méthode 2 : utiliser INSERT IGNORE Également très simple :
INSERT IGNORE INTO
transcripts
. SETensembl_transcript_id
= 'ENSORGT00000000001',transcript_chrom_start
= 12345,transcript_chrom_end
= 12678 ;Ici, si l'identifiant 'ensembl_transcript_id' est déjà présent dans la base de données, il sera silencieusement supprimé. 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 Si vous utilisez le mot-clé IGNORE, les erreurs qui se produisent lors de l'exécution de l'instruction INSERT sont traitées comme des avertissements. 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 Si l'enregistrement n'existe pas encore, il sera créé.
Cette deuxième méthode présente plusieurs faiblesses potentielles, notamment le non-abandon de la requête au cas où un autre problème surviendrait (voir le manuel). Elle doit donc être utilisée si elle a été testée au préalable sans le mot-clé mot-clé IGNORE.
Il y a une autre option : utiliser la syntaxe `INSERT ... ON DUPLICATE KEY UPDATE'. et, dans la partie UPDATE, ne rien faire d'autre qu'une opération dénuée de sens, comme le calcul de la valeur de la clé. (vide), comme le calcul de 0+0 (Geoffray suggère de faire l'affectation de l'id=id pour MySpace). l'affectation id=id pour que le moteur d'optimisation de MySQL ignore cette opération. cette opération). L'avantage de cette méthode est qu'elle n'ignore que les événements de clé l'avantage de cette méthode est qu'elle ignore uniquement les événements de clé en double, tout en abandonnant sur d'autres erreurs.
En guise de conclusion : ce billet a été inspiré par Xaprb. Je vous conseille également de consulter son autre billet sur l'écriture de requêtes SQL flexibles.
[on duplicate key update][1], ou [insert ignore][2] peuvent être des solutions viables avec MySQL.
Exemple de mise à jour [on duplicate key update][1] basé 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 [insert ignore][2] 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] ... ]
[1] : http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html [2] : http://dev.mysql.com/doc/refman/5.1/en/insert.html
Toute contrainte simple devrait faire l'affaire, si une exception est acceptable. Exemples :
Désolé si cela semble faussement simple. Je sais que cela semble mauvais par rapport 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 vous inciter à mettre à jour vos exigences, ce qui serait aussi une "bonne chose"(TM)).
Edited : 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. Cela arrêtera certainement votre script, avec un échec. Il doit être possible en PHP d'adresser ce cas ...