MySQL で変数を宣言して使用する方法

MySQL には変数を扱う3つの仕組みがあります。同じ接続内の複数の SQL 文で値を共有するなら、@limit のようなユーザー定義セッション変数を使います。ストアドプロシージャ、関数、トリガー、イベント内では @ のないローカル変数を使います。サーバーまたは1つの接続を調べたり設定したりする場合は、@@SESSION.sql_mode のようなシステム変数を使います。

重要なのは、DECLARE は SQL ウィンドウで自由に使える一般文ではないことです。BEGIN ... END 複合文の中だけで使用でき、そのブロック内の実行文より前に置く必要があります。通常のスクリプトなら SET @name = value; から始めます。SQL のキーワード DECLARE、SET、SELECT ... INTO、BEGIN ... END、SESSION、GLOBAL は例の中でそのまま使います。

目的に合う MySQL 変数を選ぶ

これらは置き換えて使えるものではありません。DECLARE @name INT はローカル変数の構文ではなく、ストアドプログラム外で DECLARE name INT を単独実行すると構文エラーになります。

SQL セッションでユーザー定義変数を使う

ユーザー定義変数は @ で始まります。使用前に宣言する必要はありません。SET で値または式を代入し、後続の文で参照します。

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

変数は現在の接続にだけ属します。別の接続からは参照できず、接続が終了すると消えます。初期化していないユーザー変数は NULL になるため、再利用するスクリプトの冒頭で初期化してください。

SET が最も分かりやすい代入方法です。SET 文では := も使えます。

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

型は宣言で決まらず、代入した値に応じて扱われます。数値、10進値、文字列、バイナリ文字列、NULL などを保持できます。型の境界を越える値には明示的な変換を使います。

SELECT ... INTO でクエリ結果を読み込む

クエリから値を取得する場合は SELECT ... INTO を使います。対象変数の数は、選択する式の数と一致させます。

SELECT COUNT(*) INTO @number_of_rows
FROM demo_numbers;

SELECT @number_of_rows;

クエリは1行を返すように設計します。COUNT(*) は自然に1行を返し、一意キーによる検索も適しています。複数行の可能性がある場合は、意図した ORDER BY と LIMIT 1 で対象を決めてください。

ユーザー変数をテーブル名や列名として使うことはできません。SET @table_name = 'demo_numbers'; SELECT * FROM @table_name; ではテーブル名は置換されません。動的 SQL が必要なら PREPARE 用の文を作り、アプリケーションから渡された識別子を検証します。

MySQL 9.7 では互換性のため SET 以外でのユーザー変数代入もまだ受け付けますが、将来削除予定です。SELECT @n := n のような式ではなく、SET と SELECT ... INTO を推奨します。

ストアドプログラムでローカル変数を宣言する

ローカル変数は @ なしの DECLARE を使い、MySQL のデータ型が必要です。DEFAULT を指定しない場合の初期値は NULL です。BEGIN ... END ブロックの先頭で、実行文より前に宣言します。変数はそのブロックと内側のブロックから見えますが、ブロックの外では使えません。

次のプロシージャは入力パラメーター、ローカル変数、出力パラメーターを使います。DELIMITER は mysql クライアントの命令で、セミコロンを含む本体を1つの 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 はプロシージャの実行中だけ存在します。OUT パラメーターは CALL に渡したセッション変数を通じて返されます。複数列をローカル変数に代入する場合は式ごとに対象を用意し、該当行がない場合も処理します。

同じ型の変数は1つの文で宣言できますが、別々に書くと保守しやすくなります。

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

DECLARE は条件、カーソル、ハンドラーも定義します。順序は重要で、変数と条件、カーソル、ハンドラーの順に置きます。そのため SELECT や SET の後に宣言すると構文エラーになります。

SELECT ... INTO の行数境界を理解する

スカラーの対象には1つの値しか保存できません。次のクエリは意図的に3行を返すため、エラー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

既知の1行に絞るか、集計するか、結果を変数ではなく行集合として扱います。LIMIT 1 は意図した順序と一緒に使ってください。

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

同じ1行ルールはローカル変数にも適用されます。行がないことが正しい結果になり得るルーチンでは、変数を明示的に初期化し、必要なら NOT FOUND ハンドラーまたは存在確認を使います。

MySQL システム変数を調べる、設定する

システム変数はサーバーまたはクライアント接続を設定します。SHOW VARIABLES や @@ 修飾子で確認します。

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

スコープを省略した SHOW VARIABLES は現在のセッションを対象にします。SESSION の値はその接続だけに影響し、GLOBAL の値は通常、新しい接続の初期値になります。GLOBAL 変数の変更には権限と変更可能な設定が必要です。

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

コメントの GLOBAL 文を実行可能な fixture に含めていないのは、アカウントの権限と変数のスコープ・変更可否で結果が変わるためです。

動作しない変数をトラブルシュートする

• DECLARE が拒否されたら、BEGIN ... END の中で実行文より前に置かれているか確認します。通常の SQL スクリプトなら SET @name = value を使います。
• 予想外に NULL になったら初期化し、元の式や検索結果を確認します。
• SELECT ... INTO がエラー1172を返すなら、クエリを1行にするか、結果を行集合のまま扱います。
• 別の接続から @name が見えないのは、セッション固有なので正常です。
• @@GLOBAL.name の変更に失敗したら、変数の変更可否と管理権限を確認します。

成功 fixture はセッション変数を代入してフィルターで使い、プロシージャ内のローカル変数と出力パラメーターを検証します。境界 fixture は複数行の SELECT ... INTO 診断を確認します。どちらも一時 MySQL サーバーを使います。

完全な構文は、MySQL ユーザー定義変数リファレンス、ローカル DECLARE リファレンス、SET による代入のリファレンスを参照してください。