Use the Returning feature to return result sets from DML statements - ApsaraDB RDS

The Returning feature of AliSQL lets you execute a DML statement and retrieve the affected rows in a single round trip — no follow-up SELECT needed. It is especially useful when the affected rows are hard to identify reliably after the operation (for example, rows inserted with AUTO_INCREMENT keys or updated by a bulk WHERE clause).

MySQL statement execution results are divided into three types: result sets, OK packets, and ERR packets. An OK or ERR packet contains attributes such as the number of affected records and the number of scanned records. The Returning feature enables the server to respond to the client only once by combining the DML execution results into a result set.

Prerequisites

Before you begin, ensure that you have:

An RDS instance running MySQL 8.0

How it works

Call dbms_trans.returning() with the fields to return and the DML statement to execute. The server runs the DML statement and returns the affected rows as a result set.

Syntax

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

Parameter	Description
`Field_list`	The fields to return. Separate multiple fields with commas. Supports native fields and the wildcard (`*`). Aggregation and calculated expressions are not supported. To return an OK packet instead of a result set, pass an empty string (`""`).
`Statement`	The DML statement to execute. Supports INSERT, UPDATE, and DELETE only.

Usage notes

dbms_trans.returning() is not a transactional statement. It inherits the transaction context of the DML statement it wraps. To end the transaction, explicitly commit or roll it back.

INSERT Returning

Returns the rows inserted by the INSERT statement, including auto-incremented and default column values resolved by the server at insert time.

Create a sample table:

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;

Insert two rows and retrieve them in one call:

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)

Limitations

Only INSERT VALUES-style statements are supported. INSERT SELECT and CREATE AS are not supported.

mysql> call dbms_trans.returning("", "insert into t select * from t");
ERROR 7527 (HY000): Statement didn't support RETURNING clause

Passing an empty Field_list returns an OK packet instead of a result set:

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

Returns the rows updated by the UPDATE statement.

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)

Limitation: Multi-table UPDATE statements are not supported.

DELETE Returning

Returns the rows deleted by the DELETE statement.

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)