ユーザー定義の変数に格納された値は、他のステートメントで参照することができ、あるステートメントから別のステートメントへ値を渡すことを実現します。
ユーザー変数の記述形式は @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構文を使用せず、ユーザー変数を読み取るだけの場合は、例えばSELECT c1, c2 FROM t1 WHERE c1=@var;といった形でMySQLと互換性があります。 - SQL実行中に
:=演算子を使用してユーザー変数の値をリアルタイムで変更すると、ユーザー変数の動作は未定義となります。具体的な挙動は実行順序に依存します。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書き換えを禁止し、各層のサブクエリ間にMATERIALizerを追加してクエリ間のデータストリーミング計算をブロックすることで、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;のどちらの例でも、@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