Déclarer et utiliser des variables dans MySQL

MySQL propose trois mécanismes de variables. Utilisez une variable de session définie par l’utilisateur comme @limit lorsque plusieurs instructions d’une même connexion doivent partager une valeur. Utilisez une variable locale sans @ dans une procédure, une fonction, un trigger ou un événement stocké. Utilisez une variable système comme @@SESSION.sql_mode pour inspecter ou configurer le serveur ou une connexion.

La distinction essentielle est que DECLARE n’est pas une instruction générale utilisable dans une fenêtre SQL. Elle est autorisée uniquement dans un bloc BEGIN ... END et doit précéder les autres instructions de ce bloc. Pour un script rapide, commencez par SET @name = value;. Les mots-clés DECLARE, SET, SELECT ... INTO, BEGIN ... END, SESSION et GLOBAL restent inchangés dans les exemples SQL.

Choisir la bonne variable MySQL

Ces formes ne sont pas interchangeables. En particulier, DECLARE @name INT n’est pas la syntaxe d’une variable locale, et DECLARE name INT échoue comme instruction autonome en dehors d’un programme stocké.

Utiliser une variable définie par l’utilisateur dans une session SQL

Les variables définies par l’utilisateur commencent par @. Il n’est pas nécessaire de les déclarer avant de les utiliser. Affectez une valeur ou une expression avec SET, puis réutilisez-la :

SET @low = 2, @high = 4;
SELECT n FROM demo_numbers WHERE n BETWEEN @low AND @high ORDER BY n;

Ces variables sont privées à la connexion courante. Une autre connexion ne peut pas les utiliser et elles disparaissent lorsque la connexion se termine. Une variable non initialisée vaut NULL ; initialisez donc les variables au début d’un script réutilisable.

SET est la forme d’affectation la plus claire. MySQL accepte aussi := dans une instruction SET :

SET @total = 10;
SET @total := @total + 5;
SELECT @total;

Le type dépend de la valeur affectée et non d’une clause de type. Une variable peut contenir un nombre, un décimal, une chaîne, une chaîne binaire ou NULL. Utilisez des valeurs compatibles et des conversions explicites lorsque le type change.

Charger un résultat avec SELECT ... INTO

Utilisez SELECT ... INTO lorsque la valeur vient d’une requête. Le nombre de variables cibles doit correspondre au nombre d’expressions sélectionnées :

SELECT COUNT(*) INTO @number_of_rows
FROM demo_numbers;

SELECT @number_of_rows;

La requête doit être conçue pour renvoyer une seule ligne. COUNT(*) le garantit naturellement ; une recherche par clé unique convient également. Si plusieurs lignes sont possibles, choisissez une ligne de manière déterministe avec ORDER BY et LIMIT 1.

Une variable utilisateur ne remplace pas un identifiant de table ou de colonne. SET @table_name = 'demo_numbers'; SELECT * FROM @table_name; ne substitue pas le nom de table. Pour du SQL dynamique, construisez une instruction destinée à PREPARE et validez les identifiants fournis par l’application.

MySQL 9.7 accepte encore les affectations en dehors de SET pour la compatibilité, mais cette possibilité est annoncée comme susceptible d’être supprimée. Préférez SET et SELECT ... INTO aux expressions comme SELECT @n := n.

Déclarer une variable locale dans un programme stocké

Les variables locales utilisent DECLARE sans @ et nécessitent un type MySQL. Vous pouvez fournir DEFAULT; sinon la valeur initiale est NULL. Placez les déclarations au début du bloc BEGIN ... END, avant toute instruction exécutable. Elles sont visibles dans ce bloc et les blocs imbriqués, mais pas après le bloc.

La procédure suivante utilise un paramètre d’entrée, une variable locale et un paramètre de sortie. Les commandes DELIMITER appartiennent au client mysql : elles permettent d’envoyer le corps contenant des points-virgules comme une seule instruction CREATE PROCEDURE.

