Aller au menu - Aller au contenu

Icône Les variables utilisateur

Par Avatar Shepard
Mise à jour : 29/06/2009
1 063 visites depuis 7 jours, dont 82 sur ce chapitre classé 122/786
Voici un chapitre qui devrait en intéresser plus d'un !

Ce chapitre vous permettra de faire plein de trucs géniaux, de plus, très peu savent ce que sont les variables utilisateur, donc profitez en : même M@teo ne savait pas ce que c'était avant de lire ce tuto :D :p (pour ceux qui voudraient savoir comment moi je l'ai appris, dites-vous simplement que je me suis farci toute la doc ^^ ).

Bon amusement !
Retour en haut
Sommaire du chapitre :
Icône du chapitre
Chapitre précédent Sommaire Chapitre suivant
Retour en haut

Variables utilisateur ?

Vous vous demandez sans doute ce que c'est :p .

En fait il s'agit simplement de variables, exactement comme dans PHP, à quelques différences près :

  • Une variable commence par @ et est composée de caractères alphanumériques et de tirets bas "_" (on peut également utiliser $ et . mais je vous déconseille d'utiliser ces deux signes car ils créent des confusions).
  • Les variables ne doivent pas être déclarées : elles valent NULL par défaut.
  • Les variables peuvent être définies grâce à SET ou à l'aide de l'opérateur :=
  • Une variable peut contenir un entier, un réel ou une chaîne de caractères.
  • Toute variable est automatiquement fermée à la fin d'une session.
Retour en haut

Déclarer et utiliser une variable

Il y a plusieurs façons de définir une variable, il n'y en a pas une "meilleure" que l'autre, on utilise l'une dans certains cas, et l'autre dans d'autres.

Déclarer une variable avec SET



SET permet de déclarer une variable. Par exemple :

Code : SQL
1
SET @une_variable = 1;


On peut également utiliser une requête comme valeur de cette variable (en fait ce qu'on va faire s'appelle une sous-requête, mais on apprendra ça un peu plus tard :) ).

Ne vous préoccupez pas trop de ce code, essayez juste de le comprendre, nous apprendrons plus tard la signification de chacunes de ces lignes.

Code : SQL
1
2
3
4
5
6
CREATE DATABASE tests;
USE tests;
CREATE TABLE test ( a int );
INSERT INTO test VALUES (1), (2), (3), (4), (6), (9), (19), (34), (72);

SET @a := ( SELECT COUNT(*) FROM test ); -- Définition d'une variable grâce à une sous-requête

Image utilisateur


Sélectionner une variable avec SELECT



Pour sélectionner une variable, rien de plus simple, il suffit de faire :
Code : SQL
1
SELECT @mavariable;


Nous venons de définir deux variables : @une_variable et @a, regardons maintenant ce qu'elles contiennent ...

Code : SQL
1
SELECT @une_variable, @a;

Image utilisateur


Mais que vaut une variable non définie, par exemple @aetuo (pour ceux qui se demandent d'où ça vient, c'est azertyuiop en tirant une lettre sur deux :D ) :

Code : SQL
1
SELECT @aetuo;

Image utilisateur


Comme vous le voyez, ça ne renvoie pas d'erreur, ça ne renvoie tout simplement... rien. o_O

En fait une valeur est bien renvoyée : la valeur NULL, cette valeur est l'objet du prochain chapitre, donc je ne détaillerai pas plus ce phénomène ici. ;)

(re)Définir une variable avec SELECT



