MySQL sql_mode 解決 UNSIGNED 減法溢位錯誤

問題現象

執行減法查詢時：

SELECT store - freez AS less FROM products;

報錯：

Data truncation: BIGINT UNSIGNED value is out of range in '(`products`.`store` - `products`.`freez`)'

原因分析

• store 與 freez 欄位型別為 BIGINT UNSIGNED（無號整數）。
• MySQL 預設啟用 NO_UNSIGNED_SUBTRACTION 未開啟，導致兩個無號數相減若結果為負數，超出無號數範圍（0 ~ 18446744073709551615）而報錯。
• 此行為由 sql_mode 控制。

解決方案

方案一：會話級臨時開啟（推薦用於單次查詢）

SET SESSION sql_mode = 'NO_UNSIGNED_SUBTRACTION';
SELECT store - freez AS less FROM products;

此模式下，無號數相減結果允許為負數，自動轉為有號數型別回傳。

方案二：全域永久生效（需重啟或配置檔）

-- 當前實例生效（重啟失效）
SET GLOBAL sql_mode = CONCAT(@@global.sql_mode, ',NO_UNSIGNED_SUBTRACTION');

-- 永久生效：編輯 /etc/my.cnf
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION"

方案三：查詢層級強制轉型（不依賴 sql_mode）

SELECT CAST(store AS SIGNED) - CAST(freez AS SIGNED) AS less FROM products;
-- 或
SELECT (store + 0) - (freez + 0) AS less FROM products;  -- 隱含轉型為有號數

sql_mode 相關知識補充

查看當前 sql_mode

SELECT @@session.sql_mode;
SELECT @@global.sql_mode;

最佳實踐建議

1. 資料庫設計階段：業務上不可能為負數的欄位才用 UNSIGNED，減法運算欄位建議用 SIGNED。
2. 遷移老系統時：統一在連線池初始化 SQL 中設定 SET SESSION sql_mode = '...'。
3. ORM 框架：Hibernate/MyBatis 可在連線 URL 或攔截器中注入 sessionVariables。