Dichiarare e usare le variabili in MySQL

MySQL offre tre meccanismi per le variabili. Usa una variabile di sessione definita dall’utente, ad esempio @limit, quando più istruzioni della stessa connessione devono condividere un valore. Usa una variabile locale senza @ in una procedura, funzione, trigger o evento memorizzato. Usa una variabile di sistema come @@SESSION.sql_mode per controllare o configurare il server o una connessione.

La distinzione più importante è che DECLARE non è un’istruzione generica per una finestra SQL. È consentita solo dentro un blocco BEGIN ... END e deve comparire prima delle altre istruzioni del blocco. Per uno script rapido, inizia con SET @name = value;. Le parole chiave DECLARE, SET, SELECT ... INTO, BEGIN ... END, SESSION e GLOBAL restano invariate nel codice SQL.

Scegliere la variabile MySQL corretta

Queste forme non sono intercambiabili. In particolare, DECLARE @name INT non è la sintassi di una variabile locale e DECLARE name INT fallisce come istruzione autonoma fuori da un programma memorizzato.

Usare una variabile definita dall’utente in una sessione SQL

Le variabili definite dall’utente iniziano con @. Non devi dichiararle prima dell’uso. Assegna un valore o un’espressione con SET e usa la variabile in un’istruzione successiva:

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

Le variabili appartengono alla connessione corrente. Un’altra connessione non può usarle e scompaiono quando la connessione termina. Una variabile non inizializzata restituisce NULL, quindi inizializzala all’inizio di uno script riutilizzabile.

SET è la forma di assegnazione più chiara. MySQL accetta anche := in un’istruzione SET:

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

Il tipo dipende dal valore assegnato, non da una clausola di tipo. Una variabile può contenere numeri, decimali, stringhe, stringhe binarie o NULL; usa valori compatibili e conversioni esplicite quando cambia il tipo.

Caricare un risultato con SELECT ... INTO

Usa SELECT ... INTO quando il valore proviene da una query. Il numero delle variabili di destinazione deve corrispondere al numero delle espressioni selezionate:

SELECT COUNT(*) INTO @number_of_rows
FROM demo_numbers;

SELECT @number_of_rows;

La query dovrebbe restituire una sola riga. COUNT(*) lo garantisce naturalmente; anche una ricerca per chiave univoca è adatta. Se sono possibili più righe, scegli quella desiderata con ORDER BY deterministico e LIMIT 1.

Una variabile non può essere usata come identificatore di tabella o colonna. SET @table_name = 'demo_numbers'; SELECT * FROM @table_name; non sostituisce il nome della tabella. Per SQL dinamico, costruisci una query per PREPARE e valida gli identificatori forniti dall’applicazione.

MySQL 9.7 consente ancora assegnazioni al di fuori di SET per compatibilità, ma ne prevede la rimozione futura. Preferisci SET e SELECT ... INTO a espressioni come SELECT @n := n.

Dichiarare una variabile locale in un programma memorizzato

Le variabili locali usano DECLARE senza @ e richiedono un tipo di dati MySQL. Puoi indicare DEFAULT; altrimenti il valore iniziale è NULL. Metti le dichiarazioni all’inizio del blocco BEGIN ... END, prima delle istruzioni eseguibili. Sono visibili nel blocco e nei blocchi annidati, ma non dopo la fine del blocco.

La procedura seguente usa un parametro di ingresso, una variabile locale e un parametro di uscita. I comandi DELIMITER appartengono al client mysql e permettono di inviare il corpo, che contiene punti e virgola, come una singola istruzione 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 esiste solo durante l’esecuzione della procedura. Il parametro OUT viene restituito tramite la variabile di sessione passata a CALL. Se una query restituisce più colonne, usa una destinazione per ogni espressione e gestisci il caso in cui non vi siano righe corrispondenti.

Puoi dichiarare più variabili dello stesso tipo in un’unica istruzione, anche se dichiarazioni separate sono spesso più semplici da mantenere:

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

DECLARE definisce anche condizioni, cursori e handler. L’ordine è importante: variabili e condizioni prima dei cursori, cursori prima degli handler. Per questo una dichiarazione dopo SELECT o SET causa un errore di sintassi.

Capire il limite di righe di SELECT ... INTO

Una destinazione scalare può contenere un valore, non un insieme arbitrario di risultati. La query seguente restituisce intenzionalmente tre righe e fallisce con l’errore 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

Limita la query a una riga nota, aggrega il risultato oppure mantienilo come insieme di righe. LIMIT 1 è appropriato solo con un ordinamento intenzionale:

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

La stessa regola di una riga vale per una variabile locale. Se una routine può non trovare righe, inizializza la variabile in modo esplicito e usa, quando serve, un handler NOT FOUND o un controllo di esistenza.

Ispezionare o impostare una variabile di sistema MySQL

Le variabili di sistema configurano il server o una connessione client. Ispezionale con SHOW VARIABLES o con un qualificatore @@:

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

Senza una parola chiave di ambito, SHOW VARIABLES usa la sessione corrente. Un valore di sessione riguarda solo quella connessione; un valore globale inizializza in genere le nuove connessioni. Per modificare una variabile globale servono il privilegio appropriato e una variabile modificabile.

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’istruzione globale commentata non fa parte della fixture eseguibile: il risultato dipende dai privilegi e dall’ambito e dalla modificabilità della variabile.

Risolvere una variabile che non funziona

• Se DECLARE viene rifiutato, controlla che sia dentro BEGIN ... END e prima di ogni istruzione eseguibile. In uno script normale usa SET @name = value.
• Se compare NULL inaspettatamente, inizializza la variabile e controlla l’espressione o la ricerca d’origine.
• Se SELECT ... INTO restituisce l’errore 1172, fai in modo che la query restituisca una sola riga o conserva il risultato come righe.
• È normale che un’altra connessione non veda @name: le variabili sono specifiche della sessione.
• Se @@GLOBAL.name non può essere modificato, verifica la mutabilità della variabile e i privilegi amministrativi.

La fixture di successo assegna variabili di sessione, le riusa in un filtro, dichiara una variabile locale in una procedura e restituisce il parametro di uscita. La fixture limite verifica il messaggio per più righe di SELECT ... INTO; entrambe usano un server MySQL temporaneo.

Per la sintassi completa, consulta la referenza MySQL sulle variabili definite dall’utente, la referenza di DECLARE locale e la referenza sull’assegnazione con SET.