如何在資料庫中儲存小數：FLOAT、DECIMAL還是BIGINT？

菜鸟额發表於2024-03-13
資料庫Decimal

前言

這裡還是用前面的例子: 線上機票訂票系統的資料表設計。此時已經完成了大部分欄位的設計，可能如下:

CREATE TABLE flights (   
    flight_id INT AUTO_INCREMENT PRIMARY KEY,   
    flight_number VARCHAR(10),   
    departure_airport_code VARCHAR(3),   
    arrival_airport_code VARCHAR(3)
);

考慮到還需要儲存機票的訂單金額，此時需要新增 price 欄位來儲存金額。金額一般都需要考慮小數，如99.99，而在MySQL中儲存小數的方法其實有多種，比如:

  • FLOAT/DOUBLE:浮點數型別，能夠直接儲存小數，同時基本上不需要考慮資料範圍
  • DECIMAL: 定點數型別，能夠精確表示一個小數，比如直接儲存99.99.
  • BIGINT: 可以將小數轉換為整數，比如將99.99 轉換為 9999, 然後將其儲存到資料庫當中

這裡我們該如何選擇，才能讓資料庫在實現需求的同時，也保證資料庫的高效能呢? 下面我們先充分了解下所有可能的選擇，在這個基礎上再來對比比較，從而選出最為合適的型別。

資料型別

FLOAT/DOUBLE

FLOAT 和 DOUBLE 是浮點數型別，分別用於表示單精度和雙精度浮點數。單精度浮點數 FLOAT 使用 32 位來儲存一個浮點數，雙精度浮點數DOUBLE 使用 64 位來儲存一個浮點數。

其特點是能夠表示非常大或非常小的數值。下面舉一個例子，建立一個簡單的資料表，其中包含幾個 FLOAT 和 DOUBLE 型別的欄位，以及一些示例資料，以展示這些資料型別能夠表示的非常大和非常小的數值。

CREATE TABLE floating_point_values (     
    id INT AUTO_INCREMENT PRIMARY KEY,     
    small_float FLOAT,     
    large_float FLOAT,     
    small_double DOUBLE,     
    large_double DOUBLE 
);

在floating_point_values 的表，其中包含了四個列，具體含義如下:

  • small_float：用 FLOAT 型別來儲存非常小的數值。
  • large_float：用 FLOAT 型別來儲存非常大的數值。
  • small_double：用 DOUBLE 型別來儲存非常小的數值。
  • large_double：用 DOUBLE 型別來儲存非常大的數值。

然後往其中插入了一條記錄，展示了 FLOAT 和 DOUBLE 型別能夠表示的數值範圍。

INSERT INTO floating_point_values (small_float, large_float, 
small_double, large_double) 
VALUES  (-3.402823466E+38,3.402823466E+38, 
-1.7976931348623157E+308, 1.7976931348623157E+308);

這些數值使用科學記數法表示，其中 E (或 e) 表示 10 的冪。例如，1.5E-45 表示 1.5 乘以 10 的 -45 次方，而 3.4E+38 表示 3.4 乘以 10 的 38 次方。
下面簡單檢視插入到資料庫中的資料:

mysql> select * from floating_point_values;
+----+-------------+-------------+-------------------------+------------------------+
| id | small_float | large_float | small_double            | large_double           |
+----+-------------+-------------+-------------------------+------------------------+
|  8 | -3.40282e38 |  3.40282e38 | -1.7976931348623157e308 | 1.7976931348623157e308 |
+----+-------------+-------------+-------------------------+------------------------+
1 row in set (0.03 sec)

可以看到，FLOAT 和 DOUBLE 可以表示非常小或者非常大的數值，使用該型別來儲存資料，基本上不用考慮資料範圍的問題。之所以能夠儲存這麼大或者這麼小的數，在於其底層是遵循 IEEE 754 標準，該標準定義了浮點數的儲存和算術運算規則，這裡關於 IEEE 754 標準的內容就不再展開，感興趣的朋友可自行查閱資料。

但是這兩種型別存在一個關鍵的問題，FLOAT 和 DOUBLE 不是精確的數值型別，可能會引入舍入誤差。下面是一個經典的例子，在理想的情況下，0.1 + 0.2 應該等於 0.3，但是在執行這個查詢時，結果可能會出人意料。

下面透過建立一個簡單的表，展示這個例子，表結構定義如下:

