MySQL 存储统计数据，我需要 DECIMAL、FLOAT 还是 DOUBLE？

Storing statistical data, do I need DECIMAL, FLOAT or DOUBLE?

提问by PEPLOVE

I am creating for fun, but I still want to approach it seriously, a site which hosts various tests. With these tests I hope to collect statistical data.

我是为了好玩而创建的，但我仍然想认真对待它，一个承载各种测试的站点。我希望通过这些测试收集统计数据。

Some of the data will include the percentage of the completeness of the tests as they are timed. I can easily compute the percentage of the tests but I would like true data to be returned as I store the various different values concerning the tests on completion.

一些数据将包括测试完成时间的百分比。我可以很容易地计算出测试的百分比，但我希望返回真实数据，因为我在完成时存储了有关测试的各种不同值。

Most of the values are, in PHP floats, so my question is, if I want true statistical data should I store them in MYSQL as FLOAT, DOUBLE or DECIMAL.

大多数值都在 PHP 浮点数中，所以我的问题是，如果我想要真正的统计数据，我应该将它们作为 FLOAT、DOUBLE 或 DECIMAL 存储在 MYSQL 中。

I would like to utilize MYSQL'S functions such as AVG()and LOG10()as well as TRUNCATE(). For MYSQL to return true data based off of my values that I insert, what should I use as the database column choice.

我想利用 MYSQL 的功能，例如AVG()和LOG10()以及TRUNCATE(). 为了让 MYSQL 根据我插入的值返回真实数据，我应该使用什么作为数据库列选择。

I ask because some numbers may or may not be floats such as, 10, 10.89, 99.09, or simply 0. But I would like true and valid statistical data to be returned.

我问是因为有些数字可能是也可能不是浮点数，例如 10、10.89、99.09 或只是 0。但我希望返回真实有效的统计数据。

Can I rely on floating point math for this?

我可以依靠浮点数学吗？

EDIT

编辑

I know this is a generic question, and I apologise extensively, but for non mathematicians like myself, also I am not a MYSQL expert, I would like an opinion of an expert in this field.

我知道这是一个通用的问题，我深表歉意，但对于像我这样的非数学家，我也不是 MYSQL 专家，我想请教该领域专家的意见。

I have done my research but I still feel I have a clouded judgement on the matter. Again I apologise if my question is off topic or not suitable for this site.

我已经完成了我的研究，但我仍然觉得我对此事的判断模糊不清。如果我的问题偏离主题或不适合本网站，我再次道歉。

回答by Linger

This linkdoes a good job of explaining what you are looking for. Here is what is says:

此链接很好地解释了您要查找的内容。这是什么意思：

All these three Types, can be specified by the following Parameters (size, d). Where size is the total size of the String, and d represents precision. E.g To store a Number like 1234.567, you will set the Datatype to DOUBLE(7, 3) where 7 is the total number of digits and 3 is the number of digits to follow the decimal point.

所有这三种类型，都可以通过以下参数（大小，d）指定。其中 size 是 String 的总大小，d 表示精度。例如，要存储像 1234.567 这样的数字，您需要将数据类型设置为 DOUBLE(7, 3)，其中 7 是总位数，3 是小数点后面的位数。

FLOAT and DOUBLE, both represent floating point numbers. A FLOAT is for single-precision, while a DOUBLE is for double-precision numbers. 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 is accurate to approximately 7 decimal places, and DOUBLE upto 14.

FLOAT 和 DOUBLE，都代表浮点数。FLOAT 用于单精度，而 DOUBLE 用于双精度数字。0 到 23 之间的精度会产生一个 4 字节的单精度 FLOAT 列。从 24 到 53 的精度会产生一个 8 字节的双精度 DOUBLE 列。FLOAT 精确到大约小数点后 7 位，DOUBLE 精确到 14。

Decimal's declaration and functioning is similar to Double. But there is one big difference between floating point values and decimal (numeric) values. We use DECIMAL data type to store exact numeric values, where we do not want precision but exact and accurate values. A Decimal type can store a Maximum of 65 Digits, with 30 digits after decimal point.

Decimal 的声明和功能类似于 Double。但是浮点值和十进制（数字）值之间存在很大差异。我们使用 DECIMAL 数据类型来存储精确的数值，我们不想要精度，而是精确和准确的值。Decimal 类型最多可以存储 65 位数字，小数点后保留 30 位数字。

So, for the most accurate and precise value, Decimal would be the best option.

因此，对于最准确和精确的值，十进制将是最佳选择。

回答by Simon Byrne

Unless you are storing decimal data (i.e. currency), you should use a standard floating point type (FLOAT or DOUBLE). DECIMAL is a fixed point type, so can overflow when computing things like SUM, and will be ridiculously inaccurate for LOG10.

