This topic describes the Returning feature of AliSQL. This feature enables data manipulation language (DML) statements to return result sets and provides the DBMS_TRANS package for you to track the execution of DML statements.
Background information
The execution results of MySQL statements 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 and the number of scanned records. However, the execution of a DML statement (INSERT, UPDATE, or DELETE) is often followed by the execution of the SELECT statement to query current records. In such cases, the Returning feature enables the server to respond to the client only once by combining the execution results of the two statements into a result set.
Prerequisites
The instance must be running RDS MySQL 8.0 or 8.4.
Syntax
DBMS_TRANS.returning(<Field_list>,<Statement>);The following table describes the parameters that you need to configure.
Parameter | Description |
Field_list | The fields to return. To specify multiple fields, separate them with commas (,). You can specify fields from the table or use the wildcard (*). Operations such as calculations or aggregations are not supported. |
Statement | The DML statement to execute. INSERT, UPDATE, and DELETE are supported. |
Precautions
The dbms_trans.returning() statement is not transactional. It inherits the transaction context from the DML statement. To end the transaction, you must explicitly commit or roll back the transaction.
INSERT Returning
The server returns the records that were inserted into the specified table by using the INSERT statement.
Example:
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;
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)If you do not specify the Field_list parameter, the returning feature returns an OK or ERR 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 mysql> select * from t; +----+------+---------------------+ | id | col1 | col2 | +----+------+---------------------+ | 1 | 1 | 2019-09-03 10:40:55 | | 2 | 1 | 2019-09-03 10:40:55 | | 3 | 1 | 2019-09-03 10:41:06 | | 4 | 1 | 2019-09-03 10:41:06 | +----+------+---------------------+ 4 rows in set (0.00 sec)INSERT Returning supports only the
insert valuessyntax. Other syntaxes, such ascreate asandinsert select, are not supported.mysql> call dbms_trans.returning("", "insert into t select * from t"); ERROR 7527 (HY000): Statement didn't support RETURNING clause
UPDATE Returning
The server returns the records that were updated in the specified table by the using UPDATE statement.
Example:
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 Returning does not support multi-table UPDATE statements.
DELETE Returning
The server returns the records that were deleted from the specified table by using the DELETE statement.
Example:
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)