MySQL FLOAT 類型求和精度問題解決方案

問題背景

MySQL 中 FLOAT 和 DOUBLE 屬於浮點數類型，採用 IEEE 754 標準存儲，存在固有的精度缺失問題。直接對 float 列執行 SUM() 聚合，結果往往出現 100.0000000001 這類誤差，導致財務報表、統計分析數據不準確。

兩種解決方案

方案一：修改表結構改用 DECIMAL（推薦，治本）

-- 修改列類型為精確小數類型
-- DECIMAL(16,6) 表示總共 16 位，小數點後 6 位
ALTER TABLE your_table MODIFY price DECIMAL(16,6);

操作步驟（大表安全變更）：

1. 新增臨時列：ALTER TABLE t ADD price_new DECIMAL(16,6);
2. 數據遷移：UPDATE t SET price_new = price;
3. 驗證無誤後重命名：ALTER TABLE t DROP price, CHANGE price_new price DECIMAL(16,6);

優點：從根源解決精度問題，後續所有計算均精確；缺點：存儲空間略大，大表變更需停機或用 pt-online-schema-change。

方案二：查詢時使用 BINARY 強制類型轉換（治標，無需改表）

-- 利用 BINARY 操作符強制二進制上下文計算
SELECT SUM(BINARY price) FROM orders;

原理解析

BINARY 不是函數，而是類型轉換操作符，它將後續表達式強制視為二進制字符串。在數值運算上下文中，這會改變 MySQL 的內部精度處理邏輯，減少浮點誤差累積。

實戰範例：多表關聯求和

SELECT SUM(a.number * BINARY b.price) AS total
FROM ler_card AS a
LEFT JOIN ler_items AS b ON a.itemId = b.itemId
WHERE a.user_id = 1;

方案對比與選擇建議

最佳實踐總結

1. 新建表：涉及金額、比率、精確計算的列，一律使用 DECIMAL(p,s)
2. 存量表：評估業務風險，核心財務表務必排期改為 DECIMAL
3. 只讀/歷史表：可用 BINARY 或 CAST(col AS DECIMAL(16,6)) 臨時修正
4. 應用層：Java 用 BigDecimal、Python 用 decimal.Decimal、Go 用 shopspring/decimal，避免浮點數流入資料庫