MySQL 浮点数和十进制数据类型的区别

Difference between float and decimal data type

提问by Hacker

What difference does it make when I use float and decimal data types in MySQL?.

在 MySQL 中使用浮点数和十进制数据类型有什么区别？。

When should I use which?

我什么时候应该使用哪个？

回答by kanap008

Thisis what I found when I had this doubt.

这是我在有这个疑问时发现的。

mysql> create table numbers (a decimal(10,2), b float);
mysql> insert into numbers values (100, 100);
mysql> select @a := (a/3), @b := (b/3), @a * 3, @b * 3 from numbers \G
*************************** 1. row ***************************
  @a := (a/3): 33.333333333
  @b := (b/3): 33.333333333333
@a + @a + @a: 99.999999999000000000000000000000
@b + @b + @b: 100

The decimal did exactly what's supposed to do on this cases, it truncated the rest, thus losing the 1/3 part.

小数点在这种情况下完成了应该做的事情，它截断了其余部分，从而失去了 1/3 部分。

So for sums the decimal is better, but for divisions the float is better, up to some point, of course. I mean, using DECIMAL will not give you a "fail proof arithmetic" in any means.

所以对于总和来说，小数更好，但对于除法，浮点数更好，当然，在某种程度上。我的意思是，使用 DECIMAL 不会以任何方式为您提供“失败证明算法”。

Hope this helps.

希望这可以帮助。

回答by Michael Petrotta

A "float" in most environments is a binary floating-point type. It can accurately store base-2 values (to a certain point), but cannot accurately store many base-10 (decimal) values. Floats are most appropriate for scientific calculations. They're notappropriate for most business-oriented math, and inappropriate use of floats will bite you. Many decimal values can't be exactly represented in base-2. 0.1can't, for instance, and so you see strange results like 1.0 - 0.1 = 0.8999999.

大多数环境中的“浮点数”是二进制浮点类型。它可以准确地存储基数为 2 的值（到某个点），但不能准确地存储许多基数为 10（十进制）的值。浮点数最适合科学计算。它们不适用于大多数面向商业的数学，并且不恰当地使用浮点数会让您感到厌烦。许多十进制值不能以基数 2 精确表示。0.1不能，例如，所以你会看到奇怪的结果，比如1.0 - 0.1 = 0.8999999.

Decimals store base-10 numbers. Decimal is an good type for most business math (but any built-in "money" type is more appropriate for financial calculations), where the range of values exceeds that provided by integer types, and fractional values are needed. Decimals, as the name implies, are designed for base-10 numbers - they can accurately store decimal values (again, to a certain point).

小数存储基数为 10 的数字。Decimal 是大多数商业数学的好类型（但任何内置的“货币”类型更适合财务计算），其中值的范围超过整数类型提供的范围，并且需要小数值。顾名思义，小数是为基数为 10 的数字设计的 - 它们可以准确地存储十进制值（再次，到某个点）。

回答by user2548100

MySQL recently changed they way they store the DECIMAL type. In the past they stored the characters (or nybbles) for each digit comprising an ASCII (or nybble) representation of a number - vs - a two's complement integer, or some derivative thereof.

MySQL 最近改变了他们存储DECIMAL 类型的方式。过去，他们为每个数字存储字符（或 nybble），包括数字的 ASCII（或 nybble）表示 - 与 - 二进制补码整数或其某些派生数。

The current storage format for DECIMAL is a series of 1,2,3,or 4-byte integers whose bits are concatenated to create a two's complement number with an implied decimal point, defined by you, and stored in the DB schema when you declare the column and specify it's DECIMAL size and decimal point position.

DECIMAL 的当前存储格式是一系列 1、2、3 或 4 字节整数，这些整数的位连接起来创建一个带有隐含小数点的二进制补码，由您定义，并在您声明时存储在 DB 模式中列并指定它的 DECIMAL 大小和小数点位置。

