MySQL 二進制日誌「Unsafe statement」警告：原因與解決方案

MySQL 二進制日誌「Unsafe statement」警告：原因與解決方案

在 MySQL 錯誤日誌中，你可能會大量看到類似以下的警告訊息：

Warning Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... SELECT... ON DUPLICATE KEY UPDATE is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are updated. This order cannot be predicted and may differ on master and the slave.

這條警告指出：當 binlog_format 設為 STATEMENT 時，若執行 INSERT ... SELECT ... ON DUPLICATE KEY UPDATE 且目標表擁有多個唯一鍵（或主鍵），MySQL 會將該語句標記為「不安全」。

為什麼會發生這個問題？

根據 MySQL 官方文件說明，當表擁有超過一個主鍵或唯一鍵時，INSERT ... ON DUPLICATE KEY UPDATE 的行為取決於儲存引擎檢查鍵的順序。這個順序是不確定的，可能導致主從庫更新的行不一致，進而破壞複製的一致性。

解決辦法

1. 修改 binlog 格式（推薦）：將 binlog_format 設為 ROW 或 MIXED。
   在 my.cnf 中添加：
   binlog_format=ROW 或 binlog_format=MIXED
   重啟 MySQL 服務生效。
2. 調整 SQL 語句：避免在擁有多個唯一鍵的表上使用 INSERT ... ON DUPLICATE KEY UPDATE，改用應用層邏輯先查後寫，或確保表只有單一唯一鍵。

建議

生產環境建議預設使用 ROW 或 MIXED 格式，以避免此類不安全語句導致主從數據不一致。若必須使用 STATEMENT 格式，請仔細審核所有包含 ON DUPLICATE KEY UPDATE 的 SQL。