如何在数据库中存储小数：FLOAT、DECIMAL还是BIGINT？

前言

这里还是用前面的例子: 在线机票订票系统的数据表设计。此时已经完成了大部门字段的设计，可能如下:

1. CREATE TABLE flights (   
2.     flight_id INT AUTO_INCREMENT PRIMARY KEY,   
3.     flight_number VARCHAR(10),   
4.     departure_airport_code VARCHAR(3),   
5.     arrival_airport_code VARCHAR(3)
6. );

复制代码

考虑到还需要存储机票的订单金额，此时需要新增 price 字段来存储金额。金额一般都需要考虑小数，如99.99，而在MySQL中存储小数的方法其实有多种，比如:

• FLOAT/DOUBLE:浮点数范例，能够直接存储小数，同时基本上不需要考虑数据范围
  
• DECIMAL: 定点数范例，能够准确表现一个小数，比如直接存储99.99.
  
• BIGINT: 可以将小数转换为整数，比如将99.99 转换为 9999, 然后将其保存到数据库当中
  

这里我们该如何选择，才能让数据库在实现需求的同时，也保证数据库的高性能呢? 下面我们先充分了解下所有可能的选择，在这个底子上再来对比比较，从而选出最为符合的范例。
数据范例

FLOAT/DOUBLE

FLOAT 和 DOUBLE 是浮点数范例，分别用于表现单精度和双精度浮点数。单精度浮点数 FLOAT 使用 32 位来存储一个浮点数，双精度浮点数DOUBLE 使用 64 位来存储一个浮点数。
其特点是能够表现非常大或非常小的数值。下面举一个例子，创建一个简单的数据表，其中包含几个 FLOAT 和 DOUBLE 范例的字段，以及一些示例数据，以展示这些数据范例能够表现的非常大和非常小的数值。

1. CREATE TABLE floating_point_values (     
2.     id INT AUTO_INCREMENT PRIMARY KEY,     
3.     small_float FLOAT,     
4.     large_float FLOAT,     
5.     small_double DOUBLE,     
6.     large_double DOUBLE
7. );  

复制代码

在floating_point_values 的表，其中包含了四个列，具体含义如下:

• small_float：用 FLOAT 范例来存储非常小的数值。
  
• large_float：用 FLOAT 范例来存储非常大的数值。
  
• small_double：用 DOUBLE 范例来存储非常小的数值。
  
• large_double：用 DOUBLE 范例来存储非常大的数值。
  

然后往其中插入了一条记录，展示了 FLOAT 和 DOUBLE 范例能够表现的数值范围。

1. INSERT INTO floating_point_values (small_float, large_float,
2. small_double, large_double)
3. VALUES  (-3.402823466E+38,3.402823466E+38,
4. -1.7976931348623157E+308, 1.7976931348623157E+308);

复制代码

这些数值使用科学记数法表现，其中 E (或 e) 表现 10 的幂。比方，1.5E-45 表现 1.5 乘以 10 的 -45 次方，而 3.4E+38 表现 3.4 乘以 10 的 38 次方。
下面简单查看插入到数据库中的数据:

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

复制代码

可以看到，FLOAT 和 DOUBLE 可以表现非常小大概非常大的数值，使用该范例来存储数据，基本上不消考虑数据范围的问题。之以是能够存储这么大大概这么小的数，在于其底层是遵照 IEEE 754 标准，该标准定义了浮点数的存储和算术运算规则，这里关于 IEEE 754 标准的内容就不再展开，感兴趣的朋友可自行查阅资料。
但是这两种范例存在一个关键的问题，FLOAT 和 DOUBLE 不是准确的数值范例，可能会引入舍入偏差。下面是一个经典的例子，在抱负的情况下，0.1 + 0.2 应该等于 0.3，但是在执行这个查询时，结果可能会出人意料。
下面通过创建一个简单的表，展示这个例子，表结构定义如下:

1. -- 创建一个名为 prices 的表，其中包含两个 DOUBLE 类型的列
2. CREATE TABLE prices (   
3.     price1 DOUBLE,   
4.     price2 DOUBLE
5. );  
6. -- 插入一些可能导致精度问题的值
7. INSERT INTO prices (price1, price2) VALUES (0.1, 0.2);  

复制代码

通过查询表并查抄两个价格 price1 和 price2 的和是否等于 0.3:

1. mysql> SELECT price1, price2, price1 + price2 AS total, (price1 + price2) = 0.3 AS IsEqual FROM prices;
2. +--------+--------+---------------------+---------+
3. | price1 | price2 | total               | IsEqual |
4. +--------+--------+---------------------+---------+
5. |    0.1 |    0.2 | 0.30000000000000004 |       0 |
6. +--------+--------+---------------------+---------+

