存储统计数据时，我需要十进制、浮点数还是双精度？

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

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

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

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

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

我可以依赖浮点数吗？

剪辑

我知道这是一个一般性的问题，我深表歉意，但对于像我这样的非数学家，我也不是 MYSQL 专家，我希望在这个领域的专家的意见。

我已经做了调查，但我仍然觉得自己对这件事情的判断有些模糊。我再次道歉，如果我的问题是偏离主题或不适合这个网站。

95599 次浏览
小开
最佳答案

This link does 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.

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.

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.

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

小开

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.

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

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

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:)

小开

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.

小开

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.

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.

小开

Decimal : Fixed-Point Types (Exact Value). 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. salary will be in the range of -999999.99 to 999999.99

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

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

Note that they are approximate value, in this case:

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

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.6543219 it will be stored as 7.65432. Similarly the Double data type approximates values but it has more precision than Float.

When creating a table with a column of Decimal data 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.

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

小开

Simply use FLOAT. And do not tack on '(m,n)'. Do display numbers to a suitable precision with formatting options. Do not expect to get correct answers with "="; for example, float_col = 0.12 will always return FALSE.

For display purposes, use formatting to round the results as needed.

Percentages, averages, etc are all rounded (at least in some cases). That any choice you make will sometimes have issues.

Use DECIMAL(m,n) for currency; use ...INT for whole numbers; use DOUBLE for scientific stuff that needs more than 7 digits of precision; use FLOAT` for everything else.

Transcendentals (such as the LOG10 that you mentioned) will do their work in DOUBLE; they will essentially never be exact. It is OK to feed it a FLOAT arg and store the result in FLOAT.

This Answer applies not just to MySQL, but to essentially any database or programming language. (The details may vary.)

PS: (m,n) has been removed from FLOAT and DOUBLE. It only added extra rounding and other things that were essentially no benefit.


提交答案