システム変数の意味と、システム変数の確認、取得、変更方法を紹介します。
またMySQLでは、ユーザーがシステム変数を定義できるユーザー定義変数が使えるので、
ユーザー定義変数についても紹介します。
システム変数とは
システム変数とは、データベース上で設定されている設定値のことです。
この設定値でデータベースの挙動が変化します。
確認と変更の方法を先にまとめると、確認はSHOW VARIABLES LIKE '変数名';、変更はSET GLOBAL 変数名 = 値;(サーバー全体)またはSET SESSION 変数名 = 値;(今の接続だけ)です。
SET GLOBALで変えた値は今開いている接続には反映されず、新しい接続から有効になります。
また、サーバーを再起動すると設定ファイル(my.cnf / my.ini)の値に戻るため、恒久的に変えたい場合は設定ファイルも書き換えます(MySQL 8.0以降ならSET PERSISTで設定ファイルなしで永続化できます)。
データベースを扱うときに、データベースのシステム変数を確認したい場合があります。
その方法を紹介します。
システム変数の値の取得
-- セッション
@@session.変数名: 現在のセッション内での値を取得
-- @@local.変数名: @@sessionと同じ
-- グローバル
@@global.変数名: サーバー上の値を取得
-- sessionにあればセッション、なければglobal
@@変数名: session、globalの順番で変数を取得システム変数には、@@sessionのように、セッション内で有効な変数と、
@@globalのように、サーバー上で有効な変数があります。
@@localは@@sessionとほぼ同じ意味です。
@@だけにすると、暗黙的にsessionから値を探し、sessionになければglobalに変数を取得します。
システム変数の値の変更
set session: 現在のセッション内で有効
set local: SESSIONと同じ
set global: 全てのセッション（サーバー全体）で有効（DB再起動まで）
set persist: globalと同じ＋再起動後も維持（MySQL 8.0以降）set globalは、データベースを再起動すると設定ファイルの値に戻るので注意が必要です。
省略した場合はセッション変数を変更することになります。
set globalにはSYSTEM_VARIABLES_ADMIN(または従来のSUPER)権限が必要で、権限がないと「Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s)」というエラーになります。
また、max_connectionsのようにglobalにしかない変数をset sessionしようとすると「Variable ‘max_connections’ is a GLOBAL variable」というエラーになります。
どちらのスコープを持つ変数かは、公式リファレンスのシステム変数一覧で確認できます。
システム変数の確認方法
-- 変数の確認
show variables
show [session|global|local] variables like '%auto%';上のようにするとシステム変数を確認できます。
like検索を使えば、部分一致の検索もできます。
show variablesだけだと数百件出てくるため、通常はlikeで絞ります。
show global variablesとshow session variablesで値が違う場合は、set globalの後に接続を張り直していないか、接続時にsession側だけ変更されていることが原因です。
システム変数の値の取得
-- 値の取得
select @@session.autocommit;
select @@global.autocommit;上を実行すると、その値を取得できます。
システム変数の値の変更
-- 値の設定
set session autocommit = 1;
set @@session.autocommit = 0;
-- サーバー全体を変更(新しい接続から有効)
set global max_connections = 300;
set @@global.max_connections = 300;
-- 変更を確認
show global variables like 'max_connections';上のようにすると、システム変数を変更できます。
sessionの値は、変更しても一度閉じると初期化されます。
上のように、autocommitを0にすると、start transactionを書く必要はなくなります。(オートコミットオフの状態)
オートコミットオフの状態では、すべての更新はtransactionの一部とみなされるので、commitがあるまではデータが反映されません。
ロックが残り続ける原因にもなるため、autocommitの扱いはMySQLのロックとデッドロックについても合わせて確認してみてください。
ユーザー定義変数とは
ユーザー定義変数とは、ユーザー定義したシステム変数です。
セッション内でのみ有効となります。
ユーザー定義変数の定義方法
-- 定義
set @変数名 = 値;
-- 使用するとき
@変数名
set @sys_id = 2;
select * from mst_products mp
where mp.id = @sys_id;setで値を指定することで、ユーザー定義変数を作成できます。
そして、@変数名で使用できます。
取得した値をセットする
set @sys_id = 2;
select @sys_name := name from mst_products mp
where mp.id = @sys_id;
-- @sys_nameの値を確認
select @sys_name;
-- 同じことをselect ... intoで書く(MySQL 8.0で推奨)
select name into @sys_name from mst_products where id = @sys_id;上のように、コロンとイコール(:=)にすることで、取得してきたnameの値が@sys_nameに代入されます。
なお、select内での:=による代入はMySQL 8.0で非推奨になっているため、新しく書く場合はselect ... into @変数の形を使います。
セッション内でのみ有効な点には注意が必要です。
文字コードや照合順序(character_set_server / collation_server)もシステム変数のひとつで、確認と変更の考え方は同じです。
照合順序そのものの意味はMySQLのCOLLATION(照合順序)の意味と確認方法を参考にしてみてください。