On peut également donner une valeur à une variable grâce à SELECT (ou UPDATE ou INSERT ou DELETE mais nous ne parlerons que de SELECT, sachez juste que c'est possible).

On définit une variable grâce à l'opérateur := (comme en Delphi pour ceux qui connaissent). La plupart du temps, les variables sont définies la première fois avec SET puis manipulées avec SELECT, mais il n'est pas rare que des exceptions enfreignent cette règle.

Définissons @a à 4:
Code : SQL
1
SELECT @a := 4;

Image utilisateur


Comme vous le voyez, SELECT renvoie directement la valeur vu que @a := 4 est considéré comme une colonne. On aurait très bien pu rajouter un AS pour redéfinir le nom de cette colonne.

Tiens à votre avis, est-il possible de donner à une colonne un nom contenu dans une variable ? Faisons le test :
Code : SQL
1
2
SET @nom = "colonne";
SELECT 4 AS @nom;

Image utilisateur


Comme vous le voyez, ça ne fonctionne malheureusement pas. C'est dommage, car ça aurait ouvert des voies pour la linéarisation de données (lignes en colonnes ou colonnes en lignes). Si toutefois quelqu'un trouve une astuce pour contourner ce problème, je lui serais fortement reconnaissant de me dire comment faire.
Retour en haut

Mini-TP : un classement

Le problème ...



Enormément de choses sont possibles grâce aux variables utilisateur ! Cependant la plupart des applications possibles demandent plus de connaissances que ce que vous êtes censé savoir. Il y a cependant une chose que vous devriez être capable de faire en cherchant un peu : un système de classement !

Le but ? Je vous donne une table avec des pseudos associés à des points, vous devez réussir à donner une place à chacun de ces pseudos en fonction de son nombre de points. Chaque pseudo a un nombre de points différents (pas d'égalité donc), en effet la gestion des ex-aequos rendrait l'exercice bien trop difficile pour votre niveau actuel. :p

Quelques indices supplémentaires :
  • Pensez à ORDER BY que vous avez appris à utiliser dans le cours de M@teo.
  • SET et l'opérateur := vous seront normalement nécessaires.


Voici les requêtes pour la création de la table :
Code : SQL
1
2
CREATE TABLE joueurs ( pseudo VARCHAR(30), points INT );
INSERT INTO joueurs ( pseudo, points ) VALUES ( 'Shepard', 13854 ), ( 'Alexi', 4251 ), ( 'Janne', 11245 ), ( 'Shagrath', 1248 ), ( 'Fred', 42857 );


Vous devez obtenir ce tableau :
Image utilisateur


Il ne me reste plus qu'à vous souhaiter bonne chance !

Une solution



En SQL, il n'y a jamais qu'un seul moyen de faire, ce qu'il y a dans la case secrète représente donc une solution, peut-être que la vôtre est radicalement différente (ça m'étonnerait un peu quand même mais bon :p ), mais ce qui compte à ce niveau, c'est que ça fonctionne ! Le principal c'est d'avoir cherché et d'être tombé sur quelque chose de potable, si vous n'y êtes pas arrivé, ne vous inquiétez pas : c'était le premier et il y en aura sans doute d'autres. ;) :)

Bon, assez blablaté. ^^

Secret (cliquez pour afficher)

Code : SQL
1
2
SET @place = 0;
SELECT @place := @place + 1 AS place, pseudo, points FROM joueurs ORDER BY points DESC;

Image utilisateur


Comme vous le voyez, les requêtes ne sont pas longues du tout (ce n'est pas pour rien que je disais que c'était de votre niveau :-° ), néanmoins il fallait y penser ! Je félicite ceux qui ont trouvé, mine de rien ce n'était pas si facile !
Retour en haut

Un autre exemple : sélection au hasard

J'ai hésité à ajouter cette sous-partie à ce chapitre. En effet, elle fait appel à des notions dont vous n'avez certainement jamais entendu parler, je l'ai ajoutée quand même, dites-vous qu'il ne s'agit que d'un exemple, si vous ne comprenez pas tout à fait, ne vous inquiétez pas, on reviendra plus tard dans le cours sur tout ce qui sera montré ici. ;)


Le problème



On a une table nommée livreor (id, auteur, message) qui contient des messages contenus dans un livre d'or, on aimerait sélectionner un message au hasard. Chaque message a un identifiant unique.

