Декларация и использование переменных в MySQL: примеры

Быстрый ответ

Переменную в MySQL можно определить с помощью команды SET вот так:

SET @var := 'value';

Также для этой цели можно использовать SELECT:

SELECT 'value' INTO @var;

Теперь переменная @var содержит значение 'value'.

Область видимости: сеансовые переменные против локальных переменных

В MySQL существуют сеансовые переменные и локальные переменные.

Сеансовые переменные: @var

Сеансовые переменные уникальны для каждого соединения. Для объявления и присваивания значения сеансовой переменной можно использовать команды SET или SELECT.

SET @my_var := 'session value';

Символ @ ставится перед именем сеансовой переменной.

Локальные переменные: var

Локальные переменные ограничены областью видимости хранимых процедур или блоков BEGIN ... END. Для объявления локальной переменной используется ключевое слово DECLARE, и, в отличие от сеансовых переменных, префикс @ тут не применяется.

BEGIN
  DECLARE local_var VARCHAR(100);
  SET local_var = 'local value';
END;

Локальные переменные видимы только в блоке их объявления.

Использование настроек сервера с помощью SET GLOBAL/SESSION

Установка параметров сервера во время работы выполняется командами SET GLOBAL или SET SESSION.

SET GLOBAL max_connections = 500;
SET SESSION sql_mode = 'TRADITIONAL';

Настройки сервера можно просмотреть с помощью SHOW VARIABLES или запросить значение конкретной переменной через SELECT @@var_name.

Работа с пользовательскими переменными

Пользовательские переменные связаны с текущим соединением. Их типизация слабо сформализована, что делает их удобными для использования в различных операциях.

Прямое присвоение значений

Значение переменной можно установить с помощью SET:

SET @var = 'value';

Присвоение значений через SELECT

Значения также можно присваивать через SELECT:

SELECT 'value' INTO @var_name;

Сохранение результатов запросов

Результаты запроса SELECT можно сохранить в переменной:

SELECT column INTO @var FROM table WHERE condition;

Использование переменных в условии WHERE

Переменные удобно использовать в условии WHERE для создания динамических фильтров:

SET @id = 5;
SELECT * FROM table WHERE id = @id;

Обработка NULL значений

Для обработки NULL значений используйте функцию IFNULL(@var, default_value):

SET @might_be_null := NULL;
SELECT IFNULL(@might_be_null, "Значение по умолчанию");

Использование переменных в триггерах

Переменные также применяются в триггерах для обновления полей:

CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
   SET NEW.column_name = @var_name;
END;

Визуализация

Создание переменной в MySQL можно представить как построение невидимого сундука:

SET @my_secret = 'treasure';

Чтобы увидеть содержимое сундука:

SELECT @my_secret;

Каждая сеансовая переменная – это некий именной невидимый сундука в мире SQL.

Продвинутые советы

Некоторые дополнительные приемы могут оказаться полезными при решении сложных задач.

Присваивание значений нескольким переменным

Объявить и присвоить значения сразу нескольким переменным можно c помощью одного выражения:

SET @a = 10, @b = @a + 5, @c = @b * @a;

Подготовленные выражения

В подготовленных выражениях разрешается использовать пользовательские переменные для создания динамических SQL-запросов:

SET @s = 'SELECT SQRT(POW(?, 2) + POW(?, 2)) AS гипотенуза';
PREPARE stmt FROM @s;
SET @a = 3;
SET @b = 4;
EXECUTE stmt USING @a, @b;

Совершенствование JOIN

Становится важным обратить внимание на использование переменных в JOIN, чтобы исключить полное сканирование таблиц:

SELECT *
FROM tbl JOIN (
  SELECT @rownum := @rownum + 1 AS ранг, results.*
  FROM (SELECT @rownum := 0) r, tbl
  WHERE condition1
  ORDER BY col1
) results USING (col2)
WHERE results.ранг = tbl.ранг;

Полезные материалы

1. Руководство по MySQL 8.0: Определенные пользователем переменные — официальное руководство MySQL по переменным.
2. Stack Overflow: Как объявить переменную в MySQL? — подробное обсуждение в рамках сообщества.
3. User-Defined Variables – MariaDB Knowledge Base — подробная информация о переменных MariaDB, применимая к MySQL.