"Original" MySQL return update value (returning)

In write SQL, there are often such things as updating a row of records, and then getting the updated line. itself from the program, no difficulty, the big deal to cache this line, finished direct access. But from the database point of view, how can be quickly taken out, but not the original table two scan? For example, other databases provide the following syntax to implement:

Return the updated line:

t_girl=# Update T1 Set log_time = Now () where ID in (A/n) 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 3time:6.991 ms

To return a deleted line:

t_girl=# Delete from T1 where ID < 2 returning *;          ID | Log_time----+----------------------------1 | 2014-11-26 11:06:53.555217 (1 row) DELETE 1time:6.042 ms

Returns the inserted row:

t_girl=# INSERT INTO T1 select 1,now () returning *;          ID | Log_time----+----------------------------1 | 2014-11-26 11:07:40.431766 (1 row) INSERT 0 1time:6.107 mst_girl=#

How does it work in MySQL?

I can create several memory tables to hold these return values, as follows:

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);

The above set up three tables to store the corresponding operation. T1_insert save insert; t1_update save update; T1_delete save Delete.

That way, I'll create the corresponding trigger to complete.

DELIMITER $ $USE ' t_girl ' $ $DROP TRIGGER/*!50032 IF EXISTS */' tr_t1_insert_after ' $ $CREATE/*!50017 definer = ' root ' @ ' lo  Calhost ' */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 ' @ ' lo  Calhost ' */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 ' @ ' lo  Calhost ' */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;

Once the above tables and triggers have been created, it is very easy to get the return value, which I query directly from the above tables.

I will now show you:

Update:

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

Get update record:

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)

Insert:

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)

Get Insert Record:

Mysql> SELECT * from t1_insert;+----+----------------------------+| ID |  Log_time |+----+----------------------------+| 1 | 2014-11-26 13:38:06.000000 |+----+----------------------------+1 row in Set (0.00 sec)

Delete:

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)

Get Delete Record:

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)

This article is from "God, we don't see!" "Blog, be sure to keep this provenance http://yueliangdao0608.blog.51cto.com/397025/1584932

"Original" MySQL return update value (returning)