View Full Version : Float rounding in mySQL

02-16-2007, 02:33 PM
Please forgive my admittedly basic question, but...

I'm trying to work out how mySQL data types handle rounding numbers...

Let's say I put the number 104.853632 into a row that uses FLOAT(12,6)... I need it to keep 6 significant figures to the right of the decimal. Now for some reason, after I put this in it gives me 104.853630 ... in other words it (apparently) rounded to one higher digit than it's actually displaying! I didn't like that so I tried changing it to FLOAT(12,7)... this time it gives me 104.8536301! I can understand the 7 decimal places (as that's what I specified), but why wouldn't it be 104.8536320 ?? Can someone explain what's going on here??

I don't want it to round off at all! Just store my numbers to 6 significant figures (past the decimal... the number before the decimal can vary from 0 to the hundreds).

02-16-2007, 03:14 PM
would using a fixed-precision type (decimal/numeric/whatever mysql calls it?) not solve your problems?

02-16-2007, 03:32 PM
Well, it might, but I was reading about data types and they were saying that fixed-precision (digital I think) is actually stored as a string... and that this can decrease performance. Also I need numeric functionality with the data...

02-16-2007, 05:34 PM
A single-precision floating-point number is accurate to approximately 7 decimal placesThis also means that it suffers from rounding errors at approximately 7 decimal places, which is what your results show.

You should either use a DOUBLE "A double-precision floating-point number is accurate to approximately 15 decimal places." or use a DECIMAL.

There are two problems with FLOATS/DOUBLES.

1) Math performed on them is done in binary, the number of bits is limited by the hardware and you have precision and rounding problems if you need significant digits close to the limit of what is available.

2) When they are converted from their internal binary format for display, they also suffer from precision and rounding problems.

The DECIMAL type uses BCD math and you can specify a greater number of decimal digits to keep rounding out of the significant digits you want. There are also no conversion errors when it is output and displayed because it is already stored in a directly displayable format.

02-17-2007, 03:21 AM
Ok, I see. But I guess I'm interested in the fastest queries and being able to use the stored info as a number...

How does DECIMAL stack up (compared to DOUBLE) in this case?

02-17-2007, 04:05 AM
they are all fast and you probably won't notice any speed difference at all. don't store numbers in varchar/char/text fields.

if you are doing arithmatic or rounding do not use float/double you will run into errors as described above.

02-17-2007, 07:43 AM
Great! I think decimal is what I want... Thanks a bunch!