Declarar e usar variáveis no MySQL

O MySQL tem três mecanismos de variáveis. Use uma variável de sessão definida pelo utilizador, como @limit, quando várias instruções na mesma ligação precisam de partilhar um valor. Use uma variável local sem @ numa procedure, função, trigger ou evento armazenado. Use uma variável do sistema como @@SESSION.sql_mode para consultar ou configurar o servidor ou uma ligação.

A distinção principal é que DECLARE não é uma instrução geral para uma janela SQL. Só é permitida dentro de um bloco BEGIN ... END e deve aparecer antes das outras instruções desse bloco. Para um script rápido, comece com SET @name = value;. As palavras-chave DECLARE, SET, SELECT ... INTO, BEGIN ... END, SESSION e GLOBAL permanecem iguais no SQL.

Escolher a variável MySQL correta

Estas formas não são intercambiáveis. DECLARE @name INT não é a sintaxe de uma variável local, e DECLARE name INT falha como instrução isolada fora de um programa armazenado.

Usar uma variável definida pelo utilizador numa sessão SQL

As variáveis definidas pelo utilizador começam por @. Não é necessário declará-las antes de as utilizar. Atribua um valor ou expressão com SET e use a variável numa instrução posterior:

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

As variáveis pertencem à ligação atual. Outra ligação não as pode utilizar e elas desaparecem quando a ligação termina. Uma variável não inicializada resulta em NULL; inicialize-a no início de um script reutilizável.

SET é a forma de atribuição mais clara. O MySQL também aceita := numa instrução SET:

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

O tipo depende do valor atribuído, não de uma declaração de tipo. A variável pode conter números, decimais, cadeias, cadeias binárias ou NULL; use valores compatíveis e conversões explícitas quando o tipo mudar.

Carregar um resultado com SELECT ... INTO

Use SELECT ... INTO quando o valor vem de uma consulta. O número de variáveis de destino deve corresponder ao número de expressões selecionadas:

SELECT COUNT(*) INTO @number_of_rows
FROM demo_numbers;

SELECT @number_of_rows;

A consulta deve ser concebida para devolver uma linha. COUNT(*) faz isso naturalmente e uma pesquisa por chave única também é adequada. Se forem possíveis várias linhas, escolha uma linha de modo determinístico com ORDER BY e LIMIT 1.

Uma variável não substitui um nome de tabela ou de coluna. SET @table_name = 'demo_numbers'; SELECT * FROM @table_name; não substitui o nome da tabela. Para SQL dinâmico, crie uma instrução para PREPARE e valide os identificadores fornecidos pela aplicação.

O MySQL 9.7 ainda permite atribuições fora de SET por compatibilidade, mas indica que esse comportamento será removido. Prefira SET e SELECT ... INTO a expressões como SELECT @n := n.

Declarar uma variável local num programa armazenado

As variáveis locais usam DECLARE sem @ e precisam de um tipo de dados MySQL. Pode indicar DEFAULT; caso contrário, o valor inicial é NULL. Coloque as declarações no início do bloco BEGIN ... END, antes das instruções executáveis. Elas são visíveis nesse bloco e nos blocos aninhados, mas não depois dele.

O procedimento seguinte usa um parâmetro de entrada, uma variável local e um parâmetro de saída. Os comandos DELIMITER pertencem ao cliente mysql e permitem enviar o corpo, que contém ponto e vírgula, como uma única instrução 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 existe apenas enquanto o procedimento é executado. O parâmetro OUT é devolvido através da variável de sessão fornecida ao CALL. Se uma consulta devolver várias colunas, forneça um destino por expressão e trate o caso sem correspondências.

Pode declarar várias variáveis do mesmo tipo numa instrução, mas declarações separadas costumam ser mais fáceis de manter:

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

DECLARE também define condições, cursores e handlers. A ordem é importante: variáveis e condições antes dos cursores, e cursores antes dos handlers. Por isso, uma declaração depois de SELECT ou SET provoca um erro de sintaxe.

Entender o limite de linhas de SELECT ... INTO

Um destino escalar armazena um valor, não um conjunto de resultados arbitrário. A consulta seguinte devolve intencionalmente três linhas e falha com o erro 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

Limite a consulta a uma linha conhecida, agregue o resultado ou mantenha-o como um conjunto de linhas. LIMIT 1 só é adequado com uma ordenação intencional:

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

A mesma regra de uma linha aplica-se a uma variável local. Se uma rotina puder não encontrar nenhuma linha, inicialize a variável de propósito e use um handler NOT FOUND ou uma verificação de existência quando necessário.

Consultar ou definir uma variável do sistema do MySQL

As variáveis do sistema configuram o servidor ou uma ligação de cliente. Consulte-as com SHOW VARIABLES ou com @@:

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

Sem uma palavra de escopo, SHOW VARIABLES usa a sessão atual. Um valor de sessão afeta apenas essa ligação; um valor global normalmente inicializa novas ligações. Alterar uma variável global exige o privilégio adequado e uma variável modificável.

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

A instrução GLOBAL comentada não faz parte da fixture executável: o sucesso depende dos privilégios e do escopo e da possibilidade de alteração da variável.

Resolver uma variável que não funciona

• Se DECLARE for rejeitado, confirme que está dentro de BEGIN ... END e antes de todas as instruções executáveis. Num script normal, use SET @name = value.
• Se surgir NULL inesperadamente, inicialize a variável e verifique a expressão ou a consulta de origem.
• Se SELECT ... INTO der o erro 1172, faça a consulta devolver exatamente uma linha ou mantenha o resultado como linhas.
• É esperado que outra ligação não veja @name, porque as variáveis são específicas da sessão.
• Se @@GLOBAL.name falhar, verifique a possibilidade de alteração da variável e os seus privilégios administrativos.

A fixture de sucesso atribui variáveis de sessão, reutiliza-as num filtro, declara uma variável local numa procedure e devolve o parâmetro de saída. A fixture de limite verifica o diagnóstico de várias linhas de SELECT ... INTO; ambas usam um servidor MySQL temporário.

Consulte a referência de variáveis definidas pelo utilizador do MySQL, a referência de DECLARE local e a referência de atribuição com SET.