【原創】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 `tr_t1_insert_after`$$

CREATE

   

    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 `tr_t1_update_after`$$

CREATE

   

    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 `tr_t1_delete_after`$$

CREATE

   

    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)

©著作權歸作者所有：來自51CTO部落格作者david_yeung的原創作品，如需轉載，請註明出處，否則將追究法律責任

MySQL觸發器SQL語句與特殊技巧