MySQL 8.0 前不支援 RETURNING 子句 (PostgreSQL/Oracle 原生支援)。若業務需在 UPDATE 後立即獲取變更行 (如審計日誌、緩存失效、異步通知),無法直接實現。本文方案:利用 MEMORY 引擎表 + AFTER 觸發器,捕獲 INSERT/UPDATE/DELETE 變更行,應用層讀取即得「返回值」,避免二次掃描主表。
其他資料庫原生支援:
-- PostgreSQL
UPDATE t1 SET log_time = now() WHERE id IN (1,2,3) RETURNING *;
-- 直接返回更新後的 3 行,無需額外 SELECT
MySQL 缺失此功能,常見繞過方案:
SELECT * FROM t WHERE id IN (...) (雙倍查詢、高併發下可能讀到其他事務修改)-- 1. 建立影子表 (結構與主表一致,引擎 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;
-- 2. 加主鍵保證 REPLACE INTO 不重複 (同一 ID 多次更新僅保留最新)
ALTER TABLE t1_insert ADD PRIMARY KEY (id);
ALTER TABLE t1_update ADD PRIMARY KEY (id);
ALTER TABLE t1_delete ADD PRIMARY KEY (id);
為什麼用 MEMORY 引擎? 全內存操作、無磁盤 I/O、自動隨重啟清空、支援 HASH 索引 (主鍵查找 O(1))、無事務開銷。
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$$
DELIMITER ;
DELIMITER $$
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 $$
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 ;
關鍵點: 使用REPLACE INTO而非INSERT,確保同一 ID 多次操作 (如連續 UPDATE) 在記憶體表中僅保留**最新狀態**,避免主鍵衝突報錯。
-- 1. 清空記憶體表 (每次業務操作前)
TRUNCATE TABLE t1_update;
-- 2. 執行業務 UPDATE
UPDATE t1 SET log_time = NOW() WHERE id < 15;
-- Query OK, 3 rows affected
-- 3. 從記憶體表讀取「返回值」
SELECT * FROM t1_update;
+----+----------------------------+
| id | log_time |
+----+----------------------------+
| 12 | 2024-01-15 13:38:06.000000 |
| 13 | 2024-01-15 13:38:06.000000 |
| 14 | 2024-01-15 13:38:06.000000 |
+----+----------------------------+
-- INSERT
TRUNCATE TABLE t1_insert;
INSERT INTO t1 VALUES (1, NOW());
SELECT * FROM t1_insert; -- 返回 id=1 新行
-- DELETE
TRUNCATE TABLE t1_delete;
DELETE FROM t1 WHERE id < 15;
SELECT * FROM t1_delete; -- 返回被刪除的 4 行 (OLD 值)
| 維度 | 表現 | 說明 |
|---|---|---|
| 寫入延遲增加 | ~5-10 微秒/行 | 觸發器上下文切換 + MEMORY 表寫入 |
| 讀取延遲 | < 1 毫秒 | 純內存 HASH 索引查找 |
| 併發安全性 | 高 | MEMORY 表表級鎖,但操作極快;高併發建議分庫/分表隔離 |
| 持久化 | 無 | 重啟丟失,僅作臨時捕獲,不適合審計存檔 |
| 大事務影響 | 記憶體膨脹風險 | 單事務影響行數 > 10 萬時,MEMORY 表可能佔用過多內存 |
| 複製/備份 | 不複製 | MEMORY 表不寫 Binlog,從庫無此數據 (符合預期) |
TRUNCATE 對應記憶體表,防止內存洩漏{table}_{op}_log (如 orders_update_log)SHOW TABLE STATUS LIKE 't1\_%\_log' 監控 Data_lengthRETURNING (8.0.20+ 支援 INSERT ... RETURNING,8.0.27+ 支援 UPDATE/DELETE ... RETURNING)-- 預存過程封裝 (簡化應用調用)
DELIMITER $$
CREATE PROCEDURE sp_update_with_returning(IN p_id INT, IN p_new_val VARCHAR(50))
BEGIN
TRUNCATE TABLE t1_update_log;
UPDATE t1 SET col = p_new_val WHERE id = p_id;
SELECT * FROM t1_update_log;
END$$
DELIMITER ;
-- 應用調用
CALL sp_update_with_returning(12, 'new_value');
MySQL 無原生 RETURNING 時,**觸發器 + MEMORY 表** 是最輕量、效能最優的模擬方案:
此方案在「寫入後即時同步緩存/發送 MQ/寫審計」等高頻場景價值極大,建議納入團隊 SQL 開發規範庫。