您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 實現 RETURNING 效果：用 Memory 表 + 觸發器捕獲 INSERT/UPDATE/DELETE 變更行

編輯：MySQL 綜合教學

MySQL 實現 RETURNING 效果：用 Memory 表 + 觸發器捕獲 INSERT/UPDATE/DELETE 變更行

PostgreSQL、Oracle、SQL Server 支援 RETURNING 子句，可在 DML 執行後直接返回受影響行的完整資料。MySQL 8.0 之前無原生支援，本文介紹「Memory 表 + AFTER 觸發器」的通用替代方案，適用 5.6+ 版本。

核心思路

建立三張 ENGINE=MEMORY 表，分別暫存插入、更新、刪除的行影像。
在目標表上建立 AFTER INSERT/UPDATE/DELETE 觸發器，將 NEW/OLD 行寫入對應 Memory 表。
DML 執行後，直接 SELECT * FROM memory_table 即可獲得「返回行」。

完整建表與觸發器腳本

-- 目標表
CREATE TABLE t1 (id INT PRIMARY KEY, log_time DATETIME(6)) ENGINE=InnoDB;

-- 三張 Memory 表（結構同步目標表，加主鍵防重）
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);

-- 觸發器
DELIMITER $$
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$$
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$$
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 ;

實戰演示

UPDATE 返回

TRUNCATE TABLE t1_update;
UPDATE t1 SET log_time = NOW() WHERE id < 15;
SELECT * FROM t1_update;
-- +----+----------------------------+
-- | id | log_time                   |
-- +----+----------------------------+
-- | 12 | 2024-01-15 10:30:00.000000 |
-- | 13 | 2024-01-15 10:30:00.000000 |
-- | 14 | 2024-01-15 10:30:00.000000 |
-- +----+----------------------------+

INSERT 返回

TRUNCATE TABLE t1_insert;
INSERT INTO t1 VALUES (1, NOW());
SELECT * FROM t1_insert;

DELETE 返回

TRUNCATE TABLE t1_delete;
DELETE FROM t1 WHERE id < 15;
SELECT * FROM t1_delete;

關鍵細節與避坑指南

項目	說明
Memory 表資料易失	重啟 MySQL 或 `TRUNCATE` 即清空，適合「即用即查」場景，不作持久化。
主鍵衝突用 REPLACE	同一 ID 連續多次 DML 時，`REPLACE` 保留最新影像；若需保留歷史，改用 `INSERT IGNORE` 或去除主鍵改用自增 ID。
大批量 DML 效能	觸發器逐行執行，百萬行級別會明顯拖慢寫入；大批量操作建議應用層自行 `SELECT ... WHERE id IN (...)` 回查。
事務隔離	觸發器在同一事務內，Memory 表變更隨事務提交/回滾而生效/消失，符合 ACID 預期。
權限需求	需 `TRIGGER` 權限（MySQL 5.7+ 需 `SUPER` 或 `TRIGGER` 權限）。

MySQL 8.0.20+ 原生支援

INSERT INTO t1 VALUES (1, NOW()) RETURNING id, log_time;
UPDATE t1 SET log_time = NOW() WHERE id = 1 RETURNING *;
DELETE FROM t1 WHERE id = 1 RETURNING *;

若環境已升級 8.0.20+，請直接使用原生語法，效能與可讀性更優。

此方案在不升級版本前提下，以極低成本彌補 MySQL 缺失 RETURNING 的短板，適合中小規模寫入場景。