复制代码

可以看到 price1 和 price2 的总和（即 total 列）实际上是一个略大于 0.3 的值，这是由于浮点数的精度问题导致的。因此，IsEqual 列表现为 0，表明 (price1 + price2) 的结果并不等于 0.3。
之以是存在精度问题的缘故因由，这里也可以简单类比说明一下。在十进制系统中，有些分数不能准确表现（比方，1/3 等于 0.3333...，小数点后的 3 会无限重复）。
类似地，在二进制（基数为 2）系统中，有些十进制分数也不能被准确表现，因为它们在二进制中是无限循环小数。比方，十进制的 0.1 在二进制中会变成一个无限循环的分数：

1. 0.1 (十进制) = 0.0001100110011001100110011001100110011... (二进制)

复制代码

由于计算机内存是有限的，浮点数范例必须在某一点截断这个无限循环，这就导致了准确度的丧失。
以是如果在处理涉及金融和需要高精度的数据时，应该避免使用FLOAT/DOUBLE范例，从而由于这种范例的舍入偏差，导致系统出现问题。
DECIMAl

DECIMAL 范例与 FLOAT/DOUBLE 范例差异，DECIMAL 范例是一种定点数数据范例，它用于存储准确的数值，其在存储和计算时不会丢失精度，这使得它特别得当用于需要准确计算的应用场景。下面举个例子说明一下:

1. -- 创建一个名为 exact_prices 的表，其中包含两个 DECIMAL 类型的列
2. CREATE TABLE exact_prices (   
3.     price1 DECIMAL(10, 2),   
4.     price2 DECIMAL(10, 2)
5. );  
6. -- 插入精确的十进制值
7. INSERT INTO exact_prices (price1, price2) VALUES (0.1, 0.2);  -- 查询表并检查两个价格的和是否等于 0.3
8. SELECT price1, price2, price1 + price2 AS total, (price1 + price2) = 0.3 AS IsEqual FROM exact_prices;

复制代码

执行上述插入和查询应该得到以下结果：

1. +--------+--------+-------+---------+
2. | price1 | price2 | total | IsEqual |
3. +--------+--------+-------+---------+
4. |   0.10 |   0.20 |  0.30 |       1 |
5. +--------+--------+-------+---------+

复制代码

在这个例子中，与使用 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 位，而小数位数最多保存两位小数。下面举个例子来说明一下:

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

复制代码

这个例子中，financial_records 表的 transaction_amount 字段被定义为 DECIMAL(10, 2) 范例，意味着可以存储最多 8 位整数和 2 位小数的数值。比如下面这个数据就能正常存入:

1. INSERT INTO financial_records(transaction_amount) VALUES (12345.67);

复制代码

如果小数位数超过2位，此时将会进行四舍五入，最终只会保存2位小数，示比方下:

1. mysql> INSERT INTO financial_records (transaction_amount) VALUES (12345.688);
2. Query OK, 1 row affected, 1 warning (0.03 sec)
4. mysql> select * from financial_records;
5. +----+--------------------+
6. | id | transaction_amount |
7. +----+--------------------+
8. |  3 |           12345.69 |
9. +----+--------------------+
10. 1 row in set (0.03 sec)

复制代码

DECIMAL 还有一个注意点，便是其在 MySQL 中是有长度限定的。在 MySQL 中 DECIMAL 范例的最大精度（即数字的总位数，包罗小数点前后的数字）可以达到 65 位。这意味着 DECIMAL 范例的数字的总位数不能超过 65。
下面通过一个示例演示一下，看看 DECIMAL 的位数超过65位，此时会发生什么:

1. -- 创建一个名为 example_decimal 的表，包含一个 DECIMAL 类型的列
2. CREATE TABLE example_decimal (   
3.     amount DECIMAL(65, 30) -- 正确的 DECIMAL 定义
4. );  
6. -- 尝试创建一个 DECIMAL 列，其精度超过了最大限制
7. CREATE TABLE example_decimal_too_large (   
8.     amount DECIMAL(66, 30) -- 错误的 DECIMAL 定义，因为精度超过了 65
9. );

复制代码

可以看到，DECIMAL 的精度为65时，此时是能正常定义的；在第二个 CREATE TABLE 语句中，我们尝试创建一个精度为 66 的 DECIMAL 字段，此时将会报错，具体如下:

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

复制代码

从功能层面上看，DECIMAL可以在需要准确计算的场景，很好得满足我们的诉求。下面我们来看看 DECIMAL 和 FLOAT/DOUBLE 范例在存储空间和执行效率上的比较，看看在这准确性的要求下，我们会付出怎样的代价。
这里通过创建两个表，其中一个使用 DECIMAL 来存储数据，一个使用 DOUBLE 范例来存储数据:

