Mysql5 – Requêtes multiples

Si vous voulez parcourir toutes les améliorations de MySql5, lisez les official relase notes.

L’objectif de ce post est d’écrire des requêtes MySql plus efficaces. Certains serveurs surveillent les ressources MySql qu’occupent une page Web. Si le nombre de requêtes pour une simple page Web est considéré comme trop gourmand en ressources, le développeur pourrait bien recevoir un avertissement (voire plus) de la part de l’hébergeur.

Lisez l’article “PHP5 : Afficher toutes les requêtes MySql d’une page Web” pour surveiller vos requêtes.

Ce sur quoi je veux me concentrer ici est une syntaxe qui a été introduite dans MySql 4.1 mais qui me semble trop peu utilisée :

INSERT INTO… ON DUPLICATE KEY UPDATE…

Mais parcourons tout d’abord la requête multiple pour l’effacement de données.

Effacer plusieurs lignes en une seule requête (DELETE)

Au lieu de générer des requêtes dans le but d’effacer des lignes d’une table :

$mysql = Mysql(); //initialisation de l’objet

$mysql->Connect(); //connexion à la BD

$array = array(1,2,4); //lignes à effacer

FOREACH($array as $value) {

$sql = “DELETE FROM table_name WHERE id=’$value’”;

$mysql->strictExecute($sql); //Exécution de la requête par l’objet $mysql

}

ce qui produira 3 requêtes dans cet exemple, vous pourriez faire la chose suivante :

$mysql = Mysql(); //initialisation de l’objet

$mysql->Connect(); //connexion à la BD

$array = array(1,2,4); //lignes à effacer

$sql = “”;

FOREACH($array as $value) {

IF($sql <> “”) $sql .= ” OR “;

$sql .= “id=’$value’”; //ajoute la chaîne id=’$value’ à $sql

}

$sql = “DELETE FROM table_name WHERE “.$sql; //construit la requête unique: DELETE FROM table_name WHERE id=’1′ OR id=’2′ OR id=’4′;

$mysql->strictExecute($sql); //Exécution de la requête par l’objet $mysql

ce qui produira une seule requête à la BD.

Je conseillerais même d’utiliser la sous-requête IN suivie de la condition :

$mysql = Mysql(); //initialisation de l’objet

$mysql->Connect(); //connexion à la DB

$array = array(1,2,4); //lignes à effacer

$sql = “”;

FOREACH($array as $value) {

IF($sql <> “”) $sql .= “, “;

$sql .= “‘$value’”; //ajoute la chaîne id=’$value’ à $sql

}

$sql = “DELETE FROM table_name WHERE id IN (“.$sql.”)”; //construit la requête unique : DELETE FROM table_name WHERE id IN (’1′, ’2′, ’4′);

$mysql->strictExecute($sql); //Exécution de la requête par l’objet $mysql

Insérer plusieurs lignes en une seule requête (INSERT TO)

Pour insérer de multiples lignes en une seule requête, utilisez la requête suivante :

$mysql = Mysql(); //initialisation de l’objet

$mysql->Connect(); //connexion à la BD

$books = array(0=>array(“title”=>”Mysql explained”,”n_pages”=>”350″),

1=>array(“title”=>”PHP explained”,”n_pages”=>”150″)

); //lignes à insérer

$sql = “”;

FOREACH($books as $value) {

IF($sql <> “”) $sql .= “, “;

$sql .= “(‘”.$value["title"].”‘,’”.$value["n_pages"].”‘)”;

}

$sql = “INSERT INTO table_name(title,n_pages) VALUES”.$sql;

$mysql->strictExecute($sql); //Exécution de la requête par l’objet $mysql

Mettre à jour plusieurs lignes (INSERT INTO… ON DUPLICATE KEY UPDATE)

Mettre à jour des lignes dans une table MySql peut se faire de la manière suivante :

$mysql = Mysql(); //initialisation de l’objet

$mysql->Connect(); //connexion à la BD

$books = array(0=>array(“id”=>1,”title”=>”Mysql explained”,”n_pages”=>”450″),

1=>array(“id”=>2,”title”=>”PHP explained”,”n_pages”=>”450″)

); //lignes à mettre à jour

$sql = “”;

FOREACH($books as $value) {

$sql .= “UPDATE table_name SET title=’”.$value["title"].”‘,n_pages=’”.$value["n_pages"].”‘ WHERE id=’”.$value["id"].”‘”;

$mysql->strictExecute($sql); //Exécution de la requête par l’objet $mysql

}

ce qui produira 2 requêtes.

Il est possible de n’en générer qu’une seule pour mettre à jour les 2 lignes par une requête INSERT INTO avec la syntaxe ON DUPLICATE KEY UPDATE. Dans le cas où la clef existe déjà (ici la valeur id = 1 et id = 2), MySql opérera un update. Si la clef n’existe pas, MySql fera un insert into classique :

$mysql = Mysql(); //initialisation de l’objet

$mysql->Connect(); //connexion à la BD

$books = array(0=>array(“id”=>1,”title”=>”Mysql explained”,”n_pages”=>”450″),

1=>array(“id”=>2,”title”=>”PHP explained”,”n_pages”=>”450″)

); //lignes à modifier