By way of example, if you take a 32-bit int you can store any number from 0 - 4,294,967,295. That will only reliably cover 999,999,999, so if you threw out 2 bits and used (1<<30 -1) you'd give up nothing. Covering all 9-digit numbers with only 4 bytes is more efficient than covering 4 digits in 32 bits using 4 ASCII characters, or 8 nybble digits. (a nybble is 4-bits, allowing values 0-15, more than is needed for 0-9, but you can't eliminate that waste by going to 3 bits, because that only covers values 0-7)

举例来说，如果您使用 32 位 int，您可以存储 0 - 4,294,967,295 之间的任何数字。那只能可靠地覆盖 999,999,999，因此如果您丢弃 2 位并使用 (1<<30 -1)，您将一无所获。仅用 4 个字节覆盖所有 9 位数字比使用 4 个 ASCII 字符或 8 个 nybble 数字覆盖 32 位中的 4 个数字更有效。（一个 nybble 是 4 位，允许值 0-15，比 0-9 需要的更多，但你不能通过使用 3 位来消除这种浪费，因为它只涵盖值 0-7）

The example used on the MySQL online docs uses DECIMAL(18,9) as an example. This is 9 digits ahead of and 9 digits behind the implied decimal point, which as explained above requires the following storage.

MySQL 在线文档中使用的示例使用 DECIMAL(18,9) 作为示例。这是隐含小数点前 9 位和后 9 位的数字，如上所述，这需要以下存储。

As 18 8-bit chars: 144 bits

作为 18 个 8 位字符：144 位

As 18 4-bit nybbles: 72 bits

作为 18 个 4 位 nybbles：72 位

As 2 32-bit integers: 64 bits

作为 2 个 32 位整数：64 位

Currently DECIMAL supports a max of 65 digits, as DECIMAL(M,D) where the largest value for M allowed is 65, and the largest value of D allowed is 30.

目前 DECIMAL 最多支持 65 位数字，如 DECIMAL(M,D)，其中 M 允许的最大值为 65，D 允许的最大值为 30。

So as not to require chunks of 9 digits at a time, integers smaller than 32-bits are used to add digits using 1,2 and 3 byte integers. For some reason that defies logic, signed, instead of unsigned ints were used, and in so doing, 1 bit gets thrown out, resulting in the following storage capabilities. For 1,2 and 4 byte ints the lost bit doesn't matter, but for the 3-byte int it's a disaster because an entire digit is lost due to the loss of that single bit.

为了一次不需要 9 个数字的块，小于 32 位的整数用于使用 1,2 和 3 字节整数添加数字。出于某种不符合逻辑的原因，使用了有符号而不是无符号整数，这样做时会抛出 1 位，从而产生以下存储功能。对于 1,2 和 4 字节整数，丢失的位无关紧要，但对于 3 字节整数，这是一场灾难，因为由于丢失了单个位，整个数字都丢失了。

With an 7-bit int: 0 - 99

使用 7 位整数：0 - 99

With a 15-bit int: 0 - 9,999

使用 15 位整数：0 - 9,999

With a 23-bit int: 0 - 999,999 (0 - 9,999,999 with a 24-bit int)

对于 23 位整数：0 - 999,999（对于 24 位整数为 0 - 9,999,999）

1,2,3 and 4-byte integers are concatenated together to form a "bit pool" DECIMAL uses to represent the number precisely as a two's complement integer. The decimal point is NOT stored, it is implied.

1、2、3 和 4 字节整数连接在一起形成一个“位池”，DECIMAL 用于将数字精确表示为二进制补码整数。小数点不存储，它是隐含的。

This means that no ASCII to int conversions are required of the DB engine to convert the "number" into something the CPU recognizes as a number. No rounding, no conversion errors, it's a real number the CPU can manipulate.

