Saving statistics, do I need DECIMAL, FLOAT or DOUBLE?

Question

I create for pleasure, but I still want to seriously approach it, the site on which various tests are conducted. With these tests, I hope to collect statistics.

Some of the data will include the percentage of completeness of the tests as time passes. I can easily calculate the percentage of tests, but I would like the true data to be returned when I store various values ​​related to the tests at the end.

Most of the values ​​are in PHP floats, so my question is that if I need true statistics, I have to store them in MYSQL as FLOAT, DOUBLE or DECIMAL.

I would like to use MYSQL functions such as AVG() and LOG10() , as well as TRUNCATE() . For MYSQL to return true data based on my values, which I insert, which I should use as a selection of the database column.

I ask because some numbers may or may not be floating, for example, 10, 10.89, 99.09, or just 0. But I would like true and reliable statistics to be returned.

Can I rely on floating point math?

EDIT

I know that this is a general question, and I apologize widely, but for non-mathematicians like me, I am also not an expert on MYSQL, I would like to receive the opinion of an expert in this field.

I have done my research, but I still feel that I have a muddy judgment on this issue. Again, I apologize if my question does not fit the topic or is not suitable for this site.

Answer 1

This link does an excellent job of what you are looking for. Here is what it says:

All three of these types can be specified using the following parameters (size, d). Where size is the total size of the string and d is precision. For example, to store a number like 1234.567, you set the data type to DOUBLE (7, 3), where 7 is the total number of digits and 3 is the number of digits that follows the decimal point.

FLOAT and DOUBLE both represent floating point numbers. FLOAT is for single point processing, and DOUBLE is for double precision numbers. Accuracy from 0 to 23 results in a 4-byte FLOAT column with one precision. Accuracy from 24 to 53 results in an 8-byte DOUBLE double precision column. FLOAT is accurate to about 7 decimal places, and DOUBLE is accurate to 14.

The decimal declaration and operation are similar to Double. But there is one big difference between floating point values ​​and decimal (numeric) values. We use the DECIMAL data type to store exact numerical values, where we do not need precision, but precise and accurate values. The decimal type can store a maximum of 65 digits, with 30 digits after the decimal point.

So, for the most accurate and accurate value, Decimal is the best option.

Answer 2

Simply put, Float and double are not as accurate as decimals. decimal is recommended for entering the amount of incoming money (currency and salary). Another point to be noted is: DO NOT compare floating point numbers using "=", "<>" since floating point numbers are not exact.

Answer 3

If you do not store decimal data (i.e. currency), you should use the standard floating-point type (FLOAT or DOUBLE). DECIMAL is a fixed point type, so it can overflow when calculating things like SUM, and will be ridiculously inaccurate for LOG10.

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

Answer 4

Linger: The website you mention and cite has IMO some inaccurate information, which made me confused. I read in the docs that when you declare a float or double, the decimal point is NOT actually included in the number. Thus, this is not the number of characters in the string, but all the numbers used.

Compare the documents: "DOUBLE ACCURACY (M, D). Here" (M, D) "means that values ​​can be stored with a total of up to M digits, of which D digits can be after the decimal point. For example, a certain column since FLOAT ( 7.4) will look like -999.9999 when displaying " http://dev.mysql.com/doc/refman/5.1/en/floating-point-types.html

Also the nomenclature in the misleading is according to the documents: M is “accuracy” and D is “scale”, while the website takes “scale” for “accuracy”.

Thought it would be useful if sb, like me, was trying to get a picture. Correct me if I am wrong, I hope that I have not read some outdated documents :)

Answer 5

Decimal : fixed-point types (exact value). Use it when you need accurate accuracy, such as money.

Example: salary DECIMAL(8,2) , 8 - total number of digits, 2 - number of decimal places. salary will range from -999999.99 to 999999.99

---

Float, Double : floating point types (approximate value). Float uses 4 bytes to represent the value, Double uses 8 bytes to represent the value.

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

Please note that this is an approximation , in this case:

• A value like 1 / 3.0 = 0.3333333... will be stored as 0.33 (2 decimal places)
• A value of type 33.009 will be saved as 33.01 (rounding to 2 decimal places)