$sql = “”;

FOREACH($books as $value) {

IF($sql <> “”) $sql .= “,”;

$sql .= (‘”.$value["id"].”‘,’”.$value["title"].”‘,’”.$value["n_pages"].”‘);

}

$sql = “INSERT INTO table_name(id,title,n_pages) VALUES”.$sql.” ON DUPLICATE KEY UPDATEid=VALUES(id),title=VALUES(title),n_pages=VALUES(n_pages)”;

$mysql->strictExecute($sql); //Exécution de la requête par l’objet $mysql

Cette requête est tellement efficace qu’elle permet non seulement la mise à jour de plusieurs lignes, mais qu’elle inclut aussi l’insertion de nouvelles données, et ce, en une seule et même requête.

Webliographie :

MySql5 Release notes

MySql5 – Multiple queries

If you want to read more about MySql5 improvements, please read the official release notes.

The aim of this post is to write more efficient MySql queries. Some servers watch over the MySql resource a web page uses. If the number of requests for one single web page is considered too resource-hungry, the developer may receive a warning (or more) from the host!

Read article “PHP5 to watch over MySql queries” if you want PHP to display MySql queries generated in a specific Web page.

What I want to focus on here is a syntax that was introduced in MySql 4.1 but which is still rarely used :

INSERT INTO… ON DUPLICATE KEY UPDATE…

But first, let’s develop multiple queries for deletion.

Deleting several rows in one single query (DELETE)

Instead of dynamically producing queries in order to delete rows in a table :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$array = array(1,2,4); //rows to be deleted

FOREACH($array as $value) {

$sql = “DELETE FROM table_name WHERE id=’$value’”;

$mysql->strictExecute($sql); //Execute query through $mysql object

}

which will produce 3 queries, you can do the following :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$array = array(1,2,4); //rows to be deleted

$sql = “”;

FOREACH($array as $value) {

IF($sql <> “”) $sql .= ” OR “;

$sql .= “id=’$value’”; //adds string id=’$value’ to $sql

}

$sql = “DELETE FROM table_name WHERE “.$sql; //builds the single query : DELETE FROM table_name WHERE id=’1′ OR id=’2′ OR id=’4′;

$mysql->strictExecute($sql); //Execute query through $mysql object

which will produce one single query to DB.

I would even advise using the IN predicate :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$array = array(1,2,4); //rows to be deleted

$sql = “”;

FOREACH($array as $value) {

IF($sql <> “”) $sql .= “, “;

$sql .= “‘$value’”; //adds string id=’$value’ to $sql

}

$sql = “DELETE FROM table_name WHERE id IN (“.$sql.”)”; //builds the single query : DELETE FROM table_name WHERE id IN (’1′, ’2′, ’4′);

$mysql->strictExecute($sql); //Execute query through $mysql object

Inserting several rows in one single query (INSERT TO)

In order to insert multiple rows in one query, do the following :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$books = array( 0=>array(“title”=>”Mysql explained”,”n_pages”=>”350″),

1=>array(“title”=>”PHP explained”,”n_pages”=>”150″)

); //rows to be inserted

$sql = “”;

FOREACH($books as $value) {

IF($sql <> “”) $sql .= “, “;

$sql .= “(‘”.$value["title"].”‘,’”.$value["n_pages"].”‘)”;

}

$sql = “INSERT INTO table_name(title,n_pages) VALUES”.$sql;

$mysql->strictExecute($sql); //Execute query through $mysql object

Updating several rows in one single query (INSERT INTO… ON DUPLICATE KEY UPDATE)

Updating MySql rows can be done this way :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$books = array( 0=>array(“id”=>1,”title”=>”Mysql explained”,”n_pages”=>”450″),

1=>array(“id”=>2,”title”=>”PHP explained”,”n_pages”=>”450″)

); //rows to be inserted

$sql = “”;

FOREACH($books as $value) {

$sql .= “UPDATE table_name SET title=’”.$value["title"].”‘,n_pages=’”.$value["n_pages"].”‘ WHERE id=’”.$value["id"].”‘”;

$mysql->strictExecute($sql); //Execute query through $mysql object

}

which will produce 2 queries.

The trick here is to generate an INSERT INTO query with the ON DUPLICATE KEY UPDATE syntax. In case the key already exists, only an update will take place. If the key doesn’t exist, Mysql will operate a classical insert into query :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$books = array( 0=>array(“id”=>1,”title”=>”Mysql explained”,”n_pages”=>”450″),

1=>array(“id”=>2,”title”=>”PHP explained”,”n_pages”=>”450″)

); //rows to be inserted

$sql = “”;

FOREACH($books as $value) {

IF($sql <> “”) $sql .= “,”;

$sql .= (‘”.$value["id"].”‘,’”.$value["title"].”‘,’”.$value["n_pages"].”‘);

}

$sql = “INSERT INTO table_name(id,title,n_pages) VALUES”.$sql.” ON DUPLICATE KEY UPDATEid=VALUES(id),title=VALUES(title),n_pages=VALUES(n_pages)”;

$mysql->strictExecute($sql); //Execute query through $mysql object

Webliography :

MySql5 Release notes