这意味着 DB 引擎不需要将 ASCII 转换为 int 来将“数字”转换为 CPU 识别为数字的内容。没有四舍五入，没有转换错误，这是 CPU 可以操作的实数。

Calculations on this arbitrarily large integer must be done in software, as there is no hardware support for this kind of number, but these libraries are very old and highly optimized, having been written 50 years ago to support IBM 370 Fortran arbitrary precision floating point data. They're still a lot slower than fixed-sized integer algebra done with CPU integer hardware, or floating point calculations done on the FPU.

这个任意大整数的计算必须在软件中完成，因为这种数字没有硬件支持，但是这些库非常古老并且高度优化，50年前编写的支持IBM 370 Fortran任意精度浮点数据. 它们仍然比用 CPU 整数硬件完成的固定大小的整数代数或在 FPU 上完成的浮点计算慢很多。

In terms of storage efficiency, because the exponent of a float is attached to each and every float, specifying implicitly where the decimal point is, it is massively redundant, and therefore inefficient for DB work. In a DB you already know where the decimal point is to go up front, and every row in the table that has a value for a DECIMAL column need only look at the 1 & only specification of where that decimal point is to be placed, stored in the schema as the arguments to a DECIMAL(M,D) as the implication of the M and the D values.

在存储效率方面，因为浮点数的指数附加到每个浮点数上，隐式指定小数点在哪里，它是大量冗余的，因此对于数据库工作效率低下。在 DB 中，您已经知道小数点在前面的位置，并且表中具有 DECIMAL 列值的每一行只需要查看小数点放置、存储位置的 1 和唯一规范在模式中作为 DECIMAL(M,D) 的参数作为 M 和 D 值的含义。

The many remarks found here about which format is to be used for various kinds of applications are correct, so I won't belabor the point. I took the time to write this here because whoever is maintaining the linked MySQL online documentation doesn't understand any of the above and after rounds of increasingly frustrating attempts to explain it to them I gave up. A good indication of how poorly they understood what they were writing is the very muddled and almost indecipherable presentation of the subject matter.

这里找到的许多关于哪种格式用于各种应用程序的评论是正确的，所以我不会详细说明这一点。我花时间在这里写这篇文章，因为维护链接的 MySQL 在线文档的人不理解上述任何内容，经过几轮越来越令人沮丧的尝试向他们解释它之后，我放弃了。他们对自己所写内容的理解有多差的一个很好的迹象是对主题的非常混乱且几乎难以理解的呈现。

As a final thought, if you have need of high-precision floating point computation, there've been tremendous advances in floating point code in the last 20 years, and hardware support for 96-bit and Quadruple Precision floatare right around the corner, but there are good arbitrary precision libraries out there if manipulation of the stored value is important.

最后，如果您需要高精度浮点计算，在过去的 20 年中浮点代码取得了巨大的进步，对 96 位和四倍精度浮点的硬件支持即将到来，但是如果对存储值的操作很重要，那么有很好的任意精度库。

回答by SingleNegationElimination

Not just specific to MySQL, the difference between float and decimal types is the way that they represent fractional values. Floating point types represent fractions in binary, which can only represent values as {m*2^n | m, n Integers}. values such as 1/5 cannot be precisely represented (without round off error). Decimal numbers are similarly limited, but represent numbers like {m*10^n | m, n Integers}. Decimals still cannot represent numbers like 1/3, but it is often the case in many common fields, like finance, that the expectation is that certain decimal fractions can always be expressed without loss of fidelity. Since a decimal number can represent a value like $0.20(one fifth of a dollar), it is preferred in those situations.

不仅特定于 MySQL，float 和 decimal 类型之间的区别在于它们表示小数值的方式。浮点类型以二进制表示分数，它只能将值表示为{m*2^n | m, n Integers}. 无法精确表示 1/5 等值（没有舍入误差）。十进制数也有类似的限制，但表示像{m*10^n | m, n Integers}. 小数仍然不能表示像 1/3 这样的数字，但是在许多常见领域（例如金融）中，通常情况是期望始终可以在不失保真度的情况下表达某些小数。由于十进制数可以表示$0.20（一美元的五分之一）之类的值，因此在这些情况下是首选。