-- 建立一個名為 prices 的表，其中包含兩個 DOUBLE 型別的列
CREATE TABLE prices (   
    price1 DOUBLE,   
    price2 DOUBLE 
);  
-- 插入一些可能導致精度問題的值
INSERT INTO prices (price1, price2) VALUES (0.1, 0.2);

透過查詢表並檢查兩個價格 price1 和 price2 的和是否等於 0.3:

mysql> SELECT price1, price2, price1 + price2 AS total, (price1 + price2) = 0.3 AS IsEqual FROM prices; 
+--------+--------+---------------------+---------+
| price1 | price2 | total               | IsEqual |
+--------+--------+---------------------+---------+
|    0.1 |    0.2 | 0.30000000000000004 |       0 |
+--------+--------+---------------------+---------+

可以看到 price1 和 price2 的總和（即 total 列）實際上是一個略大於 0.3 的值，這是由於浮點數的精度問題導致的。因此，IsEqual 列顯示為 0，表明 (price1 + price2) 的結果並不等於 0.3。

之所以存在精度問題的原因，這裡也可以簡單類比說明一下。在十進位制系統中，有些分數不能精確表示（例如，1/3 等於 0.3333...，小數點後的 3 會無限重複）。

類似地，在二進位制（基數為 2）系統中，有些十進位制分數也不能被精確表示，因為它們在二進位制中是無限迴圈小數。例如，十進位制的 0.1 在二進位制中會變成一個無限迴圈的分數：

0.1 (十進位制) = 0.0001100110011001100110011001100110011... (二進位制)

由於計算機記憶體是有限的，浮點數型別必須在某一點截斷這個無限迴圈，這就導致了精確度的喪失。

所以如果在處理涉及金融和需要高精度的資料時，應該避免使用FLOAT/DOUBLE型別，從而由於這種型別的舍入誤差，導致系統出現問題。

DECIMAl

DECIMAL 型別與 FLOAT/DOUBLE 型別不同，DECIMAL 型別是一種定點數資料型別，它用於儲存精確的數值，其在儲存和計算時不會丟失精度，這使得它特別適合用於需要精確計算的應用場景。下面舉個例子說明一下:

-- 建立一個名為 exact_prices 的表，其中包含兩個 DECIMAL 型別的列
CREATE TABLE exact_prices (   
    price1 DECIMAL(10, 2),   
    price2 DECIMAL(10, 2) 
 );  
 -- 插入精確的十進位制值
INSERT INTO exact_prices (price1, price2) VALUES (0.1, 0.2);  -- 查詢表並檢查兩個價格的和是否等於 0.3
SELECT price1, price2, price1 + price2 AS total, (price1 + price2) = 0.3 AS IsEqual FROM exact_prices;

執行上述插入和查詢應該得到以下結果：

+--------+--------+-------+---------+
| price1 | price2 | total | IsEqual |
+--------+--------+-------+---------+
|   0.10 |   0.20 |  0.30 |       1 |
+--------+--------+-------+---------+

在這個例子中，與使用 FLOAT/DOUBLE 型別不同，price1 和 price2 的和恰好是 0.30，這是因為 DECIMAL 型別提供了精確的數值計算而不會引入浮點數的舍入誤差。因此，IsEqual 列顯示為 1，表明 (price1 + price2) 的結果確實等於 0.3。所以涉及金融和需要高精度的資料時，DECIMAL 型別是個更好的選擇。

在宣告 DECIMAL 型別時，可以指定精度（總共的數字個數）和標度（小數點後的數字個數）。格式為 DECIMAL(M, D)，其中 M 是精度， 代表最多能夠儲存 D 是標度。

例如，DECIMAL(10, 2) 可以儲存最大為 99999999.99 的數值，其中 整數位數最多為 M - D，也就是 10 - 2 = 8 位，而小數位數最多儲存兩位小數。下面舉個例子來說明一下:

CREATE TABLE financial_records (     
    id INT AUTO_INCREMENT PRIMARY KEY,     
    transaction_amount DECIMAL(10, 2) -- 10位精度，其中包含2位小數 
);

這個例子中，financial_records 表的 transaction_amount 欄位被定義為 DECIMAL(10, 2) 型別，意味著可以儲存最多 8 位整數和 2 位小數的數值。比如下面這個資料就能正常存入:

INSERT INTO financial_records(transaction_amount) VALUES (12345.67);

如果小數位數超過2位，此時將會進行四捨五入，最終只會儲存2位小數，示例如下:

