Returning 機能を使用して DML ステートメントから結果セットを返す - RDS - Alibaba Cloud - ApsaraDB RDS

AliSQL の Returning 機能により、DML ステートメントを実行して影響を受けた行を取得する処理を 1 回のラウンドトリップで完了できます。後続の SELECT ステートメントは不要です。この機能は、操作後に影響を受けた行を確実に特定することが困難な場合（例：AUTO_INCREMENT キーで挿入された行、または複数行を対象とした WHERE 句で更新された行）に特に有効です。

MySQL のステートメント実行結果は、結果セット、OK パケット、ERR パケットの 3 種類に分類されます。OK パケットまたは ERR パケットには、影響を受けたレコード数やスキャンされたレコード数などの属性が含まれます。Returning 機能では、DML 実行結果を結果セットに統合することで、サーバーがクライアントに対して 1 回のみ応答を行うことが可能になります。

前提条件

開始する前に、以下の条件を満たしていることを確認してください。

MySQL 8.0 を実行中の RDS インスタンス

仕組み

dbms_trans.returning() を呼び出し、返却するフィールドと実行する DML ステートメントを指定します。サーバーは DML ステートメントを実行し、影響を受けた行を結果セットとして返却します。

構文

DBMS_TRANS.returning(<Field_list>, <Statement>);

パラメーター	説明
`Field_list`	返却するフィールドを指定します。複数のフィールドをカンマで区切ります。ネイティブフィールドおよびワイルドカード (`*`) をサポートします。集計関数および計算式はサポートされません。結果セットではなく OK パケットを返却する場合は、空文字列 (`""`) を指定します。
`Statement`	実行する DML ステートメントを指定します。INSERT、UPDATE、DELETE のみをサポートします。

注意事項

dbms_trans.returning() はトランザクション文ではありません。ラップされた DML ステートメントのトランザクションコンテキストを継承します。トランザクションを終了するには、明示的にコミットまたはロールバックする必要があります。

INSERT Returning

INSERT ステートメントによって挿入された行を返却します。挿入時にサーバーが解決した AUTO_INCREMENT 値およびデフォルト値を含みます。

サンプルテーブルを作成します。

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` int(11) NOT NULL DEFAULT '1',
  `col2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

2 行を挿入し、1 回の呼び出しでそれらを取得します。

mysql> call dbms_trans.returning("*", "insert into t(id) values(NULL),(NULL)");
+----+------+---------------------+
| id | col1 | col2                |
+----+------+---------------------+
|  1 |    1 | 2019-09-03 10:39:05 |
|  2 |    1 | 2019-09-03 10:39:05 |
+----+------+---------------------+
2 rows in set (0.01 sec)

制限事項

INSERT VALUES 形式のステートメントのみをサポートします。INSERT SELECT および CREATE AS はサポートされません。
```
mysql> call dbms_trans.returning("", "insert into t select * from t");
ERROR 7527 (HY000): Statement didn't support RETURNING clause
```

Field_list に空文字列を指定すると、結果セットではなく OK パケットが返却されます。

mysql> call dbms_trans.returning("", "insert into t(id) values(NULL),(NULL)");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

UPDATE Returning

UPDATE ステートメントによって更新された行を返却します。

mysql> call dbms_trans.returning("id, col1, col2", "update t set col1 = 2 where id >2");
+----+------+---------------------+
| id | col1 | col2                |
+----+------+---------------------+
|  3 |    2 | 2019-09-03 10:41:06 |
|  4 |    2 | 2019-09-03 10:41:06 |
+----+------+---------------------+
2 rows in set (0.01 sec)

制限事項：複数テーブルを対象とした UPDATE ステートメントはサポートされません。

DELETE Returning

DELETE ステートメントによって削除された行を返却します。

mysql> call dbms_trans.returning("id, col1, col2", "delete from t where id < 3");
+----+------+---------------------+
| id | col1 | col2                |
+----+------+---------------------+
|  1 |    1 | 2019-09-03 10:40:55 |
|  2 |    1 | 2019-09-03 10:40:55 |
+----+------+---------------------+
2 rows in set (0.00 sec)