ユーザー定義の変数に格納された値は、他のステートメントで参照することができ、値をあるステートメントから別のステートメントに渡すことができます。
ユーザー変数の記述形式は @var_name です。ここで、変数名 var_name は、英字、数字、.、_、$ で構成されます。ユーザー変数名を文字列や識別子として参照する場合（例えば、@'my-obvar'、@"my-obvar" または @`my-obvar` のように）、その名前には他の文字も含めることができます。ユーザー変数名は大文字小文字を区別せず、最大長は64文字です。
ユーザー定義変数は、特定のセッション内でのみ使用されます。あるクライアントで定義されたユーザー変数は、他のクライアントからは見えなかったり、利用できなかったりします。そのクライアントが終了すると、そのクライアントのセッションに指定されたすべての変数は自動的に解放されます。
ユーザー定義変数を設定する方法の1つは SET ステートメントを使用することです：
SET @var_name = expr [, @var_name = expr] ...
詳細については、SET を参照してください。
SET ステートメントでは、= または := のどちらも代入演算子として使用できます。
ユーザー変数に割り当て可能なデータ型は、整数、十進数、浮動小数点数、バイナリまたは非バイナリ文字列、または NULL 値です。十進数および実数値の代入では、値の精度や小数点以下の桁数は保持されません。その他のデータ型の値は、許容されるデータ型に変換されます。例えば、時刻や空間データ型の値はバイナリ文字列に変換されます。
ユーザー定義変数を読み取るには、SELECT ステートメントを使用できます。
ユーザー変数に非バイナリ（文字）文字列値が割り当てられた場合、それは文字列と同じ文字セットと照合順序を持ちます。ユーザー変数に割り当てられた16進数またはビット値は、バイナリ文字列と見なされます。16進数またはビット値を数値としてユーザー変数に割り当てるには、0を追加するか CAST 関数 CAST(... AS UNSIGNED) を使用できます：
obclient> SET @v1 = X'43';
obclient> SET @v2 = X'43'+0;
obclient> SET @v3 = CAST(X'43' AS UNSIGNED);
obclient> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1 | @v2 | @v3 |
+------+------+------+
| C | 67 | 67 |
+------+------+------+
1 row in set
obclient> SET @v1 = b'1000011';
obclient> SET @v2 = b'1000011'+0;
obclient> SET @v3 = CAST(b'1000011' AS UNSIGNED);
obclient> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1 | @v2 | @v3 |
+------+------+------+
| C | 67 | 67 |
+------+------+------+
1 row in set
結果セットでユーザー変数の値を選択した場合、それは文字列としてクライアントに返されます。初期化されていない変数を参照した場合、その値は NULL であり、型は文字列です。
制限事項
OceanBaseデータベースのユーザー定義変数には、以下の制限があります。
- OceanBaseデータベースの初期バージョンでは、
SET文以外でもユーザー変数に値を代入できましたが、将来のバージョンで削除される可能性があります。 - ユーザー変数を含む式の評価順序は定義されていません。例えば、
SELECT @a, @a:=@a+123では、まず@aが計算され、その後代入式が実行されるとは限りません。この問題を回避するため、単一の文内で変数に値を代入しながらその変数の値を読み取ることは避けてください。 - 並列実行時には、ユーザー定義変数の計算結果の正確性を保証できません。
/*+USE_DAS(...)*/ヒントを使用して直列実行を指定する必要があります。今後のバージョンでは、ヒントを指定しなくても自動的に直列実行に切り替えるよう最適化される予定です。 4.1.x および 4.2.0 バージョンでは/*+USE_DAS(...)*/ヒントを使用して制御する必要がありましたが、4.2.1 以降のバージョンではヒントを指定する必要がなく、自動的に直列実行に切り替えられます。
MySQL互換性の説明
- OceanBaseには、MySQLに類似した単一マシン実行エンジンのほか、分散クエリを処理するための並列スケジューリングエンジンも存在します。実行プロセスでユーザー変数の値を変更すると、並列タスクが読み取るユーザー変数の値が一致しなくなります。並列実行下でユーザー変数の動作を正しく保証するためには、まず実行プロセス中の各ノード間で変数値の一貫性を保つ必要があります。このような分散シナリオでの同期オーバヘッドは非常に高いため、分散シナリオではMySQLとの完全な互換性を実現することはできません。
- SQL文で
@var:=val構文を使用せず、ユーザー変数を読み取るだけの場合は、MySQLと互換性があります。例：SELECT c1, c2 FROM t1 WHERE c1=@var;。 - SQL実行中に
:=演算子を使用してユーザー変数の値をリアルタイムで変更すると、ユーザー変数の動作はUndefinedとなります。具体的な挙動は実行順序に依存し、OceanBaseデータベースV4.2.0以前のバージョンではMySQLとの完全な互換性は保証されません。 - SQL実行中に
:=演算子を使用してユーザー変数の値をリアルタイムで変更する場合、OceanBaseデータベースV4.2.0バージョンは以下のシナリオでMySQL5.6/5.7/8.0との互換性を維持します。その他の場合の結果は具体的な実行計画のタイプに依存し、MySQLと動作が異なる可能性があります。INSERT VALUES句内で使用される場合。例：INSERT INTO t1 VALUES(@a:=1, @a);。 SQL文でユーザー変数に値を代入し、その実行結果がその変数の先行する値に影響される場合、実行が失敗して再試行されると、ユーザー変数の初期値が変わっているため、代入結果にMySQLとの互換性の違いが生じる可能性があります。
ユーザー変数の初期化
ユーザー定義変数は、初期値が代入されていない場合、データベースのデフォルト値はNULLとなります。そのため、ウィンドウ関数の動作をユーザー定義変数でシミュレートする際には、通常、SQLのFROM句のサブクエリで SELECT @var:=val from dual を用いてユーザー変数を初期化し、これによりメインクエリとJOINクエリを形成します。例：
SELECT A.c1, (@rownum:= @rownum+1) AS rownum FROM t1 A,(SELECT @rownum:=0) B;
SELECT @rownum;
このクエリがNESTED LOOP JOINアルゴリズムを選択し、かつBテーブルをJOINの内部テーブルとして使用する場合、外部テーブルのデータが空集合のとき、OceanBaseの実行エンジンはショートサーキット実行戦略を採用し、Bテーブルの計算を実行しません。そのため、Bテーブル上のユーザー変数の初期化操作は実行されません。一方、MySQLでは外部テーブルが空であるかどうかにかかわらず、SELECT @var:=val from dual のような初期化ステートメントは常に実行されます。
--MySQLはt1にデータが存在するかどうかにかかわらず、常にrownum:=0 の初期化処理を実行します
MySQL> SELECT A.c1, (@rownum:= @rownum+1) AS rownum FROM t1 A,(SELECT @rownum:=0) B;
Empty set, 2 warnings
MySQL> SELECT @rownum;
+---------+
| @rownum |
+---------+
| 0 |
+---------+
1 row in set
--OceanBase 4.2バージョン以前では、Nested Loop Joinを使用する場合、t1のデータが空の場合、rownum:=0 は実行されません
obclient> SELECT /*+use_nl(A, B)*/ A.c1, (@rownum:= @rownum+1) AS rownum FROM t1 A,(SELECT @rownum:=0) B;
Empty set
obclient> SELECT @rownum;
+---------+
| @rownum |
+---------+
| NULL |
+---------+
1 row in set
MySQLのこの動作との互換性のため、OceanBaseでは、FROM句内に現れ、実体テーブルを含まないユーザー変数への代入ステートメントをユーザー変数の初期化操作と見なします。ステートメントの実行開始時に、指定された順序でこれらの初期化処理の代入ステートメントが優先的に評価されます。 例：
SELECT /*+use_nl(t1, A)*/ * FROM t1, (SELECT @rownum:=0 FROM DUAL) A;
--SELECT @rownum:=0 は変数の初期化操作であり、このサブクエリが先に実行されます
SELECT * FROM t1 WHERE (SELECT @rownum:=0 FROM DUAL)=0;
--SELECT @rownum:=0 はFROM句内にないため、初期化ステートメントではありません。実行されるかどうかはt1にデータがあるかどうかによります
SELECT /*+use_nl(t1, A)*/ * FROM t1, (SELECT @rownum:=(SELECT c1 FROM t2)) A;
--SELECT @rownum:=(SELECT c1 FROM t1) はFROM句内にありますが、実体テーブルt2にアクセスするため、初期化ステートメントではありません
SELECT * FROM t1 WHERE (SELECT rownum FROM (SELECT @rownum:=0) t)=0;
--SELECT @rownum:=0 はFROM句内にあり、初期化ステートメントです
@var:=valがFROMサブクエリ内に現れる場合
ユーザー変数の代入操作がFROMサブクエリ内に現れると、MySQLはFROMサブクエリ間のマージを禁止し、2層のクエリ間のデータ参照関係はストリーミング方式での反復処理ではなくなります。代わりに、ユーザー変数 := を含むFROMサブクエリの結果をすべて一時テーブルにマテリアライズして、上位クエリがアクセスできるようにします。例えば：
MySQL> CREATE TABLE t1(c1 INT);
Query OK, 0 rows affected
MySQL> INSERT INTO t1 VALUES(1), (2), (3);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
MySQL> SELECT @a, c1 FROM (SELECT @a:=c1, c1 FROM t1) t;
+------+------+
| @a | c1 |
+------+------+
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
+------+------+
3 rows in set, 1 warning
最終的な実行結果から分かるように、SELECT @a:=c1, c1 FROM t1 が先にマテリアライズされた後、外層クエリ SELECT @a, c1 FROM t が実行されます。
MySQLのこの特性は安定しており、ユーザーがウィンドウ関数のさまざまなグループ計算をシミュレートするために利用されています。OceanBaseはこの動作をサポートしています。
ユーザー変数 := 操作がFROMサブクエリ内に現れると、OBオプティマイザーは各層のFROMサブクエリ間のVIEW MERGE変換を禁止し、各サブクエリ間にMATERIALOPERATORを追加してクエリ間のデータストリーミング計算をブロックすることで、MySQLの動作との互換性を保証します。
obclient>EXPLAIN SELECT @a, c1 FROM (SELECT @a:=c1, c1 FROM t1) t;
+---------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------+
| =============================================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------------- |
| |0 |MATERIAL | |1 |4 | |
| |1 |└─SUBPLAN SCAN |t |1 |4 | |
| |2 | └─DISTRIBUTED TABLE FULL SCAN|t1 |1 |4 | |
| =============================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([('a')], [t.c1]), filter(nil) |
| 1 - output([t.c1]), filter(nil) |
| access([t.c1]) |
| 2 - output([(T_OP_ASSIGN, 'a', t1.c1)], [t1.c1]), filter(nil) |
| access([t1.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
+---------------------------------------------------------------------+
16 rows in set
ただし、FROMサブクエリ内で := を使用すると、FROMサブクエリが事前に評価され結果がマテリアライズされる可能性があります。これにより、元のステートメントのJOIN条件がベーステーブルにプッシュダウンされるなどの最適化が影響を受け、NESTED LOOP JOINで内部テーブルのインデックス条件を使用できなくなり、実行効率が低下する可能性があります。例えば：
CREATE TABLE t1(a INT, b INT);
CREATE TABLE t2(a INT PRIMARY KEY, b INT);
obclient> EXPLAIN SELECT /*+use_nl(t1 t)*/ * FROM t1 JOIN (SELECT @b:=b AS b, a FROM t2) t ON t1.a=t.a WHERE t1.b=1;
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| ================================================================= |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------------------------- |
| |0 |NESTED-LOOP JOIN | |1 |8 | |
| |1 |├─DISTRIBUTED TABLE FULL SCAN |t1 |1 |4 | |
| |2 |└─MATERIAL | |1 |4 | |
| |3 | └─SUBPLAN SCAN |t |1 |4 | |
| |4 | └─DISTRIBUTED TABLE FULL SCAN|t2 |1 |4 | |
| ================================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.a], [t1.b], [t.b], [t.a]), filter(nil) |
| conds([t1.a = t.a]), nl_params_(nil), use_batch=false |
| 1 - output([t1.a], [t1.b]), filter([t1.b = 1]) |
| access([t1.a], [t1.b]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
| 2 - output([t.b], [t.a]), filter(nil) |
| 3 - output([t.a], [t.b]), filter(nil) |
| access([t.a], [t.b]) |
| 4 - output([(T_OP_ASSIGN, 'b', t2.b)], [t2.a]), filter(nil) |
| access([t2.a], [t2.b]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t2.a]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
24 rows in set
@var:=val のDMLステートメントでの使用
OceanBaseは、MySQLモードでユーザー変数 := を使用してDML実行プロセス中の新旧値を取得し、クライアントとデータベース間のやり取り回数を最適化することを許可しています。これにより、OracleモードのDML RETURNING INTO機能に類似した動作をシミュレートできますが、以下の制限があります：
- DMLは内部実行の制約により、式の計算順序とユーザー定義の順序が厳密に一致する保証はありません。そのため、DML句内で同一のユーザー変数に対して代入と参照を同時に行うことは避けてください。例：
INSERT INTO t1(c1, c2) VALUES(@a:=1, @a+1);UPDATE t1 SET c1=@a:=1, c2=@a+1 WHERE c1=1;この2つの例では、@a:=1が必ずしも@a+1より先に実行される保証はありません。 - DMLで
@var:=valを使用する場合、DML affected_row=1 の場合にのみMySQLと互換性があります。affected_row>1 の場合、SQLデータの実行順序がMySQLと一致しない可能性があるため、最終的なユーザー変数の結果に差異が生じる可能性があります。
@var:=val のSELECT句での使用
一部の業務シナリオでは、同一の句内で一部の式が繰り返し冗長に計算される場合があります。このような場合、ユーザー変数に代入した後に参照することで、前者の計算結果を再利用できます。例えば SELECT @a:=(c1+1), @a+c2, @a*c3 FROM t1 のようにです。しかし、MySQLの公式ドキュメントでは、このような状況ではユーザー変数を含む式の実行順序を保証できないため、結果がユーザーの期待通りになる保証はないと明確に記述されています。
MySQLの具体的な動作に関する調査により、上記のクエリが最上位クエリのSELECT句、またはFROMサブクエリ内のSELECT句に現れる場合、式の実行順序はユーザー定義の順序に従うことが判明しました。ただし、他の句では必ずしも一致する保証はありません。例：
SELECT @a:=(c1+1), @a+c2, @a*c3 FROM t1;
-- トップレベルのクエリに現れる場合、式の実行順序はユーザー定義の順序で実行されます。
SELECT * FROM (SELECT @a:=(c1+1), @a+c2, @a*c3 FROM t1) t;
-- FROMサブクエリ内に現れる場合、式の実行順序はユーザー定義の順序で実行されます。
SELECT * FROM t1 WHERE (c1, c2) in (SELECT @a:=c1, @a+1 FROM t2);
-- WHEREサブクエリ内に現れる場合、ユーザー変数を含む式の実行順序はユーザー定義の順序である保証はなく、クエリ結果に不確実性が生じる可能性があります。
OceanBaseはMySQLの確定的な動作のみをサポートしており、不確定的な動作についてはMySQLと互換性がない場合があります。
@var:=val のWHERE句での使用
WHERE句内でのユーザー変数 := 操作の実行結果は、述語条件の実行順序に依存します。MySQLの動作は実装によって異なるため、このようなシナリオについてOceanBaseは互換性を保証するものではありません。例：
SELECT * FROM t1 WHERE (@a:=c1)>10;
-- クエリの結果および @a の最終結果は実行に依存し、MySQLと互換性がありません。
SELECT * FROM t1 WHERE c1 in (SELECT @a:=1 FROM t2);
-- WHERE句内に位置するため、結果は実装に依存し、MySQLと互換性がありません。
@var:=valがORDER BY句に現れる場合
ORDER BY句はデータを並べ替えるため、データの反復順序が乱れます。ORDER BY句内でユーザー変数 := を使用すると、予期しない結果が発生する可能性があります。OceanBaseは、ORDER BY句内でのユーザー変数の使用について、MySQLと完全に互換性を持たせることはできません。ORDER BY句でユーザー変数を使用する場合、各データベースの動作は実装定義となります。例：
CREATE TABLE t1(a INT PRIMARY KEY, b INT);
INSERT INTO t1 VALUES(1, 1), (2, 1), (3, 1), (4, 1), (5, 5), (6, 6);
--MySQLの動作
MySQL> SELECT @rownum:=@rownum+1 AS rn, MAX(a), sum(a) FROM t1, (SELECT @rownum:=1) t GROUP BY b ORDER BY rn;
+------+--------+--------+
| rn | max(a) | sum(a) |
+------+--------+--------+
| 2 | 4 | 10 |
| 3 | 5 | 5 |
| 4 | 6 | 6 |
+------+--------+--------+
3 rows in set, 2 warnings
--ORDER BYのソート順序が昇順の場合、rownumの最終結果は最後の行の値を指し、4となります。
MySQL> SELECT @rownum;
+---------+
| @rownum |
+---------+
| 4 |
+---------+
1 row in set
MySQL> SELECT @rownum:=@rownum+1 AS rn, MAX(a), SUM(a) FROM t1, (SELECT @rownum:=1) t GROUP BY b ORDER BY rn desc;
+------+--------+--------+
| rn | max(a) | sum(a) |
+------+--------+--------+
| 4 | 6 | 6 |
| 3 | 5 | 5 |
| 2 | 4 | 10 |
+------+--------+--------+
3 rows in set, 2 warnings
--ORDER BYのソート順序が降順の場合、rownumの最終結果は最初の行の値を指し、4となります。
MySQL> SELECT @rownum;
+---------+
| @rownum |
+---------+
| 2 |
+---------+
1 row in set
--OceanBaseの動作
obclient> SELECT @rownum:=@rownum+1 AS rn, MAX(a), SUM(a) FROM t1, (SELECT @rownum:=1) t GROUP BY b ORDER BY rn;
+------+--------+--------+
| rn | max(a) | sum(a) |
+------+--------+--------+
| 2 | 4 | 10 |
| 3 | 5 | 5 |
| 4 | 6 | 6 |
+------+--------+--------+
3 rows in set
--ORDER BYのソート順序が昇順の場合、rownumの最終結果は最後の行の値を指し、4となります。
obclient> SELECT @rownum;
+---------+
| @rownum |
+---------+
| 4 |
+---------+
1 row in set
obclient> SELECT @rownum:=@rownum+1 AS rn, MAX(a), SUM(a) FROM t1, (SELECT @rownum:=1) t GROUP BY b ORDER BY rn desc;
+------+--------+--------+
| rn | max(a) | sum(a) |
+------+--------+--------+
| 4 | 6 | 6 |
| 3 | 5 | 5 |
| 2 | 4 | 10 |
+------+--------+--------+
3 rows in set
--ORDER BYのソート順序が降順の場合、rownumの最終結果は最初の行の値を指しますが、それでも4となります。
obclient> SELECT @rownum;
+---------+
| @rownum |
+---------+
| 4 |
+---------+
1 row in set
したがって、ORDER BY句でユーザー定義変数 := を使用して代入演算を行ったり、間接的にユーザー定義変数 := の演算後の列を参照してソート演算を行ったりすると、OceanBaseはソート後のユーザー定義変数の結果がMySQLと互換性を保つことを保証できません。そのため、ORDER BY句の後で再度そのユーザー定義変数を使用した関連演算を行うことは避けてください。
@var:=val はクエリの並列実行を妨げる
クエリに @var:=val 演算が含まれている場合、OceanBaseはすべてのシナリオでMySQLの動作と完全に互換性を持たせることはできませんが、このような演算を含むクエリが最終的に自己解釈可能であることを保証する必要があります。そのため、@var:=val 割り当て操作を含むクエリでは、処理の直列実行が強制的に適用され、この種のクエリに対する明示的な並列実行は無効となります。例えば：
obclient> EXPLAIN EXTENDED_NOADDR SELECT /*+parallel(2)*/ @a:=c1 FROM t1;
+-------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------+
| =========================================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------------------- |
| |0 |DISTRIBUTED TABLE FULL SCAN|t1 |1 |4 | |
| =========================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([(T_OP_ASSIGN, 'a', t1.c1)]), filter(nil) |
| access([t1.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| PARALLEL(2) |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "test"."t1"@"SEL$1") |
| USE_DAS(@"SEL$1" "test"."t1"@"SEL$1") |
| PARALLEL(2) |
| OPTIMIZER_FEATURES_ENABLE('4.0.0.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| t1: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:1 |
| table_dop:1 |
| dop_method:DAS DOP |
| avaiable_index_name:[t1] |
| stats version:0 |
| dynamic sampling level:0 |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelism is 1 because of hint |
+-------------------------------------------------------------------+
48 rows in set