mysql> INSERT INTO financial_records (transaction_amount) VALUES (12345.688);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> select * from financial_records;
+----+--------------------+
| id | transaction_amount |
+----+--------------------+
|  3 |           12345.69 |
+----+--------------------+
1 row in set (0.03 sec)

DECIMAL 還有一個注意點，便是其在 MySQL 中是有長度限制的。在 MySQL 中 DECIMAL 型別的最大精度（即數字的總位數，包括小數點前後的數字）可以達到 65 位。這意味著 DECIMAL 型別的數字的總位數不能超過 65。

下面透過一個示例演示一下，看看 DECIMAL 的位數超過65位，此時會發生什麼:

-- 建立一個名為 example_decimal 的表，包含一個 DECIMAL 型別的列
CREATE TABLE example_decimal (   
    amount DECIMAL(65, 30) -- 正確的 DECIMAL 定義 
);  

-- 嘗試建立一個 DECIMAL 列，其精度超過了最大限制
CREATE TABLE example_decimal_too_large (   
    amount DECIMAL(66, 30) -- 錯誤的 DECIMAL 定義，因為精度超過了 65 
);

可以看到，DECIMAL 的精度為65時，此時是能正常定義的；在第二個 CREATE TABLE 語句中，我們嘗試建立一個精度為 66 的 DECIMAL 欄位，此時將會報錯，具體如下:

ERROR 1426 (42000): Too-big precision 66 specified for 'amount'. Maximum is 65.

從功能層面上看，DECIMAL可以在需要精確計算的場景，很好得滿足我們的訴求。下面我們來看看 DECIMAL 和 FLOAT/DOUBLE 型別在儲存空間和執行效率上的比較，看看在這精確性的要求下，我們會付出怎樣的代價。

這裡透過建立兩個表，其中一個使用 DECIMAL 來儲存資料，一個使用 DOUBLE 型別來儲存資料:

-- 建立使用 DECIMAL 型別的表
CREATE TABLE decimal_table (   
    id INT AUTO_INCREMENT PRIMARY KEY,   
    decimal_col DECIMAL(30,10) 
 );
 -- 建立使用 DOUBLE 型別的表
 CREATE TABLE double_table (   
     id INT AUTO_INCREMENT PRIMARY KEY,   
     double_col DOUBLE 
 );

然後使用儲存過程往其中插入100w條資料，儲存過程展示如下:

-- 建立儲存過程插入資料
DELIMITER $$

CREATE PROCEDURE InsertData()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 1000000 DO
    INSERT INTO decimal_table (decimal_col) VALUES (RAND() * 1000000000.1234567890);
    INSERT INTO double_table (double_col) VALUES (RAND() * 1000000000.1234567890);
    SET i = i + 1;
  END WHILE;
END$$

DELIMITER ;

-- 呼叫儲存過程來插入資料
CALL InsertData();

此時 decimal_table 和 double_table 表中都有100w條資料，我們下面將透過執行查詢語句來比較 DECIMAL 和 FLOAT/DOUBLE 型別在儲存效率、效能上的差異。

下面透過這個SQL檢視 decimal_table 和 double_table 兩張表佔用的磁碟的大小:

mysql> SELECT  table_name AS 'Table',   round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = 'test' AND table_name in ('decimal_table', 'double_table');
+---------------+------------+
| Table         | Size in MB |
+---------------+------------+
| decimal_table |      38.56 |
| double_table  |      32.56 |
+---------------+------------+
2 rows in set (0.02 sec)

可以看到 decimal_table 佔用的磁碟空間確實比 double_table 大一些，但是可以看到，其大小差距並不是很大，僅相差大約20%。

儘管 DECIMAL 使用了更多的位元組來確保精確度，但由於其最佳化的儲存方式，空間佔用並沒有顯著增加。

事實上從 MySQL 5.0 開始，DECIMAL 型別的儲存被最佳化為每4個位元組儲存9個十進位制數字（對於小數點前的數字和小數點後的數字都是如此）。

不過這也意味著每個 DECIMAL 數字的儲存大小是其精度的函式，而不是數值的大小。

下面我們來看看 DECIMAL 型別在效能上的表現。一般來說由於 DECIMAL 型別是用來進行精確的定點數計算的，它在處理和儲存資料時通常會比 DOUBLE 型別慢。

