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

前言

這里還是用前面的例子: 在線機票訂票系統的數據表設計。此時已經完成了大部分字段的設計，可能如下:

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 類型，都可以對小數進行存儲。不過往往在某個場景下，只有一個類型才最滿足要求。

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

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

posted @ 2024-03-13 20:36 菜鳥額閱讀(2500) 評論(0) 收藏舉報

刷新頁面返回頂部

菜鳥額

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

前言

數據類型

FLOAT/DOUBLE

DECIMAl

整形

怎么選擇

總結

公告