回答by Skylar Saveland

decimal is for fixed quantities like money where you want a specific number of decimal places. Floats are for storing ... floating point precision numbers.

十进制用于固定数量，例如您需要特定小数位数的货币。浮点数用于存储......浮点精度数字。

回答by zloctb

mysql> CREATE TABLE num(id int ,fl float,dc dec(5,2));
Query OK, 0 rows affected (0.00 sec)


mysql> INSERT INTO num VALUES(1,13.75,13.75);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO num VALUES(2,13.15,13.15);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM num WHERE fl = 13.15;
Empty set (0.00 sec)

mysql> SELECT * FROM num WHERE dc = 13.15;
+------+-------+-------+
| id   | fl    | dc    |
+------+-------+-------+
|    2 | 13.15 | 13.15 |
+------+-------+-------+
1 row in set (0.00 sec)

mysql> SELECT SUM(fl) ,SUM(dc)  FROM num;
+--------------------+---------+
| SUM(fl)            | SUM(dc) |
+--------------------+---------+
| 26.899999618530273 |   26.90 |
+--------------------+---------+
1 row in set (0.00 sec)


mysql> SELECT * FROM num WHERE ABS(fl -  13.15)<0.01;
+------+-------+-------+
| id   | fl    | dc    |
+------+-------+-------+
|    2 | 13.15 | 13.15 |
+------+-------+-------+
1 row in set (0.00 sec)

回答by Tuomo B

I found this useful:

我发现这很有用：

Generally, Float values are good for scientific Calculations, but should not be used for Financial/Monetary Values. For Business Oriented Math, always use Decimal.

通常，浮点值适用于科学计算，但不应用于财务/货币值。对于面向商业的数学，请始终使用小数。

Source: http://code.rohitink.com/2013/06/12/mysql-integer-float-decimal-data-types-differences/

来源：http: //code.rohitink.com/2013/06/12/mysql-integer-float-decimal-data-types-differences/

回答by Think Big

Floating-Point Types (Approximate Value) - FLOAT, DOUBLE

浮点类型（近似值）- FLOAT、DOUBLE

The FLOAT and DOUBLE types represent approximatenumeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

FLOAT 和 DOUBLE 类型表示近似数值数据值。MySQL 对单精度值使用四个字节，对双精度值使用八个字节。

For FLOAT, the SQL standard permits an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. MySQL also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in a 4-byte single-precision FLOAT column. A precision from 24 to 53 results in an 8-byte double-precision DOUBLE column.

对于 FLOAT，SQL 标准允许在括号中的关键字 FLOAT 之后的位中可选地指定精度（但不是指数的范围）。MySQL 也支持这个可选的精度规范，但精度值仅用于确定存储大小。0 到 23 之间的精度会产生一个 4 字节的单精度 FLOAT 列。从 24 到 53 的精度会产生一个 8 字节的双精度 DOUBLE 列。

MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

MySQL 允许使用非标准语法：FLOAT(M,D) 或 REAL(M,D) 或 DOUBLE PRECISION(M,D)。这里的“(M,D)”是指最多可以存储M位的数值，其中D位可能在小数点后。例如，定义为 FLOAT(7,4) 的列在显示时看起来像 -999.9999。MySQL 在存储值时执行四舍五入，因此如果将 999.00009 插入 FLOAT(7,4) 列，则近似结果为 999.0001。

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies.

由于浮点值是近似值而不是作为精确值存储，因此尝试在比较中将它们视为精确值可能会导致问题。它们还受制于平台或实现依赖性。

For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of digits.

为获得最大的可移植性，需要存储近似数字数据值的代码应使用 FLOAT 或 DOUBLE PRECISION，不指定精度或位数。

