[cron] insert on duplicate key update

Soyez le premier à donner votre avis sur cette source.

Snippet vu 3 939 fois - Téléchargée 16 fois

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

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

Ajouter un commentaire

Commentaires

cod57
Messages postés
1660
Date d'inscription
dimanche 7 septembre 2008
Statut
Membre
Dernière intervention
11 septembre 2013
8 -
bonjour

/*Bonjour je crois qu'il y a problème de sécurité dans une fonction*/
/*http://www.phpsecure.info/v2/article/InjSql.php*/
/*le tableau $data doit etre ecrasé*/
/* mon test */
/*on suppose connecté*/
$link = mysql_connect('localhost','root','');
$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');
echo '
';
$Q->ajouter('lol', 'mom');
echo '
';
$Q->concatener("('lol','')");

/*mon test*/
$data=array("INSERT INTO membres (login,password,nom,email,userlevel) VALUES ('','','','','3')#','1");
print_r($Q->ajouter_tableau($data));
/*fin test*/

//array_map('mysql_real_escape_string', $data);
//mais $data = array_map('mysql_real_escape_string', $data);
//j'ai mis le return pour le test

//public function ajouter_tableau($data){
//
// $data=array_map('mysql_real_escape_string', $data)
// return $chaine = '(' . $this->guillemet . implode($this->guillemet . ', ' . $this->guillemet, $data) .
// $this->guillemet . ')';

?>
pierreSabatier
Messages postés
27
Date d'inscription
lundi 12 avril 2010
Statut
Membre
Dernière intervention
15 octobre 2011
-
Merci COD57. Effectivement, je m'étais mal renseigné sur la fonction "array_map", j'avais pensé à un passage par référence :p

Maintenant le code est sûr.

Mais attention COD57. Je crois que tu n'as pas bien compris comment utiliser cette classe. La méthode SegmeterRequete::ajouter_tableau prend en paramètre un tableau de valeurs à ajouter, et seulement les valeurs, pas la structure de la requête. Celle-ci est définie par les attributs SegmenterRequete::$debut et SegmenterRequete::$fin.
cod57
Messages postés
1660
Date d'inscription
dimanche 7 septembre 2008
Statut
Membre
Dernière intervention
11 septembre 2013
8 -
Si j'ai compris ce que tu veux faire avec cette class, mon exemple n'étais que pour mettre en évidence le non filtrage ... bon ... pour ce qui est vraiment utile ton code est intéressant, il faudra que je teste ça sur un gros update. Il y a des astuces très ingénieuses.

++
pierreSabatier
Messages postés
27
Date d'inscription
lundi 12 avril 2010
Statut
Membre
Dernière intervention
15 octobre 2011
-
Merci COD57 :-) En affectant l'attribut SegmenterRequete::$mode à Mode::DEBUG_ONE, la première requête est affichée. Ca peut être rassurant de voir le résultat avant le gros update ;-)

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.

Du même auteur (pierreSabatier)