MySQL返回更新值（RETURNING）_MySQL

在写SQL中，经常会有诸如更新了一行记录，之后要获取更新过的这一行。 本身从程序来说，没啥难度，大不了把这行缓存起来，完了直接访问。 但是从数据库的角度出发，怎么能快速的拿出来，而又不对原表进行二次扫描？ 比如其他数据库提供了如下的语法来实现：

t_girl=# update t1 set log_time = now() where id in (1,2,3) returning *;
 id |          log_time          
----+----------------------------
  1 | 2014-11-26 11:06:53.555217
  2 | 2014-11-26 11:06:53.555217
  3 | 2014-11-26 11:06:53.555217
(3 rows)


UPDATE 3
Time: 6.991 ms
返回删除掉的行：

t_girl=# delete from t1 where id  truncate table t1_update;
Query OK, 0 rows affected (0.00 sec)


mysql> UPDATE t1 SET log_time = NOW() WHERE id  select * from t1_update;
+----+----------------------------+
| id | log_time                   |
+----+----------------------------+
| 12 | 2014-11-26 13:38:06.000000 |
| 13 | 2014-11-26 13:38:06.000000 |
| 14 | 2014-11-26 13:38:06.000000 |
+----+----------------------------+
3 rows in set (0.00 sec)
插入：

mysql> truncate table t1_insert;
Query OK, 0 rows affected (0.00 sec)


mysql> INSERT INTO t1 VALUES (1,NOW());
Query OK, 1 row affected (0.08 sec)
获取插入记录：

mysql> select * from t1_insert;
+----+----------------------------+
| id | log_time                   |
+----+----------------------------+
|  1 | 2014-11-26 13:38:06.000000 |
+----+----------------------------+
1 row in set (0.00 sec)
删除：

mysql> truncate table t1_delete;
Query OK, 0 rows affected (0.00 sec)


mysql> DELETE FROM t1 WHERE id  select * from t1_delete;
+----+----------------------------+
| id | log_time                   |
+----+----------------------------+
|  1 | 2014-11-26 13:38:06.000000 |
| 12 | 2014-11-26 13:38:06.000000 |
| 13 | 2014-11-26 13:38:06.000000 |
| 14 | 2014-11-26 13:38:06.000000 |
+----+----------------------------+
4 rows in set (0.00 sec)

返回删除掉的行：

t_girl=# delete from t1 where id 
 

 
mysql> select * from t1_delete;
+----+----------------------------+
| id | log_time                   |
+----+----------------------------+
|  1 | 2014-11-26 13:38:06.000000 |
| 12 | 2014-11-26 13:38:06.000000 |
| 13 | 2014-11-26 13:38:06.000000 |
| 14 | 2014-11-26 13:38:06.000000 |
+----+----------------------------+
4 rows in set (0.00 sec)