在写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 3Time: 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 < 2 returning *; id | log_time ----+---------------------------- 1 | 2014-11-26 11:06:53.555217(1 row)DELETE 1Time: 6.042 ms
mysql> select * from t1_delete;+----+----------------------------+| id | log_time |+----+----------------------------+| 1 | 2014-11-26 13:38:06.000000 || 12 | 2014-11-26 13:38:06.000<em style="color:transparent">本文来源[email protected]搞@^&代*@码网(</em>000 || 13 | 2014-11-26 13:38:06.000000 || 14 | 2014-11-26 13:38:06.000000 |+----+----------------------------+4 rows in set (0.00 sec)
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：MySQL返回更新值（RETURNING）_MySQL
转载请注明原文链接：MySQL返回更新值（RETURNING）_MySQL
