萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL SUM(float) 產生精度誤差的兩種解決方案

問題現象


SELECT SUM(price) FROM orders;
-- 預期 100.00,實際 100.00000000000001

原因:FLOAT/DOUBLE 為 IEEE 754 二進制浮點,無法精確表示十進制小數(如 0.1)。

方案一:改用 DECIMAL 精確類型(根治)


-- 1. 新增 DECIMAL 列
ALTER TABLE orders ADD price_decimal DECIMAL(16,6);

-- 2. 精確遷移數據(BINARY 強制二進制比較避免隱式轉換)
UPDATE orders SET price_decimal = CAST(price AS DECIMAL(16,6));

-- 3. 驗證無誤後切換
ALTER TABLE orders DROP COLUMN price, CHANGE price_decimal price DECIMAL(16,6);

DECIMAL(16,6) 表示共 16 位,小數 6 位,整數 10 位,滿足金額/價格精度需求。

方案二:查詢層臨時修正(不改表結構)


-- 利用 BINARY 運算符強制二進制上下文,MySQL 內部以高精度計算
SELECT SUM(BINARY price) FROM orders;

-- 或顯式 CAST
SELECT SUM(CAST(price AS DECIMAL(16,6))) FROM orders;

BINARY 是類型轉換運算符,非函數。原理:強制表達式在二進制精度上下文計算,減少浮點誤差累積。僅作查詢層緩解,不改變存儲精度。

實戰範例:多表聯查求和


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. 新表嚴禁用 FLOAT/DOUBLE 存儲金額,統一 DECIMAL(精度,刻度)
  2. 存儲「分」為單位整數(BIGINT),應用層除 100 顯示,徹底規避浮點
  3. 既有 FLOAT 欄位:方案一重構為準,方案二作臨時查詢修正
  4. 匯總報表建議落地物化視圖/匯總表,避免實時 SUM 大表
copyright © 萬盛學電腦網 all rights reserved