[SATLUG] Difference between FLOAT and DECIMAL MySQL Data Types
by Example
Alan Lesmerises
alesmerises at satx.rr.com
Mon Nov 26 00:30:00 CST 2012
On 11/25/2012 10:16 PM, Christopher Lemire wrote:
> I'm studying for the second Linux+. The cert requires 2 exams. Trying
> to understand the differences in MySQL data types FLOAT and DECIMAL
> shown by an example, I wrote this. Near the end, you can see that the
> types do exactly the opposite of what the docs say they do, or at
> least it seems that way. Can somebody explain this?
>
> mysql> select * from numbers;
> +--------+--------+
> | a | b |
> +--------+--------+
> | 100.00 | 100.00 |
> +--------+--------+
> 1 row in set (0.00 sec)
>
> mysql> describe numbers;
> +-------+---------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+---------------+------+-----+---------+-------+
> | a | decimal(10,2) | YES | | NULL | |
> | b | float(10,2) | YES | | NULL | |
> +-------+---------------+------+-----+---------+-------+
> 2 rows in set (0.05 sec)
>
> mysql> select @a := (a/3) * 3, @b := (b/3) * 3 from numbers \G;
> *************************** 1. row ***************************
> @a := (a/3) * 3: 99.999999999
> @b := (b/3) * 3: 100.000000
> 1 row in set (0.00 sec)[/CODE]
>
> But the docs say:
>
> The FLOAT and DOUBLE types represent approximate numeric data values.
>
> And
>
> The DECIMAL and NUMERIC types store exact numeric data values. These
> types are used when it is important to preserve exact precision
>
> Yet the results show that arithmetic operations on
> DECIMAL gave an approximate value. (33 1/3) * 3 = 99.999999999.
>
> And the results show that arithmetic operations on
> FLOAT gave an exact value. (33 1/3) * 3 = 100.000000
>
> This is the exact opposite of what the docs suggest!
>
> And if you know of any other examples that can show the difference
> between the two, please share.
>
> Christopher Lemire<christopher.lemire at gmail.com>
> Ubuntu 64 bit Linux Raid Level 0
>
> Gnu Privacy Guard Key Fingerprint = 3E1A 9103 EF3D 4885 6866 E9DE
> C69F 18B3 E13B 0909
>
> Web: http://linuxinnovations.blogspot.com
> Jabber: recursivequicksort at jabber.org
The precision the documentation is talking about is the 'exactness' of
the value that's stored, not how exact the answer will be when you use
that value in a mathematical operation. As another post said, you're
seeing round-off error effects here.
I haven't really used MySQL before, but this is my take on it (for what
it's worth):
In another programming language that I use on a regular basis (that will
remain nameless), data types that exist to maintain very high precision
are stored as long integers & then transformed when retrieved (e.g.,
"Currency" values are scaled up by a factor of 100 to preserve exact
penny amounts, then divided by 100 when you use it). Based on what you
showed above, it appears that you can specify the number of digits that
you need, but it is a fixed number only that can't store anything beyond
what was defined for that field.
Floating point numbers handle everything somewhat like scientific
notation (see Wikipedia definitions for "Significand" and "Exponent").
Since the significand is represented by only a fixed number of bits,
describing the fractional portions of non-integer numbers with lots of
digits to the right of the decimal point cannot be represented
completely -- it eventually gets truncated. The question is how much
can be represented (e.g., the difference between "Single" and "Double"
precision). Based on what you showed above, it looks like you can tell
MySQL what precision to DISPLAY when you define the field. The actual
precision is much higher than 2 digits to the right of the decimal
point, that's why the answer came out better with the FLOAT value.
Al Lesmerises
More information about the SATLUG
mailing list