DROP PROCEDURE IF EXISTS count_numbers;
DELIMITER //
CREATE PROCEDURE count_numbers(IN p_min INT, OUT p_count INT)
BEGIN
  DECLARE local_count INT DEFAULT 0;
  SELECT COUNT(*) INTO local_count
  FROM demo_numbers
  WHERE n >= p_min;
  SET p_count = local_count;
END//
DELIMITER ;
CALL count_numbers(3, @result);
SELECT @result;

local_count n’existe que pendant l’exécution de la procédure. Le paramètre OUT est renvoyé par la variable de session fournie à CALL. Si une requête renvoie plusieurs colonnes, prévoyez une cible par expression et gérez l’absence éventuelle de ligne dans la routine.

Vous pouvez déclarer plusieurs variables du même type dans une instruction, mais des déclarations séparées sont souvent plus faciles à maintenir :

DECLARE quantity, retries INT DEFAULT 0;
DECLARE label VARCHAR(100) DEFAULT 'pending';

DECLARE définit aussi des conditions, des curseurs et des gestionnaires. L’ordre est important : variables et conditions avant les curseurs, puis curseurs avant les gestionnaires. Une déclaration placée après SELECT ou SET provoque donc une erreur de syntaxe.

Comprendre la limite de lignes de SELECT ... INTO

Une cible scalaire peut stocker une valeur, pas un ensemble de résultats arbitraire. La requête suivante renvoie volontairement trois lignes et échoue avec l’erreur 1172 :

SET @selected = 99;
SELECT n INTO @selected FROM demo_numbers ORDER BY n;

ERROR 1172 (42000) at line 6: Result consisted of more than one row

Limitez la requête à une ligne connue, agrégez le résultat ou conservez-le sous forme d’ensemble de lignes. LIMIT 1 n’est approprié qu’avec un ordre intentionnel :

SELECT n INTO @selected
FROM demo_numbers
ORDER BY n DESC
LIMIT 1;

La même règle d’une ligne s’applique aux variables locales. Si une routine peut légitimement ne trouver aucune ligne, initialisez la variable et utilisez au besoin un gestionnaire NOT FOUND ou un contrôle d’existence.

Inspecter ou définir une variable système MySQL

Les variables système configurent le serveur ou une connexion client. Inspectez-les avec SHOW VARIABLES ou avec @@ :

SHOW SESSION VARIABLES LIKE 'sql_mode';
SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;

Sans mot-clé de portée, SHOW VARIABLES utilise la session courante. Une valeur de session ne concerne que cette connexion ; une valeur globale initialise généralement les nouvelles connexions. Modifier une variable globale exige le privilège approprié et une variable modifiable.

SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
-- A privileged server administrator may use an appropriate GLOBAL setting.
-- SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';

L’instruction globale commentée n’est volontairement pas incluse dans la fixture exécutable : sa réussite dépend des privilèges et de la portée et de la mutabilité de la variable.

Résoudre le problème d’une variable

• Si DECLARE est refusé, vérifiez qu’il se trouve dans BEGIN ... END et avant toute instruction exécutable. Dans un script ordinaire, utilisez SET @name = value.
• Si la variable contient NULL de façon inattendue, initialisez-la et vérifiez l’expression ou la recherche source.
• Si SELECT ... INTO renvoie l’erreur 1172, faites en sorte que la requête renvoie exactement une ligne ou conservez le résultat comme lignes.
• Qu’une autre connexion ne voie pas @name est normal : la variable est propre à la session.
• Si @@GLOBAL.name échoue, vérifiez la mutabilité de la variable et vos privilèges administratifs.

La fixture de réussite affecte des variables de session, les réutilise dans un filtre, déclare une variable locale dans une procédure et renvoie son paramètre de sortie. La fixture limite vérifie le diagnostic de plusieurs lignes de SELECT ... INTO; les deux utilisent un serveur MySQL temporaire.

Consultez la référence MySQL des variables utilisateur, la référence de DECLARE local et la référence d’affectation avec SET pour la syntaxe complète.