Eliminate Extra Queries by Using RETURNING for DML Results - PolarDB

After you run an INSERT, UPDATE, or DELETE statement, the Returning feature returns the affected rows immediately — without requiring a follow-up SELECT. This is useful for retrieving database-generated values such as AUTO_INCREMENT IDs or DEFAULT column values.

Prerequisites

Before you begin, ensure that:

Your PolarDB for MySQL cluster is running version 5.7 with revision version 5.7.1.0.6 or later. To check your version, see Query the engine version.

How it works

Standard MySQL returns only an OK or ERR message after a DML statement. The message includes the number of affected and scanned rows, but not the actual row data. To inspect the data, you normally run a follow-up SELECT — a second round trip to the server.

The Returning feature eliminates that second round trip. Call DBMS_TRANS.RETURNING() with your DML statement and a column list. The procedure runs the DML and returns the matching rows in a single response.

CALL DBMS_TRANS.RETURNING() is not a transactional statement. It inherits the transaction context of the DML statement you pass in. Commit or roll back the transaction explicitly.

Syntax

CALL DBMS_TRANS.RETURNING(Field_list=>, Statement=>);

Parameters

Parameter	Description
`Field_list`	Comma-separated list of columns to return, or `*` for all columns. Calculation and aggregation expressions are not supported. Pass an empty string (`""`) to suppress the result set and return only the OK or ERR message.
`Statement`	The DML statement to run. Supported types: INSERT, UPDATE, DELETE.

Limitations

INSERT: Only INSERT ... VALUES statements are supported. INSERT ... SELECT and CREATE TABLE ... AS SELECT are not supported. Running an unsupported form returns ERROR 7527 (HY000): Statement didn't support RETURNING clause.
UPDATE: Multi-table UPDATE statements are not supported.

Examples

All examples use the following 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

Return all columns for inserted rows, including the AUTO_INCREMENT id and DEFAULT col2 values generated by the database:

CALL DBMS_TRANS.RETURNING("*", "insert into t(id) values(NULL),(NULL)");

Result:

+----+------+---------------------+
| 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)

To suppress the result set and return only the OK or ERR message, pass an empty string as Field_list:

CALL DBMS_TRANS.RETURNING("", "insert into t(id) values(NULL),(NULL)");

Result:

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

UPDATE

Return the updated rows with their new column values:

CALL DBMS_TRANS.RETURNING("id, col1, col2", "update t set col1 = 2 where id > 2");

Result:

+----+------+---------------------+
| 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)

DELETE

Return the deleted rows with their values before deletion:

CALL DBMS_TRANS.RETURNING("id, col1, col2", "delete from t where id < 3");

Result:

+----+------+---------------------+
| 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)