ユーザー定義の変数に格納された値は、他のステートメントで参照することができ、値をあるステートメントから別のステートメントに渡すことを実現します。
ユーザー変数の記述形式は @var_name です。ここで、変数名 var_name は、英字、数字、.、_、$ で構成されます。ユーザー変数名を文字列や識別子として参照する場合（例えば、@'my-obvar'、@"my-obvar" または @`my-obvar` のように）、その名前には他の文字も含めることができます。ユーザー変数名は大文字小文字を区別せず、名前の最大長は 64 文字です。
ユーザー定義変数は、指定したセッション内でのみ使用されます。あるクライアントで定義されたユーザー変数は、他のクライアントからは見えなかったり、使用できなかったりします。そのクライアントが終了すると、指定されたクライアントセッションのすべての変数は自動的に解放されます。
ユーザー定義変数を設定する方法の一つは 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の最終結果は最後の行の値を指し、2となります。
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