You will see the same rounding problems if you alter a FLOAT to either a
DOUBLE or a DECIMAL. Unless all of your original data had integer values,
it's too late unless you force rounding yourself.
For example, if you alter the FLOAT field to DECIMAL(23,3), add .005 to each
number, and then alter the field to DECIMAL(22,2) you will wind up with a
value that is exactly what you want. (This applies only to positive numbers,
for negative numbers you need to decide if you want to round up towards 0 or
down away from 0.)
Using DOUBLE will not solve the problem, it will merely make it less likely
to cause trouble.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -----Original Message-----
> From: Ahmad Al-Twaijiry [mailto:ahmadt@stripped]
> Sent: Friday, November 17, 2006 5:34 PM
> To: mos; Dan Nelson
> Cc: mysql@stripped
> Subject: Re: float numbers
>
> Thanks Dan & Mos
>
> I have many tables that use float (in production database), if I
> convert all of the float column to DOUBLE or DECIMAL (using alter ),
> is there any impact or anything I should know that could happen to me
> ?
>
> is there any page that describe the differences between FLOAT, DECIMAL
> and DOUBLE in MySQL 5.0.* ?
>
> Thanks
>
> On 11/18/06, mos <mos99@stripped> wrote:
> > At 03:29 PM 11/17/2006, Ahmad Al-Twaijiry wrote:
> > >Ho everyone
> > >
> > >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 ?
> >
> > Ho Ho Ho,
> >
> > Float only uses 4 bytes and does not have much precision so
> you'll get
> > rounding. Try DOUBLE instead.
> >
> > Mike
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
> >
> >
>
>
> --
> echo "Hello World :)"
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>
