[cron] insert on duplicate key update
Contenu du snippet
Un CRON journalier doit mettre à jour plus de 100000 enregistrements sur une table MYSQL (vous pourriez adapter cela à votre SGBD).
D'un côté, faire un gros delete puis un insert multiple décalait chaque jour les index de la table, laissant des gros trous.
D'un autre, modifier chaque enregistrement prenait beaucoup de temps et de ressources serveur.
D'où cette idée de segmenter une requête grâce à la fonction "VALUES" disponible depuis Mysql 4.1.1 dans un INSERT ON DUPLICATE KEY UPDATE. Par exemple :
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
/!\ AVERTISSEMENT : ce code n'est pas testé (celui utilisé en prod est différent et un peu plus archaïque je crois).
Bien qu'étant destiné en priorité à un cron - où les valeurs en entrée sont contrôlées, s'il y a une faille, ce serait sympa de le dire.
Il s'agit d'un code d'initié, plus en fonction de son usage que de sa complexité intrinsèque.
D'un côté, faire un gros delete puis un insert multiple décalait chaque jour les index de la table, laissant des gros trous.
D'un autre, modifier chaque enregistrement prenait beaucoup de temps et de ressources serveur.
D'où cette idée de segmenter une requête grâce à la fonction "VALUES" disponible depuis Mysql 4.1.1 dans un INSERT ON DUPLICATE KEY UPDATE. Par exemple :
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
Source / Exemple :
<?php
interface Mode {
const PROD = 0;
const DEV = 1;
const DEBUG_ONE = 2;
const DEBUG_ALL = 3;
}
class SegmenterRequete {
private $debut;
private $fin;
private $requete;
private $n;
public $separateur = ', ';
public $guillemet = "'";
public $mode = Mode::PROD;
public $pause = 2;
public $max = 200;
public function __construct($debut, $fin){
$this->debut = $debut;
$this->fin = $fin;
$this->initialiser_requete();
}
public function __destruct(){
// si les enregistrements n'ont pas tous ete faits
if (0 < $this->n) {
// nettoyer la requete : supprimer son dernier separateur
$this->requete = substr($this->requete, 0, -strlen($this->separateur));
$this->executer();
}
}
private function initialiser_requete(){
// ecrire une nouvelle requete
$this->requete = $this->debut;
$this->n = 0;
}
private function executer(){
$this->requete .= $this->fin;
switch ($this->mode) {
case Mode::PROD:
mysql_query($this->requete);
sleep($this->pause);
break;
case Mode::DEV:
mysql_query($this->requete);
// pas de break, on "passe" au cas suivant
case Mode::DEBUG_ONE:
die($this->requete);
break;
case Mode::DEBUG_ALL:
print($this->requete) . "\n<br/>";
break;
}
}
public function ajouter(){
return $this->ajouter_tableau(func_get_args());
}
public function ajouter_tableau($data){
$data = array_map('mysql_real_escape_string', $data);
$chaine = '(' . $this->guillemet . implode($this->guillemet . ', ' . $this->guillemet, $data) .
$this->guillemet . ')';
return $this->concatener($chaine);
}
public function concatener($ch){
$this->requete .= $ch;
if (++$this->n < $this->max) {
$this->requete .= $this->separateur;
}
else {
$this->executer();
$this->initialiser_requete();
}
return $this;
}
}
/** un exemple fictif
$Q = new SegmenterRequete('INSERT INTO T (pop, top) VALUES ', ' ON DUPLICATE KEY UPDATE SET pop = VALUES(pop);');
$Q->mode = Mode::DEBUG_ALL;
$Q->max = 2;
$Q->ajouter('lol', 'mom');
$Q->ajouter('lol', 'mom');
$Q->concatener("('lol','')");
- /
Conclusion :
/!\ AVERTISSEMENT : ce code n'est pas testé (celui utilisé en prod est différent et un peu plus archaïque je crois).
Bien qu'étant destiné en priorité à un cron - où les valeurs en entrée sont contrôlées, s'il y a une faille, ce serait sympa de le dire.
Il s'agit d'un code d'initié, plus en fonction de son usage que de sa complexité intrinsèque.
A voir également
- [cron] insert on duplicate key update
- On duplicate key update ✓ - Forum PHP
- INSERT INTO [...] ON DUPLICATE KEY UPDATE [...] ✓ - Forum SQL
- L'instruction insert est en conflit avec la contrainte foreign key - Forum Visual Basic 6
- Problème avec alter table ... add constraint ...foreign key .. ✓ - Forum SQL
- Sql update plusieurs lignes ✓ - Forum SQL
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.