MySQL returns the update value (RETURNING) and mysqlreturning

MySQL returns the update value (RETURNING) and mysqlreturning
In SQL writing, such as updating a row of records, you must obtain the updated row. From the perspective of the program itself, there is no difficulty, it is a big deal to cache this line, and direct access is completed. But from the perspective of the database, how can we quickly take it out without performing a secondary scan on the original table? For example, other databases provide the following syntax:


Returns the updated row:

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 3Time: 6.991 ms

Return the deleted row:

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

Return 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 can we implement it in MySQL?
I can create several memory tables to save the returned values, as shown below:

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 preceding three tables are created to store corresponding operations. T1_insert: Save and insert; t1_update: Save and update; t1_delete: Save and delete.


In this case, I will create the corresponding trigger.

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 ;

After the above tables and triggers are created, it is very easy to get the return value. I can query them directly from the above tables.


I will demonstrate it now:
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

Obtain update records:

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)