下面我們編寫一個Python指令碼，會對前面定義的decimal_table 和 double_table 進行重複的數值運算，從而能夠直觀得展示二者的效能差異，指令碼如下:

import mysql.connector
from time import time


# 定義數學運算函式
def math_operation_test(table_name, num_trials):
    if table_name == "decimal_table":
        update_query = f"UPDATE {table_name} SET decimal_col = decimal_col * 1.0000000001 WHERE id % 4 = 0;"
    else:
        update_query = f"UPDATE {table_name} SET double_col = double_col * 1.0000000001 WHERE id % 4 = 0;"

    total_time = 0
    for _ in range(num_trials):
        start_time = time()
        cursor.execute(update_query)
        cnx.commit()
        total_time += time() - start_time
    return total_time / num_trials, total_time  # 返回平均執行時間


if __name__ == '__main__':

    # 連線資料庫
    db_config = {
        'user': 'user',
        'password': 'password',
        'host': 'hostname',
        'port': port,
        'database': 'test'
    }
    try:
        cnx = mysql.connector.connect(**db_config)
        cursor = cnx.cursor()

        # 定義測試次數
        num_trials = 100

        # 進行數學運算測試
        decimal_avg_time, decimal_total_time = math_operation_test('decimal_table', num_trials)
        double_avg_time, double_total_time = math_operation_test('double_table', num_trials)

        # 輸出結果
        print(f"Average DECIMAL Math Operation Time: {decimal_avg_time} seconds")
        print(f"Average DOUBLE Math Operation Time: {double_avg_time} seconds")

        print(f"DECIMAL Math Total Operation Time: {decimal_total_time} seconds")
        print(f"DOUBLE Math Total Operation Time: {double_total_time} seconds")

        # 關閉連線
        cursor.close()
        cnx.close()

    except mysql.connector.Error as err:
        print(f"Error: {err}")

在這個指令碼中，我們多次呼叫數學運算函式，取其每次計算的平均值以及總計算耗時，獲得 decimal 和 double 這兩種型別在高頻率數學運算的差異:

Total DECIMAL Math Operation Time: 135.5842161178589 seconds
Total DOUBLE Math Operation Time: 118.5552248954773 seconds
Average DECIMAL Math Operation Time: 1.355842161178589 seconds
Average DOUBLE Math Operation Time: 1.185552248954773 seconds

可以看到DECIMAL 型別平均一次計算耗時需要1.38s，而DOUBLE 型別平均一次計算耗時為1.18s。

從這個對比結果可以看出 DECIMAL 型別的計算時間比 FLOAT 或 DOUBLE 型別的計算時間要長。這就是為了精確度而付出的效能代價。

整形

在實際開發中，BIGINT 型別也是常見的儲存小數的一種方式，其既能具備 FLOAT/DOUBLE 型別的高效能，同時也能夠擁有 DECIMAL 型別的準確性，使得其非常適合既需要高效能，也需要準確性的場景下使用。

這裡關於 BIGINT 型別的儲存效率，查詢效率的對比驗證，這裡就不再展開，可以參考上面的對比過程。下面透過一個例子，展示其在獲取高效能和精確性的情況下，不可避免帶來程式碼複雜性的問題。

使用 BIGINT 儲存小數的方法依賴於將小數轉換為整數，下面舉個例子來說明。這裡需要一個欄位來儲存訂單的金額，而這些金額通常有兩位小數。這裡使用 BIGINT 型別來儲存的一個方式，是將金額放大100倍，以分為單位來進行儲存:

CREATE TABLE financial_transactions (     
    id INT AUTO_INCREMENT PRIMARY KEY,     
    amount BIGINT  -- 金額以分為單位儲存     
 );

在這個表中，amount 欄位將用來儲存以分為單位的金額，這樣100分等於1元。所以這裡需要在程式中對其進行轉換。在插入資料時，需要在應用層將金額轉換為分：

def insert_transaction(cursor, amount):
    # 將金額轉換為分
    amount_in_cents = int(amount * 100)
    # 插入資料
    cursor.execute("INSERT INTO financial_transactions (amount) VALUES (%s)", (amount_in_cents,))
    
if __name__ == '__main__':
    # 連線資料庫
    db_config = {
        # ... 這裡省略
    }
    # 獲取資料庫連線
    cnx = mysql.connector.connect(**db_config)
    cursor = cnx.cursor()
    # 插入金額為100.20
    insert_transaction(cursor, 100.20)
    cnx.commit()

