• Facile

Ce cours est visible gratuitement en ligne.

Vous pouvez être accompagné et mentoré par un professeur particulier par visioconférence sur ce cours.

J'ai tout compris !

Mis à jour le 21/11/2013

Les variables utilisateur

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

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 !

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.

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 :

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.

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 :

SELECT @mavariable;

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

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

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:

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 :

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.

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 :

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é. ^^

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

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 !

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

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 :

SELECT COUNT(*) FROM livreor;

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

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 :

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

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

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 :

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

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 :

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 ...

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 :

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 :

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 :

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

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 !

Exemple de certificat de réussite
Exemple de certificat de réussite