REPLACE INTO 是 MySQL 特有的「插入或替換」語句,邏輯等價於:
表 必須有 PRIMARY KEY 或 UNIQUE 索引,否則 REPLACE 退化為普通 INSERT,無替換語義。
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} (value_list),(...),...
| SET col_name = value, ...
| SELECT ...
| 返回值 | 含義 | 示例場景 |
|---|---|---|
| 1 | 插入 1 行,無衝突 | 新主鍵值 |
| 2 | 刪除 1 行 + 插入 1 行 | 主鍵衝突,單行替換 |
| >2 | 刪除多行 + 插入 1 行 | 多個唯一索引同時衝突 |
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, val INT);
REPLACE INTO t (id, val) VALUES (1, 100); -- id=1 不存在,插入
REPLACE INTO t (id, val) VALUES (1, 200); -- id=1 存在,刪除舊行插入新行
-- 自增計數器已推進到 2,下次 INSERT 將得到 id=2
影響:高頻 REPLACE 導致自增 ID 快速耗盡,大表可能觸及 BIGINT 上限。
CREATE TRIGGER trg_before_delete BEFORE DELETE ON t
FOR EACH ROW INSERT INTO audit_log VALUES (OLD.id, 'DELETED');
CREATE TRIGGER trg_after_insert AFTER INSERT ON t
FOR EACH ROW INSERT INTO audit_log VALUES (NEW.id, 'INSERTED');
REPLACE INTO t (id, val) VALUES (1, 300);
-- 審計表將記錄:DELETE + INSERT 兩條日志
CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child (id INT, pid INT, FOREIGN KEY (pid) REFERENCES parent(id) ON DELETE CASCADE);
REPLACE INTO parent (id) VALUES (1); -- 刪除 parent.id=1 將級聯刪除 child 表關聯行!
CREATE TABLE t (id INT PRIMARY KEY, cnt INT DEFAULT 0, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
REPLACE INTO t (id) VALUES (1); -- cnt=0, ts=當前時間
REPLACE INTO t (id, cnt) VALUES (1, cnt + 1); -- ❌ 錯誤!右側 cnt 被視為 DEFAULT(cnt)=0
-- 正確寫法需用子查詢或應用層計算
| 維度 | REPLACE INTO | INSERT ... ON DUPLICATE KEY UPDATE |
|---|---|---|
| 語義 | 刪除舊行 + 插入新行 | 更新現有行 |
| 自增 ID | 會跳號 | 不跳號 |
| 觸發器 | 觸發 DELETE + INSERT | 觸發 UPDATE |
| 外鍵級聯 | 觸發 CASCADE DELETE | 不觸發 |
| 部分列更新 | 不支持 (全行替換) | 支持 SET col=val |
| 性能 | 較差 (兩次操作) | 較好 (就地更新) |
| 複製安全性 | Statement 模式可能不一致 | Row 模式安全 |
INSERT INTO t (id, val) VALUES (1, 100) ON DUPLICATE KEY UPDATE val=VALUES(val); 避免上述所有陷阱REPLACE INTO t SELECT ... FROM staging 批量處理Innodb_rows_deleted + Innodb_rows_inserted 峰值,預警自增 ID 消耗速度-- 每日全量同步維度表 (幾千行),REPLACE 簡潔清晰
REPLACE INTO dim_product (product_id, name, category, price, updated_at)
SELECT product_id, name, category, price, NOW()
FROM staging_product
WHERE dt = '2024-01-15';
-- 對比 IODKU 寫法
INSERT INTO dim_product (product_id, name, category, price, updated_at)
SELECT product_id, name, category, price, NOW()
FROM staging_product
WHERE dt = '2024-01-15'
ON DUPLICATE KEY UPDATE
name=VALUES(name), category=VALUES(category), price=VALUES(price), updated_at=VALUES(updated_at);
此場景 REPLACE 可接受:無外鍵、無觸發器、全量覆蓋、行數少、自增 ID 無關緊要。