程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL返回更新值(RETURNING)

MySQL返回更新值(RETURNING)

編輯:MySQL綜合教程

MySQL返回更新值(RETURNING)


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

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved