[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