On peut distinguer deux cas à ce stade :

  • Les identifiants sont continus (1, 2, 3, 4,...). Dans ce cas il est très facile de résoudre notre problème, mais en fait il est très rare que les identifiants soient continus. La plupart du temps, des messages ont été supprimés par un administrateur (messages de test lors du développement du site, messages malvenus, etc).
  • On voit donc apparaître le deuxième cas : les identifiants ne sont pas continus (2, 3, 6, 8,...) dans ce cas le problème devient plus compliqué à résoudre, et c'est là que les variables de MySQL entreront en jeu. :)


Cas 1 : les identifiants sont continus



Code : SQL
1
2
3
4
5
6
7
8
CREATE TABLE livreor ( id INT AUTO_INCREMENT, auteur VARCHAR(30), message TEXT, PRIMARY KEY(id) );
INSERT INTO livreor ( auteur, message )
VALUES 
        ( 'Shepard', 'Tres bon site :p' ),
        ( 'Alexi', 'Vive Children of Bodom !' ),
        ( 'Shepard', 'Entierement d''acord, Alexi :)' ),
        ( 'Janne', 'Vive le synthe :-°' ),
        ( 'Alexi','N''importe quoi ! C''est la guitare qui est mieux :D' );


Rien que dans ce cas-là, les variables MySQL sont déjà très utiles, je vous conseille de bien comprendre ce cas-ci car sinon vous risquez de vraiment avoir du mal pour le second... :p

En fait, il suffit de trouver un nombre au hasard pris entre 1 et n, n étant le nombre de messages.

On peut récupérer très facilement n grâce à la requête suivante :

Code : SQL
1
SELECT COUNT(*) FROM livreor;


Ensuite on va utiliser la fonction RAND() pour prendre un nombre au hasard entre 0 et n - 0.000(...)01 :

Code : SQL
1
SELECT RAND() * COUNT(*) FROM livreor;


Utilisons maintenant la fonction FLOOR pour arrondir à l'entier inférieur. On aura alors un nombre entre 0 et n - 1. Il faudra donc ajouter un au résultat pour avoir un nombre entre 1 et n :

Code : SQL
1
SELECT FLOOR( RAND() * COUNT(*) ) + 1 FROM livreor;


Il suffit maintenant de prendre le message ayant pour identifiant le résultat de cette requête :

Code : SQL
1
SELECT auteur, message FROM livreor WHERE id = FLOOR( RAND() * ( SELECT COUNT(*) FROM livreor ) ) + 1;


Oui mais non ! Il y a un petit problème... RAND() est réévalué à chaque ligne, ce qui fait qu'avec cette requête, on peut avoir des bizarreries de ce genre là :
Image utilisateur


Comme vous le voyez, ça ne marche pas toujours, des fois on a 3 lignes, et des fois on n'en a pas du tout !

Pour y remédier, il suffit de stocker RAND() quelque part... Où ça ? Dans une variable pardi ! Sauf qu'au lieu de juste stocker le RAND(), on va y mettre tout le FLOOR(...) + 1. Ainsi on aura un nombre entier (plus rapide à stocker) et la requête sera simplifiée :

Code : SQL
1
2
SET @id_hasard = FLOOR( RAND() * ( SELECT COUNT(*) FROM livreor ) ) + 1;
SELECT auteur, message FROM livreor WHERE id = @id_hasard;

Image utilisateur


Bien sûr, il faut réexécuter le SET avant chaque SELECT, sinon @id_hasard garde la même valeur et on obtient toujours le même message.

