MySQL 存儲過程實現 UPSERT（INSERT 或 UPDATE）模式

MySQL 存儲過程實現 UPSERT（INSERT 或 UPDATE）模式

在高併發場景下，常見需求：記錄存在則更新，不存在則插入。傳統方案需先 SELECT 判斷再決定 INSERT/UPDATE，存在競態條件且多一次網絡交互。MySQL 存儲過程配合 ROW_COUNT() 可在服務端原子化完成，顯著降低延遲。

核心邏輯

1. 先嘗試 UPDATE（主鍵/唯一鍵條件）。
2. 檢查 ROW_COUNT()：若為 0 則無匹配行，執行 INSERT。
3. 利用預處理語句（PREPARE/EXECUTE）動態構建 SQL，提升復用性。

存儲過程定義

DELIMITER //

CREATE PROCEDURE `pro_SaveData`(
    IN `p_insert_sql` VARCHAR(500),  -- INSERT 語句，如 'INSERT INTO t(id,name) VALUES(1,"a")'
    IN `p_update_sql` VARCHAR(500)   -- UPDATE 語句，如 'UPDATE t SET name="b" WHERE id=1'
)
BEGIN
    -- 1. 嘗試更新
    SET @v_upd = p_update_sql;
    PREPARE stmt FROM @v_upd;
    EXECUTE stmt;
    
    -- 2. 若無行受影響，執行插入
    IF ROW_COUNT() = 0 THEN
        SET @v_ins = p_insert_sql;
        PREPARE stmt FROM @v_ins;
        EXECUTE stmt;
    END IF;
    
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

應用層調用示例

C# (ADO.NET)

string insertSql = "INSERT INTO `table` (`id`, `name`) VALUES ('1', 'username')";
string updateSql = "UPDATE `table` SET `name` = 'table222' WHERE `id` = '1'";

int rows = data.ExecuteNonQuery(CommandType.StoredProcedure, 
    "CALL pro_SaveData(@ins, @upd)", 
    new MySqlParameter("@ins", insertSql),
    new MySqlParameter("@upd", updateSql));

Java (JDBC)

String callSql = "{CALL pro_SaveData(?, ?)}";
try (CallableStatement cs = conn.prepareCall(callSql)) {
    cs.setString(1, "INSERT INTO t(id,name) VALUES(1,'a')");
    cs.setString(2, "UPDATE t SET name='b' WHERE id=1");
    cs.execute();
}

進階優化：原生 UPSERT 語法（MySQL 8.0+ 推薦）

MySQL 8.0 引入標準 INSERT ... ON DUPLICATE KEY UPDATE，性能更優、語法更簡潔，無需存儲過程：

INSERT INTO `table` (`id`, `name`, `age`) 
VALUES (1, 'username', 25)
ON DUPLICATE KEY UPDATE 
    `name` = VALUES(`name`), 
    `age` = VALUES(`age`);

優勢： 單條 SQL 完成原子操作，優化器可直接走索引，避免存儲過程解析開銷，支持批量 VALUES (1,'a'), (2,'b') ... ON DUPLICATE KEY UPDATE。

適用場景對比

總結

現代 MySQL 應優先使用 ON DUPLICATE KEY UPDATE 實現 UPSERT。存儲過程方案僅作為舊版本兼容或極端複雜邏輯的備選。設計表結構時，務必確保業務鍵上有 唯一索引，否則 UPSERT 無法正確判斷衝突。