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)返回刪除掉的行:
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
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)