【原创】MySQL 返回更新值（RETURNING）

20309

　　在写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)