MySQL 返回更新值(RETURNING)

7 篇文章 0 订阅
4 篇文章 0 订阅
在写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 < 2 returning *;
 id |          log_time          
----+----------------------------
  1 | 2014-11-26 11:06:53.555217
(1 row)


DELETE 1
Time: 6.042 ms




返回插入后的行:
t_girl=# insert into t1 select 1,now() returning *;
 id |          log_time          
----+----------------------------
  1 | 2014-11-26 11:07:40.431766
(1 row)


INSERT 0 1
Time: 6.107 ms
t_girl=# 






那在MySQL里如何实现呢? 
我可以创建几张内存表来来保存这些返回值,如下:
CREATE TABLE t1_insert ENGINE MEMORY SELECT * FROM  t1 WHERE FALSE;
CREATE TABLE t1_update ENGINE MEMORY SELECT * FROM  t1 WHERE FALSE;
CREATE TABLE t1_delete ENGINE MEMORY SELECT * FROM  t1 WHERE FALSE;


ALTER TABLE t1_insert ADD PRIMARY KEY (id);
ALTER TABLE t1_update ADD PRIMARY KEY (id);
ALTER TABLE t1_delete ADD PRIMARY KEY (id);



以上建立了三张表来存放对应的操作。 t1_insert 保存插入;t1_update 保存更新;t1_delete 保存删除。


那这样的话,我来创建对应的触发器完成。




DELIMITER $$


USE `t_girl`$$


DROP TRIGGER /*!50032 IF EXISTS */ `tr_t1_insert_after`$$


CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `tr_t1_insert_after` AFTER INSERT ON `t1` 
    FOR EACH ROW BEGIN
      REPLACE INTO t1_insert VALUES (new.id,new.log_time);
    END;
$$


DELIMITER ;




DELIMITER $$


USE `t_girl`$$


DROP TRIGGER /*!50032 IF EXISTS */ `tr_t1_update_after`$$


CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `tr_t1_update_after` AFTER UPDATE ON `t1` 
    FOR EACH ROW BEGIN
      REPLACE INTO t1_update VALUES (new.id,new.log_time);
    END;
$$


DELIMITER ;




DELIMITER $$


USE `t_girl`$$


DROP TRIGGER /*!50032 IF EXISTS */ `tr_t1_delete_after`$$


CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `tr_t1_delete_after` AFTER DELETE ON `t1` 
    FOR EACH ROW BEGIN
      REPLACE INTO t1_delete VALUES (old.id,old.log_time);;
    END;
$$


DELIMITER ;




创建好了以上的表和触发器后, 拿到返回值就非常容易了, 我直接从以上几张表来查询就是。


我现在来演示:
更新:
mysql> truncate table t1_update;
Query OK, 0 rows affected (0.00 sec)


mysql> UPDATE t1 SET log_time = NOW() WHERE id < 15;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0



获取更新记录:
mysql> 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 < 15;
Query OK, 4 rows affected (0.01 sec)




获取删除记录:



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)



  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 6
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 6
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值