List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:November 17 2006 10:03pm
Subject:Re: float numbers
View as plain text  
In the last episode (Nov 18), Ahmad Al-Twaijiry said:
> I have a column in a table defined as float
> 
> mynumber float(20,2)
> 
> if we say mynumber column in a row is 1000000 ,  when I run this SQL :
> 
> UPDATE Table SET mynumber=mynumber-100.15
> 
> the mynumber column will be 999900 not 999899.85
> 
> what is the problem ?

It works for me (approximately) in mysql 5.1:

mysql> create table blah ( mynumber float(20,2) );
mysql> insert into blah values ( 1000000 );
mysql> update blah SET mynumber=mynumber-100.15;
mysql> select * from blah;
+-----------+
| mynumber  |
+-----------+
| 999899.88 |
+-----------+
1 row in set (0.02 sec)

Since base-10 fractions can't be represented exactly by a base-2
floating-point number, any fractional value you store will be
imprecisely stored and will cuase rounding errors.  You probably want
to use the DECIMAL type instead of FLOAT.

http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

-- 
	Dan Nelson
	dnelson@stripped
Thread
float numbersAhmad Al-Twaijiry17 Nov
  • Re: float numbersDan Nelson17 Nov
    • Re: float numbersChris W18 Nov
  • Re: float numbersmos17 Nov
    • Re: float numbersAhmad Al-Twaijiry17 Nov
      • Re: float numbersDan Nelson17 Nov
      • Re: float numbersAsif Lodhi19 Nov
        • Re: float numbersAhmad Al-Twaijiry20 Nov
      • RE: float numbersJerry Schwartz20 Nov
  • ANN: SQL Maestro 6.12 releasedSQL Maestro Group14 Dec