解決 MySQL「Incorrect integer value for column id」插入錯誤

MySQL 5.0+（特別是啟用 STRICT_TRANS_TABLES 的 5.1/5.5/5.6/5.7/8.0）在嚴格模式下，整數欄位不接受空字串 '' 作為預設值，會拋出 Incorrect integer value: '' for column 'id' at row 1。本文說明根因與三種解決方案。

錯誤重現場景

-- 表結構
CREATE TABLE test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT
);

-- 錯誤插入（PHP 範例）
INSERT INTO test VALUES ('', 'some content');  -- id 傳空字串
-- 或
INSERT INTO test (id, content) VALUES ('', 'content');

根因分析

• 嚴格模式：sql_mode 含 STRICT_TRANS_TABLES / STRICT_ALL_TABLES 時，型別不匹配直接報錯而非隱式轉換/截斷警告。
• 整數欄位：不接受 ''、'abc' 等非數值字串；若為 AUTO_INCREMENT 主鍵，應傳 NULL、0 或省略該欄位。

三種解決方案

方案一：修正應用程式碼（根治、推薦）

-- 正確寫法
INSERT INTO test (content) VALUES ('content');        -- 省略 id
INSERT INTO test (id, content) VALUES (NULL, 'content'); -- 明確傳 NULL
INSERT INTO test (id, content) VALUES (0, 'content');    -- 傳 0 觸發自增

PHP/程式語言層面：檢查變數為空時傳 NULL 而非 ''，或直接不帶入該參數。

方案二：調整 sql_mode（全域影響、需重啟）

編輯 my.cnf / my.ini：

[mysqld]
# 移除 STRICT_TRANS_TABLES
sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

SET SESSION sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
-- 執行插入
INSERT ...

SHOW VARIABLES LIKE 'sql_mode';
-- 或
SELECT @@sql_mode;