79

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.

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.

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.

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.

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.

Andy
  • 43,170
  • 54
  • 150
  • 214
PEPLOVE
  • 937
  • 1
  • 6
  • 8

6 Answers6

97

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.

Marten
  • 1,241
  • 9
  • 15
Linger
  • 14,477
  • 22
  • 48
  • 76
  • 11
    That link has a lot of incorrect information. I would not follow its advice. – Simon Byrne Dec 09 '14 at 21:58
  • 1
    Also, if used for indexing or as a key, a float is undesirable because the stored value might differ from the one inserted – Raul Santelices Jan 05 '16 at 18:35
  • @Linger so if I use somthing like, sum, multiplication functions in select then double is better? – deFreitas Dec 10 '16 at 17:37
  • 15
    I know this is an old question, but for the benefit of future readers, **this answer is completely wrong and should NOT have been accepted.** `float`, `double`, and `decimal` are all *inexact floating-point* types. As explained in [this much better answer](https://stackoverflow.com/a/618596/2447250), the primary difference between them is the *numeric base* (2 vs. 10) of the underlying data representation. – David Zhang Aug 12 '17 at 07:37
  • @DavidZhang Are you saying 'Decimal' is a base 10 data representation? In that case, it would be more precise in some cases. For example, storing 0.1 in a base 2 vs base 10. – Richard Smith Jun 21 '18 at 18:42
  • 2
    @DavidZhang The answer you linked to is for .NET decimal, float, etc. This question is asking about MYSQL – Svet Angelov Oct 01 '18 at 20:58
  • 1
    @SvetAngelov Yes, MySQL `DECIMAL` differs from .NET `decimal` in that it's fixed-point rather than floating-point. I misspoke in my previous comment when I said they were all floating-point. But that doesn't change the fact that all three datatypes are **inherently inexact**, and selecting between them means choosing the *kind of inexactness* you're willing to tolerate. – David Zhang Oct 01 '18 at 23:31
13

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.

Simon Byrne
  • 7,284
  • 1
  • 21
  • 43
  • In the docs it is mentioned, "for Decimal " data type the calculations are exact.https://dev.mysql.com/doc/refman/5.7/en/precision-math-numbers.html – Daksh Oct 23 '18 at 00:05
  • 1
    While summation of fixed point values is exact, other operations (such as division or log10) can't be: there is simply no way to represent the result in fixed point. – Simon Byrne Oct 24 '18 at 19:19
  • Can you point me to a resource where I can ready more about it, wherever I've read about it, all that is written is that, for exact calculations use Decimal types. – Daksh Oct 25 '18 at 05:08
  • 1
    Any decent coverage of numerical analysis (my favourite is Nick Higham's "Accuracy and Stability of Numerical Algorithms") will cover fixed and floating point representations. – Simon Byrne Oct 26 '18 at 04:16
  • Will take a look into it ! – Daksh Oct 26 '18 at 09:38
9

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)
Timeless
  • 6,444
  • 8
  • 54
  • 90
6

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.

waterinusa
  • 61
  • 1
  • 4
1

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

Joey
  • 357
  • 2
  • 10
0

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.

Daksh
  • 866
  • 9
  • 22