tencent cloud

TencentDB for MySQL

DokumentasiTencentDB for MySQL

RETURNING

Download
Mode fokus
Ukuran font
Terakhir diperbarui: 2026-03-11 09:57:14

Feature Introduction

In certain usage scenarios, it is necessary to return the operated data rows immediately after DML operations. There are generally two approaches to implement this requirement:
The first approach is to immediately follow the DML statement with a SELECT statement after transactions are enabled.
The second approach is to implement it using more complex operations such as triggers.
The former primarily increases the overhead of an additional SELECT statement, while the latter makes the implementation of SQL more complex and less flexible (requiring the creation of triggers). Therefore, the design of the RETURNING syntax is primarily aimed at optimizing this scenario. By adding the RETURNING keyword after a DML statement, it can flexibly and efficiently achieve the aforementioned requirement.

Supported Versions

Kernel version MySQL 5.7 20210330 and above
Kernel version MySQL 8.0 20220330 and above

Applicable Scenarios

In the current kernel version MySQL 5.7 20210330 and above, the following are supported: INSERT ... RETURNING, REPLACE ... RETURNING, DELETE ... RETURNING. This syntax allows returning all rows operated on by INSERT/REPLACE/DELETE statements (on a per-statement basis). Additionally, RETURNING is also supported in prepared statements and stored procedures.
In the current kernel version MySQL 8.0 20220330 and above, the following are supported: DELETE ... RETURNING, INSERT ... RETURNING, REPLACE ... RETURNING, and UPDATE ... RETURNING syntax, which allows returning the data rows operated on by the statement.
When using this feature, note the following points:
1. When RETURNING is used, the DELETE...RETURNING statement returns before-image data, while INSERT/REPLACE...RETURNING returns after-image data.
2. In INSERT/REPLACE scenarios, columns from the outer table are currently not visible to subqueries in the returning clause.
3. For INSERT/REPLACE RETURNING statements, if you need to return last_insert_id(), the value of last_insert_id() will be the value before the statement is successfully executed. To obtain the precise last_insert_id() value, it is recommended to use RETURNING to directly return the auto-increment column ID of the table.

Usage Instructions

INSERT... RETURNING

MySQL [test]> CREATE TABLE `t1` (id1 INT);
Query OK, 0 rows affected (0.04 sec)

MySQL [test]> CREATE TABLE `t2` (id2 INT);
Query OK, 0 rows affected (0.03 sec)

MySQL [test]> INSERT INTO  t2 (id2) values (1);
Query OK, 1 row affected (0.00 sec)

MySQL [test]> INSERT INTO t1 (id1) values (1) returning *, id1 * 2, id1 + 1, id1 * id1 as alias, (select * from t2); 
+------+---------+---------+-------+--------------------+
| id1  | id1 * 2 | id1 + 1 | alias | (select * from t2) |
+------+---------+---------+-------+--------------------+
|    1 |       2 |       2 |     1 |                  1 |
+------+---------+---------+-------+--------------------+
1 row in set (0.01 sec)

MySQL [test]> INSERT INTO t1 (id1) SELECT id2 from t2 returning id1;
+------+
| id1  |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

REPLACE ... RETURNING

MySQL [test]> CREATE TABLE t1(id1 INT PRIMARY KEY, val1 VARCHAR(1));
Query OK, 0 rows affected (0.04 sec)

MySQL [test]> CREATE TABLE t2(id2 INT PRIMARY KEY, val2 VARCHAR(1));
Query OK, 0 rows affected (0.03 sec)

MySQL [test]> INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MySQL [test]> REPLACE INTO t1 (id1, val1) VALUES (1, 'a');
Query OK, 1 row affected (0.00 sec)

MySQL [test]> REPLACE INTO t1 (id1, val1) VALUES (1, 'b') RETURNING *;
+-----+------+
| id1 | val1 |
+-----+------+
|   1 | b    |
+-----+------+
1 row in set (0.01 sec)

DELETE ... RETURNING

MySQL [test]> CREATE TABLE t1 (a int, b varchar(32));
Query OK, 0 rows affected (0.04 sec)

MySQL [test]> INSERT INTO t1 VALUES
    ->   (7,'ggggggg'), (1,'a'), (3,'ccc'),
    ->   (4,'dddd'), (1,'A'), (2,'BB'), (4,'DDDD'),
    ->   (5,'EEEEE'), (7,'GGGGGGG'), (2,'bb');
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

MySQL [test]> DELETE FROM t1 WHERE a=2 RETURNING *;
+------+------+
| a    | b    |
+------+------+
|    2 | BB   |
|    2 | bb   |
+------+------+
2 rows in set (0.01 sec)

MySQL [test]> DELETE FROM t1 RETURNING *;
+------+---------+
| a    | b       |
+------+---------+
|    7 | ggggggg |
|    1 | a       |
|    3 | ccc     |
|    4 | dddd    |
|    1 | A       |
|    4 | DDDD    |
|    5 | EEEEE   |
|    7 | GGGGGGG |
+------+---------+
8 rows in set (0.01 sec)

Stored Procedure

MySQL [test]> CREATE TABLE `t` (id INT);
Query OK, 0 rows affected (0.03 sec)

MySQL [test]> delimiter $$
MySQL [test]> CREATE PROCEDURE test(in param INT)
    -> BEGIN
    ->     INSERT INTO t (id) values (param) returning *;
    -> END$$
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> delimiter ;

MySQL [test]> CALL test(100);
+------+
| id   |
+------+
|  100 |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)
Topik selanjutnya: Tutorial Pengguna

Bantuan dan Dukungan

Apakah halaman ini membantu?

masukan