Et le tour est joué ! (J'espère que jusque là vous avez suivi parce que les choses vont se compliquer à partir de maintenant :p ).


Cas 2 : les identifiants sont discontinus



Code : SQL
1
2
3
4
5
6
7
8
9
DROP TABLE livreor;
CREATE TABLE livreor ( id INT AUTO_INCREMENT, auteur VARCHAR(30), message TEXT, PRIMARY KEY(id) );
INSERT INTO livreor ( id, auteur, message )
VALUES 
        ( 2, 'Shepard', 'Tres bon site :p' ),
        ( 3, 'Alexi', 'Vive Children of Bodom !' ),
        ( 6, 'Shepard', 'Entierement d''acord, Alexi :)' ),
        ( 9, 'Janne', 'Vive le synthe :-°' ),
        ( 12, 'Alexi','N''importe quoi ! C''est la guitare qui est mieux :D' );


Là ça se complique un peu : si on prend un nombre au hasard entre 1 et le nombre de lignes, toutes les dernières lignes ne seront pas prises en compte et les "trous" pourraient être sélectionnés, donc ça ne va vraiment pas.

Une autre solution serait de prendre un nombre au hasard entre 1 et le dernier id de la table, puis de prendre l'id en dessous de ce nombre qui se rapproche le plus de ce nombre, mais je n'aime pas cette technique, tout d'abord parce que s'il y a des "gros" trous (suppression de 2000 messages d'affilée par exemple), le message juste avant ce trou reviendra plus souvent que les autres, et ensuite parce que cette méthode est plus compliquée que celle que je vais vous montrer. :p

Il y a une astuce : créer une table temporaire (donc qui ne dure que le temps de la session) avec une colonne contenant des chiffres continus (donc on retombe dans le premier cas), plus une autre colonne contenant les "vrais" id.

Pour créer cette table, rien de plus simple : on se base sur le principe du classement, sauf qu'il n'est plus nécessaire de préciser le ORDER BY :

Code : SQL
1
2
SET @place = 0;
CREATE TEMPORARY TABLE temp_livreor AS SELECT @place := @place + 1 AS place, id FROM livreor;


Histoire de nous assurer que tout fonctionne bien ...
Code : SQL
1
SELECT * FROM temp_livreor;

Image utilisateur

Remarquez qu'en général, je n'utilise jamais l'étoile qui permet de sélectionner tous les champs d'un coup, si je le fait ici, c'est parce que je veux m'assurer qu'il n'y ait rien en trop. :)

A partir de maintenant c'est facile : il suffit d'utiliser la même technique que tout à l'heure pour choisir une entrée de la table temp_livreor, puis de prendre le "vrai" id qui lui correspond, et d'afficher les infos correspondant à cet id. :)

Par exemple :

Code : SQL
1
2
3
SET @place_hasard = ( SELECT FLOOR( RAND() * ( SELECT COUNT(*) FROM temp_livreor ) ) + 1 );
SET @id_hasard = ( SELECT id FROM temp_livreor WHERE place = @place_hasard );
SELECT auteur, message FROM livreor WHERE id = @id_hasard;


Ou encore :

Code : SQL
1
2
SET @place_hasard = ( SELECT FLOOR( RAND() * ( SELECT COUNT(*) FROM temp_livreor ) ) + 1 );
SELECT auteur, message FROM livreor WHERE id = ( SELECT id FROM temp_livreor WHERE place = @place_hasard );


Il est également possible de le faire avec une jointure :

Code : SQL
1
2
SET @place_hasard = ( SELECT FLOOR( RAND() * ( SELECT COUNT(*) FROM temp_livreor ) ) + 1 );
SELECT auteur, message FROM livreor NATURAL JOIN temp_livreor WHERE place = @place_hasard;


La meilleure solution est la dernière, mais les deux autres sont évidemment tout à fait valables :) (ne vous inquiétez pas si vous ne comprenez pas les deux dernières requêtes : c'est normal ^^ ).

Les trois techniques fonctionnent et renvoient le même message si on ne modifie pas @place_hasard (évidemment). Voici ce que ça donne dans la console :

Image utilisateur


Voilà, j'admet que cette sous-partie était ardue et impossible à comprendre en entier si vous n'aviez lu que le cours de M@teo. Je ne peux que vous conseiller de venir le relire une fois que vous saurez ce que sont les jointures, les sous-requêtes et les tables temporaires. ;) :)
Retour en haut

Q.C.M.

Par quel signe commencent les variables utilisateur ?
Quelle est la valeur d'une variable non déclarée ?
Quelle est la durée de vie d'une variable utilisateur ?

Statistiques de réponses au QCM

Retour en haut
Alors ? C'était sympa comme matière non ?

