veDB MySQL 支持 RETURNING 语法。通过该语法，在执行 DML 操作后，能够获取插入、更新或删除的操作影响行，无需额外查询。本文介绍 RETURNING 语法的基本使用方法。

功能说明

RETURNING 语法支持在执行 INSERT、REPLACE、UPDATE、DELETE 等 DML 语句时，直接返回受影响的行数据（返回的结果可以是任意列、表达式或子查询），无需再通过额外的 SELECT 查询来获取结果信息。通过 RETURNING 语法，用户能够直观地查看数据库的操作结果，简化数据库的操作流程，减少 Client 跟 MySQL Server 的交互，提高数据库操作的效率和性能。

使用限制

版本限制：实例兼容版本为 MySQL 8.0，且内核版本大于等于 3.0.5.2，关于版本的详细介绍请参见版本号管理。

语法说明

• 语法定义

  DML	语法
  INSERT ... RETURNING	INSERT [LOW_PRIORITY [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] { {VALUES [AS row_alias[(col_alias [, col_alias] ...)]] [ON DUPLICATE KEY UPDATE assignment_list] [RETURNING select_expr [, select_expr] ...] INSERT [LOW_PRIORITY [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] SET assignment_list [AS row_alias[(col_alias [, col_alias] ...)]] [ON DUPLICATE KEY UPDATE assignment_list] [RETURNING select_expr [, select_expr] ...]
  REPLACE ... RETURNING	REPLACE [LOW_PRIORITY [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] { {VALUES VALUES row_constructor_list } [RETURNING select_expr [, select_expr] ...] REPLACE [LOW_PRIORITY [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] SET assignment_list [RETURNING select_expr [, select_expr] ...]
  DELETE ... RETURNING	DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias] [PARTITION (partition_name [, partition_name] ...)] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] [RETURNING select_expr [, select_expr] ...]
  UPDATE ... RETURNING	UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET assignment_list [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] [RETURNING select_expr [, select_expr] ...]

• 参数说明

  veDB MySQL 拓展了 MySQL INSERT、REPLACE、UPDATE、DELETE 语法，支持在原语法的基础上使用 RETURNING select_expr 列表，select_expr 可以是以下内容：

  • 列名。

  • 表达式。

  • *：表示展开为所有列。

  • 子查询，只能是返回一行结果的子查询。

  • Function（函数），不支持 Aggregate Function（聚合函数）。

  注意

  • 不支持 INSERT ... SELECT ...、REPLACE ... SELECT ... 、Multiple-Table delete、Multiple-Table update 语法。

  • 在任何语法中，别名（alias）不能使用 RETURNING。

示例

• INSRET RETURNING

  MySQL [test]> create table t1 (id bigint auto_increment, name varchar(64), primary key (id));
  Query OK, 0 rows affected (0.06 sec)
  
  MySQL [test]> insert into t1(name) values('Jackie Chan'),('Bruce Lee') returning *;
  +----+-------------+
  | id | name        |
  +----+-------------+
  |  1 | Jackie Chan |
  |  2 | Bruce Lee   |
  +----+-------------+
  2 rows in set (0.01 sec)
  

• DELETE RETURNING

  MySQL [test]> delete from t1 where name = 'Jackie Chan' returning *;
  +----+-------------+
  | id | name        |
  +----+-------------+
  |  1 | Jackie Chan |
  +----+-------------+
  1 row in set (0.01 sec)
  

• REPLACE RETURNING

  MySQL [test]> insert into t1(name) values('Jackie Chan') returning *;
  +----+-------------+
  | id | name        |
  +----+-------------+
  |  3 | Jackie Chan |
  +----+-------------+
  1 row in set (0.00 sec)
  
  MySQL [test]> replace into t1(id,name) values(3, 'lisi') returning *;
  +----+------+
  | id | name |
  +----+------+
  |  3 | lisi |
  +----+------+
  1 row in set (0.01 sec)
  

• UPDATE RETURNING

  MySQL [test]> create table t2 (id bigint primary key, cnt bigint);
  Query OK, 0 rows affected (0.03 sec)
  
  MySQL [test]> insert into t2 values(1,0),(2,0);
  Query OK, 2 rows affected (0.01 sec)
  Records: 2  Duplicates: 0  Warnings: 0
  
  MySQL [test]> update t2 set cnt = cnt + 1 returning *;
  +----+------+
  | id | cnt  |
  +----+------+
  |  1 |    1 |
  |  2 |    1 |
  +----+------+
  2 rows in set (0.00 sec)
  
  MySQL [test]> update t2 set cnt = cnt + 1 returning *;
  +----+------+
  | id | cnt  |
  +----+------+
  |  1 |    2 |
  |  2 |    2 |
  +----+------+
  2 rows in set (0.00 sec)