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

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


返回更新掉的行：

点击(此处)折叠或打开

1. t_girl=# update t1 set log_time = now() where id in (1,2,3) returning *;
   
2.  id | log_time
   
3. ----+----------------------------
   
4.   1 | 2014-11-26 11:06:53.555217
   
5.   2 | 2014-11-26 11:06:53.555217
   
6.   3 | 2014-11-26 11:06:53.555217
   
7. (3 rows)
   
8. 
   
9. 
   
10. UPDATE 3
    
11. Time: 6.991 ms

返回删除掉的行：

点击(此处)折叠或打开

1. t_girl=# delete from t1 where id < 2 returning *;
   
2.  id | log_time
   
3. ----+----------------------------
   
4.   1 | 2014-11-26 11:06:53.555217
   
5. (1 row)
   
6. 
   
7. 
   
8. DELETE 1
   
9. Time: 6.042 ms

返回插入后的行：

点击(此处)折叠或打开

1. t_girl=# insert into t1 select 1,now() returning *;
   
2.  id | log_time
   
3. ----+----------------------------
   
4.   1 | 2014-11-26 11:07:40.431766
   
5. (1 row)
   
6. 
   
7. 
   
8. INSERT 0 1
   
9. Time: 6.107 ms
   
10. t_girl=#

那在MySQL里如何实现呢？ 
我可以创建几张内存表来来保存这些返回值，如下：

点击(此处)折叠或打开

1. CREATE TABLE t1_insert ENGINE MEMORY SELECT * FROM t1 WHERE FALSE;
   
2. CREATE TABLE t1_update ENGINE MEMORY SELECT * FROM t1 WHERE FALSE;
   
3. CREATE TABLE t1_delete ENGINE MEMORY SELECT * FROM t1 WHERE FALSE;
   
4. 
   
5. 
   
6. ALTER TABLE t1_insert ADD PRIMARY KEY (id);
   
7. ALTER TABLE t1_update ADD PRIMARY KEY (id);
   
8. ALTER TABLE t1_delete ADD PRIMARY KEY (id);

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


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

点击(此处)折叠或打开

1. DELIMITER $$
   
2. 
   
3. 
   
4. USE `t_girl`$$
   
5. 
   
6. 
   
7. DROP TRIGGER /*!50032 IF EXISTS */ `tr_t1_insert_after`$$
   
8. 
   
9. 
   
10. CREATE
    
11.     /*!50017 DEFINER = 'root'@'localhost' */
    
12.     TRIGGER `tr_t1_insert_after` AFTER INSERT ON `t1`
    
13.     FOR EACH ROW BEGIN
    
14.       REPLACE INTO t1_insert VALUES (new.id,new.log_time);
    
15.     END;
    
16. $$
    
17. 
    
18. 
    
19. DELIMITER ;
    
20. 
    
21. 
    
22. 
    
23. 
    
24. DELIMITER $$
    
25. 
    
26. 
    
27. USE `t_girl`$$
    
28. 
    
29. 
    
30. DROP TRIGGER /*!50032 IF EXISTS */ `tr_t1_update_after`$$
    
31. 
    
32. 
    
33. CREATE
    
34.     /*!50017 DEFINER = 'root'@'localhost' */
    
35.     TRIGGER `tr_t1_update_after` AFTER UPDATE ON `t1`
    
36.     FOR EACH ROW BEGIN
    
37.       REPLACE INTO t1_update VALUES (new.id,new.log_time);
    
38.     END;
    
39. $$
    
40. 
    
41. 
    
42. DELIMITER ;
    
43. 
    
44. 
    
45. 
    
46. 
    
47. DELIMITER $$
    
48. 
    
49. 
    
50. USE `t_girl`$$
    
51. 
    
52. 
    
53. DROP TRIGGER /*!50032 IF EXISTS */ `tr_t1_delete_after`$$
    
54. 
    
55. 
    
56. CREATE
    
57.     /*!50017 DEFINER = 'root'@'localhost' */
    
58.     TRIGGER `tr_t1_delete_after` AFTER DELETE ON `t1`
    
59.     FOR EACH ROW BEGIN
    
60.       REPLACE INTO t1_delete VALUES (old.id,old.log_time);；
    
61.     END;
    
62. $$
    
63. 
    
64. 
    
65. DELIMITER ;

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


我现在来演示：
更新：

点击(此处)折叠或打开

1. mysql> truncate table t1_update;
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. 
   
4. 
   
5. mysql> UPDATE t1 SET log_time = NOW() WHERE id < 15;
   
6. Query OK, 3 rows affected (0.01 sec)
   
7. Rows matched: 3 Changed: 3 Warnings: 0

获取更新记录：

点击(此处)折叠或打开

1. mysql> select * from t1_update;
   
2. +----+----------------------------+
   
3. | id | log_time |
   
4. +----+----------------------------+
   
5. | 12 | 2014-11-26 13:38:06.000000 |
   
6. | 13 | 2014-11-26 13:38:06.000000 |
   
7. | 14 | 2014-11-26 13:38:06.000000 |
   
8. +----+----------------------------+
   
9. 3 rows in set (0.00 sec)

插入：

点击(此处)折叠或打开

1. mysql> truncate table t1_insert;
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. 
   
4. 
   
5. mysql> INSERT INTO t1 VALUES (1,NOW());
   
6. Query OK, 1 row affected (0.08 sec)

获取插入记录：

点击(此处)折叠或打开

1. mysql> select * from t1_insert;
   
2. +----+----------------------------+
   
3. | id | log_time |
   
4. +----+----------------------------+
   
5. | 1 | 2014-11-26 13:38:06.000000 |
   
6. +----+----------------------------+
   
7. 1 row in set (0.00 sec)

删除：

点击(此处)折叠或打开

1. mysql> truncate table t1_delete;
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. 
   
4. 
   
5. mysql> DELETE FROM t1 WHERE id < 15;
   
6. Query OK, 4 rows affected (0.01 sec)

获取删除记录：

点击(此处)折叠或打开

1. mysql> select * from t1_delete;
   
2. +----+----------------------------+
   
3. | id | log_time |
   
4. +----+----------------------------+
   
5. | 1 | 2014-11-26 13:38:06.000000 |
   
6. | 12 | 2014-11-26 13:38:06.000000 |
   
7. | 13 | 2014-11-26 13:38:06.000000 |
   
8. | 14 | 2014-11-26 13:38:06.000000 |
   
9. +----+----------------------------+
   
10. 4 rows in set (0.00 sec)