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

Christopher Lemire christopher.lemire at gmail.com
Sun Nov 25 22:16:16 CST 2012


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


More information about the SATLUG mailing list