[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