1. -- 创建使用 DECIMAL 类型的表
2. CREATE TABLE decimal_table (   
3.     id INT AUTO_INCREMENT PRIMARY KEY,   
4.     decimal_col DECIMAL(30,10)
5. );
6. -- 创建使用 DOUBLE 类型的表
7. CREATE TABLE double_table (   
8.      id INT AUTO_INCREMENT PRIMARY KEY,   
9.      double_col DOUBLE
10. );  

复制代码

然后使用存储过程往其中插入100w条数据，存储过程展示如下:

1. -- 创建存储过程插入数据
2. DELIMITER $$
4. CREATE PROCEDURE InsertData()
5. BEGIN
6.   DECLARE i INT DEFAULT 0;
7.   WHILE i < 1000000 DO
8.     INSERT INTO decimal_table (decimal_col) VALUES (RAND() * 1000000000.1234567890);
9.     INSERT INTO double_table (double_col) VALUES (RAND() * 1000000000.1234567890);
10.     SET i = i + 1;
11.   END WHILE;
12. END$$
14. DELIMITER ;
16. -- 调用存储过程来插入数据
17. CALL InsertData();

复制代码

此时 decimal_table 和 double_table  表中都有100w条数据，我们下面将通过执行查询语句来比较 DECIMAL 和 FLOAT/DOUBLE 范例在存储效率、性能上的差异。
下面通过这个SQL查看 decimal_table 和 double_table 两张表占用的磁盘的大小:

1. 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');
2. +---------------+------------+
3. | Table         | Size in MB |
4. +---------------+------------+
5. | decimal_table |      38.56 |
6. | double_table  |      32.56 |
7. +---------------+------------+
8. 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 进行重复的数值运算，从而能够直观得展示二者的性能差异，脚本如下:

1. import mysql.connector
2. from time import time
5. # 定义数学运算函数
6. def math_operation_test(table_name, num_trials):
7.     if table_name == "decimal_table":
8.         update_query = f"UPDATE {table_name} SET decimal_col = decimal_col * 1.0000000001 WHERE id % 4 = 0;"
9.     else:
10.         update_query = f"UPDATE {table_name} SET double_col = double_col * 1.0000000001 WHERE id % 4 = 0;"
12.     total_time = 0
13.     for _ in range(num_trials):
14.         start_time = time()
15.         cursor.execute(update_query)
16.         cnx.commit()
17.         total_time += time() - start_time
18.     return total_time / num_trials, total_time  # 返回平均执行时间
21. if __name__ == '__main__':
23.     # 连接数据库
24.     db_config = {
25.         'user': 'user',
26.         'password': 'password',
27.         'host': 'hostname',
28.         'port': port,
29.         'database': 'test'
30.     }
31.     try:
32.         cnx = mysql.connector.connect(**db_config)
33.         cursor = cnx.cursor()
35.         # 定义测试次数
36.         num_trials = 100
38.         # 进行数学运算测试
39.         decimal_avg_time, decimal_total_time = math_operation_test('decimal_table', num_trials)
40.         double_avg_time, double_total_time = math_operation_test('double_table', num_trials)
42.         # 输出结果
43.         print(f"Average DECIMAL Math Operation Time: {decimal_avg_time} seconds")
44.         print(f"Average DOUBLE Math Operation Time: {double_avg_time} seconds")
46.         print(f"DECIMAL Math Total Operation Time: {decimal_total_time} seconds")
47.         print(f"DOUBLE Math Total Operation Time: {double_total_time} seconds")
49.         # 关闭连接
50.         cursor.close()
51.         cnx.close()
53.     except mysql.connector.Error as err:
54.         print(f"Error: {err}")

复制代码

在这个脚本中，我们多次调用数学运算函数，取其每次计算的平均值以及总计算耗时，获得 decimal 和 double 这两种范例在高频率数学运算的差异:

1. Total DECIMAL Math Operation Time: 135.5842161178589 seconds
2. Total DOUBLE Math Operation Time: 118.5552248954773 seconds
3. Average DECIMAL Math Operation Time: 1.355842161178589 seconds
4. 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倍，以分为单位来进行存储:

1. CREATE TABLE financial_transactions (     
2.     id INT AUTO_INCREMENT PRIMARY KEY,     
3.     amount BIGINT  -- 金额以分为单位存储     
4. );

复制代码

在这个表中，amount 字段将用来存储以分为单位的金额，这样100分等于1元。以是这里需要在程序中对其进行转换。在插入数据时，需要在应用层将金额转换为分：