當查詢要查詢並顯示金額時，此時需要將儲存的分轉換回來，這需要在應用層對其進行轉換：

def get_transactions(cursor):
    cursor.execute("SELECT id, amount FROM financial_transactions")
    transactions = cursor.fetchall()
    for transaction in transactions:
        # 將分轉換回金額
        amount_in_dollars = transaction[1] / 100.0
        print(
            f"Transaction ID: {transaction[0]}, Amount: {amount:.2f}")

所以雖然 BIGINT 型別既能保證精確性，也具備高效能。但是這不可避免增加了程式碼的複雜性，並增加了出錯的可能性。

在使用 BIGINT 型別儲存小數時，此時需要選擇一個因數（比如100或1000）來乘以你的小數值，轉換為整數。

這個因數決定了我們能夠表示的小數精度。也必須確保在所有的計算中都使用同樣的因數，這樣才能保證計算的一致性和正確性。其次在查詢展示時，也需要多一次轉換才能獲取到原本的資料。

從這裡我們也可以看出來，沒有一種資料型別是完美的，總是有權衡的。所以如果決定使用某種型別來儲存資料時，需要確保自己已經綜合考慮了各種因素。

怎麼選擇

在MySQL中儲存小數方式，如上所述，可以選擇FLOAT/DOULE 型別， DECIMAL 型別，也可以選擇 BIGINT 型別。但是對於某一個業務場景來說，往往只有某一種型別在滿足時間精度要求的前提下，在儲存效率，查詢效能上表現得更為優秀。

下面我們再彙總上面的內容，展示這幾種型別在資料精度，儲存效率，查詢效能，程式碼複雜性等幾個維度上的差異:

型別/比較維度 FLOAT/DOUBLE DECIMA BIGINT
資料精度 某些資料存在精度問題 精確儲存小數 精確儲存小數
儲存效率 4位元組/8位元組 精度越高，儲存效率越低 8位元組
查詢效能 查詢效能高 相對較低 查詢效能高
程式碼複雜度 無需額外的資料轉換 無需額外的資料轉換 需額外的資料轉換，更復雜

可以看到，由於 FLOAT/DOUBLE 存在精度丟失的問題，所以對於需要精確計算的場景，如金額儲存，此時就不適合使用該種型別；

但是如果不需要精確計算的話，使用 FLOAT/DOUBLE 型別就非常合適，其能夠表示非常大或非常小的數值，同時效能也比較好。

而 DECIMAL 提供精確的小數點運算，沒有浮點數的舍入誤差，就非常適合精確計算的場景，如金額儲存。

相對的，DECIMAL 的運算可能會更慢。同時儲存空間佔用也會更多，尤其是在儲存很多小數位數時。這也是其精確計算所需要付出的代價。

對於 BIGINT 型別，對於小數的儲存，其儲存效率高，同時效能也較好，但是不可避免會帶來程式碼複雜性的提高，所以如果不是對效能特別敏感的場景，可以考慮使用 DECIMAL 型別。

回到最前面資料庫設計的問題上，我這裡這麼金額欄位的定義:

  • FLOAT/DOUBLE: 需要考慮金額的精確儲存，此時不考慮
  • BIGINT: 並不需要進行大量的數學計算，對效能要求並沒有特別敏感，不考慮
  • DECIMAL: 能夠對金額進行精確儲存，能夠較好得滿足需求

所以綜合考慮之下，最終選擇了 DECIMAL 型別來對金額進行儲存:

CREATE TABLE flights (   
    flight_id INT AUTO_INCREMENT PRIMARY KEY,   
    flight_number VARCHAR(10),   
    departure_airport_code VARCHAR(3),   
    arrival_airport_code VARCHAR(3),
    price DECIMAL(10, 2) 
);

DECIMAL(10, 2) 表示這個欄位可以儲存最高為10位數的數字，其中包括2位小數。這意味著最大的金額可以是 99999999.99，基本能夠滿足需求。

總結

在小數型別儲存上，MySQL提供了多種型別的選擇，如 FLOAT,DOUBLE, DECIMAL, BIGINT 型別，都可以對小數進行儲存。不過往往在某個場景下，只有一個型別才最滿足要求。

本文詳細介紹了各種資料型別，同時在資料精度，儲存效率，執行效率，程式碼複雜性等維度上對其進行了比較，展示了其長處和相對應的缺點。

從而能夠在資料庫設計時，作出更準確，更高效的選擇。

相關文章