https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html

https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html

Problems with Floating-Point Values

浮点值问题

Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values. A floating-point value as written in an SQL statement may not be the same as the value represented internally. Attempts to treat floating-point values as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. The FLOAT and DOUBLE data types are subject to these issues. For DECIMAL columns, MySQL performs operations with a precision of 65 decimal digits, which should solve most common inaccuracy problems.

浮点数有时会引起混淆，因为它们是近似值而不是存储为精确值。SQL 语句中写入的浮点值可能与内部表示的值不同。尝试在比较中将浮点值视为精确值可能会导致问题。它们还受制于平台或实现依赖性。FLOAT 和 DOUBLE 数据类型受这些问题的影响。对于 DECIMAL 列，MySQL 以 65 位十进制数字的精度执行操作，这应该可以解决大多数常见的不准确问题。

The following example uses DOUBLE to demonstrate how calculations that are done using floating-point operations are subject to floating-point error.

下面的示例使用 DOUBLE 来演示使用浮点运算完成的计算如何受到浮点错误的影响。

mysql> CREATE TABLE t1 (i INT, d1 DOUBLE, d2 DOUBLE);
mysql> INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
    -> (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
    -> (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
    -> (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
    -> (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
    -> (6, 0.00, 0.00), (6, -51.40, 0.00);

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b
    -> FROM t1 GROUP BY i HAVING a <> b;

+------+-------+------+
| i    | a     | b    |
+------+-------+------+
|    1 |  21.4 | 21.4 |
|    2 |  76.8 | 76.8 |
|    3 |   7.4 |  7.4 |
|    4 |  15.4 | 15.4 |
|    5 |   7.2 |  7.2 |
|    6 | -51.4 |    0 |
+------+-------+------+

The result is correct. Although the first five records look like they should not satisfy the comparison (the values of a and b do not appear to be different), they may do so because the difference between the numbers shows up around the tenth decimal or so, depending on factors such as computer architecture or the compiler version or optimization level. For example, different CPUs may evaluate floating-point numbers differently.

结果是正确的。虽然前五个记录看起来不应该满足比较（a 和 b 的值似乎没有不同），但它们可能会这样做，因为数字之间的差异出现在小数点后十位左右，具体取决于因素例如计算机体系结构或编译器版本或优化级别。例如，不同的 CPU 可能会以不同的方式评估浮点数。

If columns d1 and d2 had been defined as DECIMAL rather than DOUBLE, the result of the SELECT query would have contained only one row—the last one shown above.

如果将 d1 和 d2 列定义为 DECIMAL 而不是 DOUBLE，则 SELECT 查询的结果将只包含一行——如上所示的最后一行。

The correct way to do floating-point number comparison is to first decide on an acceptable tolerance for differences between the numbers and then do the comparison against the tolerance value. For example, if we agree that floating-point numbers should be regarded the same if they are same within a precision of one in ten thousand (0.0001), the comparison should be written to find differences larger than the tolerance value:

进行浮点数比较的正确方法是首先确定数字之间差异的可接受容差，然后与容差值进行比较。例如，如果我们同意浮点数在万分之一 (0.0001) 的精度内相同时应视为相同，则应编写比较以查找大于容差值的差异：

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
    -> GROUP BY i HAVING ABS(a - b) > 0.0001;
+------+-------+------+
| i    | a     | b    |
+------+-------+------+
|    6 | -51.4 |    0 |
+------+-------+------+
1 row in set (0.00 sec)

Conversely, to get rows where the numbers are the same, the test should find differences within the tolerance value:

相反，要获得数字相同的行，测试应该找到容差值内的差异：

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
    -> GROUP BY i HAVING ABS(a - b) <= 0.0001;
+------+------+------+
| i    | a    | b    |
+------+------+------+
|    1 | 21.4 | 21.4 |
|    2 | 76.8 | 76.8 |
|    3 |  7.4 |  7.4 |
|    4 | 15.4 | 15.4 |
|    5 |  7.2 |  7.2 |
+------+------+------+
5 rows in set (0.03 sec)

Floating-point values are subject to platform or implementation dependencies. Suppose that you execute the following statements:

浮点值受平台或实现依赖性的影响。假设您执行以下语句：

CREATE TABLE t1(c1 FLOAT(53,0), c2 FLOAT(53,0));
INSERT INTO t1 VALUES('1e+52','-1e+52');
SELECT * FROM t1;

On some platforms, the SELECT statement returns inf and -inf. On others, it returns 0 and -0.

在某些平台上，SELECT 语句返回 inf 和 -inf。在其他情况下，它返回 0 和 -0。

An implication of the preceding issues is that if you attempt to create a replication slave by dumping table contents with mysqldump on the master and reloading the dump file into the slave, tables containing floating-point columns might differ between the two hosts.

上述问题的一个含义是，如果您尝试通过在主服务器上使用 mysqldump 转储表内容并将转储文件重新加载到从属来创建复制从属，则两台主机之间包含浮点列的表可能会有所不同。

https://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html

https://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html

回答by Semra

If you are after performance and not precision, you should note that calculations with floats are much faster than decimals

如果你追求性能而不是精度，你应该注意浮点数的计算比小数快得多

回答by Semra

Hard & Fast Rule

硬性规定

If all you need to do is add, subtract or multiply the numbers you are storing, DECIMAL is best.

如果您需要做的只是对存储的数字进行加、减或乘，那么 DECIMAL 是最好的。

If you need to divide or do any other form of arithmetic or algebra on the data you're almost certainly going to be happier with float. Floating point libraries, and on Intel processors, the floating point processor itself, have TONs of operations to correct, fix-up, detect and handle the blizzard of exceptions that occur when doing typical math functions - especially transcendental functions.

如果您需要对数据进行除法或任何其他形式的算术或代数运算，您几乎肯定会更喜欢浮点数。浮点库，以及在英特尔处理器上，浮点处理器本身，有大量的操作来纠正、修复、检测和处理在执行典型数学函数时发生的大量异常——尤其是超越函数。

As for accuracy, I once wrote a budget system that computed the % contribution of each of 3,000+ accounts, for 3,600 budget units, by month to that unit's consolidation node, then based on that matrix of percentages (3,000 + x 12 x 3,600) I multiplied the amounts budgeted by the highest organizational nodes down to the next 3 levels of the organizational nodes, and then computed all (3,000 + 12) values for all 3,200 detail units from that. Millions and millions and millions of double precision floating point calculations, any one of which would throw off the roll-up of all of those projections in a bottoms-up consolidation back to the highest level in the organization.

至于准确性，我曾经写过一个预算系统，计算 3,000 多个帐户中每个帐户的百分比贡献，对于 3,600 个预算单位，按月到该单位的合并节点，然后基于该百分比矩阵 (3,000 + x 12 x 3,600)我将最高组织节点的预算金额乘以组织节点的下 3 个级别，然后从中计算所有 3,200 个详细单元的所有 (3,000 + 12) 值。数以百万计的双精度浮点计算，其中任何一项都会在自下而上的合并中将所有这些预测的汇总推回到组织中的最高级别。

The total floating point error after all of those calculations was ZERO. That was in 1986, and floating point libraries today are much, much better than they were back then. Intel does all of it's intermediate calculations of doubles in 80 bit precision, which all but eliminates rounding error. When someone tells you "it's floating point error" it's almost certainty NOT true.

所有这些计算后的总浮点误差为零。那是在 1986 年，今天的浮点库比当时要好得多。英特尔以 80 位精度执行所有双精度中间计算，这几乎消除了舍入误差。当有人告诉您“这是浮点错误”时，几乎可以肯定这不是真的。