[SATLUG] Difference between FLOAT and DECIMAL MySQL Data Types by Example

Bruce Dubbs bruce.dubbs at gmail.com
Sun Nov 25 22:27:35 CST 2012


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.

You are seeing rounding.  The decimal is basically a string, e.g. 
"100.00" and the float is in ieee floating point format.  Manipulating 
the decimal format does not do the rounding and is useful in accounting 
of currency.

   -- Bruce



More information about the SATLUG mailing list