除非您要存储十进制数据（即货币），否则您应该使用标准浮点类型（FLOAT 或 DOUBLE）。DECIMAL 是一个定点类型，所以在计算 SUM 之类的东西时可能会溢出，并且对于 LOG10 会非常不准确。

There is nothing "less precise" about binary floating point types, in fact, they will be much more accurate (and faster) for your needs. Go with DOUBLE.

二进制浮点类型没有什么“不那么精确”，事实上，它们会更准确（和更快）满足您的需求。和 DOUBLE 一起去。

回答by Timeless

Decimal: Fixed-Point Types (ExactValue). Use it when you care about exact precision like money.

十进制：定点类型（精确值）。当您像金钱一样关心精确度时使用它。

Example: salary DECIMAL(8,2), 8 is the total number of digits, 2 is the number of decimal places. salarywill be in the range of -999999.99to 999999.99

示例：salary DECIMAL(8,2), 8 为总位数，2 为小数位数。salary将在范围内-999999.99，以999999.99

---

---

Float, Double: Floating-Point Types (ApproximateValue). Float uses 4 bytes to represent value, Double uses 8 bytes to represent value.

Float, Double: 浮点类型（近似值）。Float用4个字节表示值，Double用8个字节表示值。

Example: percentage FLOAT(5,2), same as the type decimal, 5 is total digits and 2 is the decimal places. percentagewill store values between -999.99to 999.99.

例：percentage FLOAT(5,2)，同类型decimal，5为总位数，2为小数位。percentage将存储之间的值-999.99来999.99。

Note that they are approximatevalue, in this case:

请注意，它们是近似值，在这种情况下：

• Value like 1 / 3.0 = 0.3333333...will be stored as 0.33(2 decimal place)
• Value like 33.009will be stored as 33.01(rounding to 2 decimal place)

• 值 like1 / 3.0 = 0.3333333...将存储为0.33（小数点后两位）
• 值 like33.009将存储为33.01（四舍五入到小数点后两位）

回答by waterinusa

Put it simply, Float and double are not as precise as decimal. decimal is recommended for money related number input.(currency and salary). Another point need to point out is: Do NOT compare float number using "=","<>", because float numbers are not precise.

简单来说，Float和double没有decimal那么精确。建议使用十进制用于与货币相关的数字输入。（货币和工资）。还有一点需要指出的是：不要使用“=”，“<>”来比较浮点数，因为浮点数并不精确。

回答by Joey

Linger: The website you mention and quote has IMO some imprecise info that made me confused. In the docs I read that when you declare a float or a double, the decimal point is in fact NOT included in the number. So it is not the number of chars in a string but all digits used.

Linger：你提到和引用的网站有 IMO 一些不精确的信息，让我感到困惑。在我读到的文档中，当您声明浮点数或双精度数时，小数点实际上不包含在数字中。所以它不是字符串中的字符数，而是使用的所有数字。

Compare the docs: "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" http://dev.mysql.com/doc/refman/5.1/en/floating-point-types.html

比较文档："DOUBLE PRECISION(M,D).. 这里，"(M,D)" 表示值最多可以存储 M 位数字，其中 D 位可能在小数点后。例如，定义为 FLOAT(7,4) 的列在显示时看起来像 -999.9999” http://dev.mysql.com/doc/refman/5.1/en/floating-point-types.html

Also the nomenclature in misleading - acc to docs: M is 'precision' and D is 'scale', whereas the website takes 'scale' for 'precision'.

还有误导性的命名法 - 根据文档：M 是“精度”，D 是“比例”，而网站将“比例”用于“精度”。

Thought it would be useful in case sb like me was trying to get a picture. Correct me if I'm wrong, hope I haven't read some outdated docs:)

认为如果像我这样的人试图拍一张照片会很有用。如果我错了，请纠正我，希望我没有读过一些过时的文档:)

回答by Daksh

Float and Double are Floating point data types, which means that the numbers they store can be precise up to a certain number of digits only. For example for a table with a column of float type if you store 7.6543219it will be stored as 7.65432. Similarly the Doubledata type approximates values but it has more precision than Float.

Float 和 Double 是浮点数据类型，这意味着它们存储的数字只能精确到一定数量的数字。例如，对于具有浮点类型列的表，如果您7.6543219将其存储为7.65432. 类似地，Double数据类型近似于值，但它比 具有更高的精度Float。

When creating a table with a column of Decimaldata type, you specify the total number of digits and number of digits after decimal to store, and if the number you store is within the range you specified it will be stored exactly.

创建Decimal数据类型列的表时，您指定要存储的总位数和小数点后位数，如果您存储的数字在您指定的范围内，它将被精确存储。

When you want to store exact values, Decimalis the way to go, it is what is known as a fixed data type.

当你想要存储精确的值时，Decimal是一种方法，它就是所谓的固定数据类型。