Bien qu'on n'ait vu que quelques utilisations de ces variables, il faut bien vous rendre compte que les possibilités sont énormes ! MySQL est l'un des seuls SGBDR à inclure ce type de fonctionnalité, donc faites attention si vous comptez changer de moteur par la suite : vos requêtes ne seront pas compatibles !

Pour moi, les variables utilisateur sont l'une des choses qu'il manque à la norme SQL, bien sûr il faudrait les améliorer un peu (pouvoir renommer une colonne selon la valeur d'une variable par exemple :p ), mais ce serait sans aucun doute une forte avancée pour le monde de SQL. Je trouve dommage qu'elles soient si peu utilisées, leur potentiel est sous-estimé, j'espère que bientôt, les développeurs se rendront compte des fonctionnalités qu'elles permettent !
Chapitre précédent Sommaire Chapitre suivant
Retour en haut

Partager

Retour en haut
23 commentaires pour "Les variables utilisateur"
Note moyenne : 3.87 / 4 (31 votes)
Pseudo Commentaire
Hors ligne Max-P # Posté le 11/10/2009 à 01:10:19
Avatar

Ville : Beaupré
Pays : Canada

 Excélent tuto, j'ai juste une remarque sur la dernière partie (sélection au hasard)...

Au lieu de créer des tables temporaires et d'y stoquer des données et utiliser des variables, on aurait pas pu faire un simple

Code : SQL
1
SELECT * FROM `ma_table` ORDER BY RAND()


à la place?

À moins que cette complication soit faite pour le sujet de tuto, dans ce cas désolé :p
Error 404 - Signature not found!
/me est en train de se construire progressivement un nouvel hébergeur, successeur à espace-gratuit.org avec un panel custom robuste et puissant! Intéressé? Envoyez un MP!
 
Hors ligne maestro982 # Posté le 02/02/2010 à 19:32:13

Saluut niquel le tuto! ;)

Par contre comment est ce qu'on fait pour inclure ca dans du php??
Je dois transmettre une variable se trouvant dans mon select, vers une page cible!

Code : SQL
1
2
SET @id_question =  SELECT id_question FROM table1 ORDER BY RAND() ;
SELECT * FROM table1 WHERE  id_question=@id_question;


en fait mon select est deja dans mon code avec:
Code : PHP
1
2
3
$reponse = mysql_query('SELECT * FROM table1 WHERE  id_question=@id_question');

$donnees = mysql_fetch_array($reponse);



mais je sais pas quoi mettre devant le SET.
et dans la page cible je recupere comment le @id_question???? Avec un $_POST['id_question']??
Merci de vos conseill!!!
A bientôt!
Hors ligne maxima # Posté le 18/07/2010 à 14:39:05
C’est pas faux.
Avatar

Avis : Très bon

Études : Lycée du Parc - Lyon

 @toto > Tu peux utiliser un logiciel comme Navicat Lite, où tu pourras avoir une console aussi belle ;)

Sinon, très bon tuto, rien à redire :-°
Hors ligne C4stor # Posté le 05/07/2011 à 08:48:51

Avis : Très bon

Études : Ecole Centrale de Paris

 Excellent tuto, je redécouvre le SQL,qui jusque là était le parent pauvre de mes programmes ! Vraiment fou toutes ces possibilités inattendues :-)
Si votre problème est résolu, n'oubliez pas de le marquer comme tel ^^
 
Hors ligne Cortexd # Posté le 29/07/2011 à 03:52:18
I drink your milkshake!
Avatar

Ville : Vauréal
Pays : France métropolitaine

 Cette requête ne fonctionne plus avec les dernières versions de MySQL (depuis assez longtemps en fait) :

Code : SQL
1
2
SET @place = 0;
SELECT @place := @place + 1 AS place, pseudo, points FROM joueurs ORDER BY points DESC;


Les différentes valeurs de la variable sont assignées avant le ORDER BY, du coup les places sont mélangées dans le résultat final.
« Chaque chevalier parle de ses armes. »
Image utilisateur Arrêt sur image
 

Voir tous les commentaires