1. def insert_transaction(cursor, amount):
2.     # 将金额转换为分
3.     amount_in_cents = int(amount * 100)
4.     # 插入数据
5.     cursor.execute("INSERT INTO financial_transactions (amount) VALUES (%s)", (amount_in_cents,))
6.    
7. if __name__ == '__main__':
8.     # 连接数据库
9.     db_config = {
10.         # ... 这里省略
11.     }
12.     # 获取数据库连接
13.     cnx = mysql.connector.connect(**db_config)
14.     cursor = cnx.cursor()
15.     # 插入金额为100.20
16.     insert_transaction(cursor, 100.20)
17.     cnx.commit()

复制代码

当查询要查询并表现金额时，此时需要将存储的分转换返来，这需要在应用层对其进行转换：

1. def get_transactions(cursor):
2.     cursor.execute("SELECT id, amount FROM financial_transactions")
3.     transactions = cursor.fetchall()
4.     for transaction in transactions:
5.         # 将分转换回金额
6.         amount_in_dollars = transaction[1] / 100.0
7.         print(
8.             f"Transaction ID: {transaction[0]}, Amount: {amount:.2f}")

复制代码

以是虽然 BIGINT 范例既能保证准确性，也具备高性能。但是这不可避免增长了代码的复杂性，并增长了堕落的可能性。
在使用 BIGINT 范例存储小数时，此时需要选择一个因数（比如100或1000）来乘以你的小数值，转换为整数。
这个因数决定了我们能够表现的小数精度。也必须确保在所有的计算中都使用同样的因数，这样才能保证计算的同等性和正确性。其次在查询展示时，也需要多一次转换才能获取到本来的数据。
从这里我们也可以看出来，没有一种数据范例是完善的，总是有权衡的。以是如果决定使用某种范例来存储数据时，需要确保自己已经综合考虑了各种因素。
怎么选择

在MySQL中存储小数方式，如上所述，可以选择FLOAT/DOULE 范例， DECIMAL 范例，也可以选择 BIGINT 范例。但是对于某一个业务场景来说，往往只有某一种范例在满足时间精度要求的前提下，在存储效率，查询性能上表现得更为精良。
下面我们再汇总上面的内容，展示这几种范例在数据精度，存储效率，查询性能，代码复杂性等几个维度上的差异:
范例/比较维度FLOAT/DOUBLEDECIMABIGINT数据精度某些数据存在精度问题准确存储小数准确存储小数存储效率4字节/8字节精度越高，存储效率越低8字节查询性能查询性能高相对较低查询性能高代码复杂度无需额外的数据转换无需额外的数据转换需额外的数据转换，更复杂可以看到，由于 FLOAT/DOUBLE 存在精度丢失的问题，以是对于需要准确计算的场景，如金额存储，此时就不得当使用该种范例；
但是如果不需要准确计算的话，使用 FLOAT/DOUBLE 范例就非常符合，其能够表现非常大或非常小的数值，同时性能也比较好。
而 DECIMAL 提供准确的小数点运算，没有浮点数的舍入偏差，就非常得当准确计算的场景，如金额存储。
相对的，DECIMAL 的运算可能会更慢。同时存储空间占用也会更多，尤其是在存储很多小数位数时。这也是其准确计算所需要付出的代价。
对于 BIGINT 范例，对于小数的存储，其存储效率高，同时性能也较好，但是不可避免会带来代码复杂性的提高，以是如果不是对性能特别敏感的场景，可以考虑使用 DECIMAL 范例。
回到最前面数据库设计的问题上，我这里这么金额字段的定义:

• FLOAT/DOUBLE: 需要考虑金额的准确存储，此时不考虑
  
• BIGINT: 并不需要进行大量的数学计算，对性能要求并没有特别敏感，不考虑
  
• DECIMAL: 能够对金额进行准确存储，能够较好得满足需求
  

以是综合考虑之下，最终选择了 DECIMAL 范例来对金额进行存储:

1. CREATE TABLE flights (   
2.     flight_id INT AUTO_INCREMENT PRIMARY KEY,   
3.     flight_number VARCHAR(10),   
4.     departure_airport_code VARCHAR(3),   
5.     arrival_airport_code VARCHAR(3),
6.     price DECIMAL(10, 2)
7. );

复制代码

DECIMAL(10, 2) 表现这个字段可以存储最高为10位数的数字，其中包罗2位小数。这意味着最大的金额可以是 99999999.99，基本能够满足需求。
总结

在小数范例存储上，MySQL提供了多种范例的选择，如 FLOAT,DOUBLE, DECIMAL, BIGINT 范例，都可以对小数进行存储。不过往往在某个场景下，只有一个范例才最满足要求。
本文具体介绍了各种数据范例，同时在数据精度，存储效率，执行效率，代码复杂性等维度上对其进行了比较，展示了其长处和相对应的缺点。
从而能够在数据库设计时，作出更正确，更高效的选择。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。