Problem understanding float data type

[ryans1000]

Here is my problem.

If I declare a column as 'float(10,2)' and do an insert such as:

'insert into employees (aFloat) values (12345678.12);'

the values shows up as 12345678.00 in the database. I do not understand why. If i declase the column as float(48,2)' I still get the same thing when I insert 12345678.12.

I have read the data types section in the manual but it provides no insight to me on why mysql does this.

 

[sleipnir214]

Rounding errors.

A float stores a IEEE binary approximation of the number. A lot of decimal numbers (for example 0.1, 1.1, or 100.1) cannot be exactly represented in IEEE form.

If you want exact representation of decimal numbers, either use an int with an implicit decimal (for example, storing 10.00 as 1000), or use the decimal column type. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[ryans1000]

It displays correctly when I put in .1, 1.1 and 100.1.

I thought when you declared FLOAT[(M,D)] that the 'D' was how many decimals of accuracy you wanted. I can see mysql storing 12345678.12 as something like 12345678.12000000xxxx internally but to round it to 12345678.00 is way off. I can't see much of a purpose of float if it works like that.

 

[sleipnir214]

This from the MySQL online manual (

http://www.mysql.com/doc/en/No_matching_rows.html):

If you are comparing FLOAT or DOUBLE columns with numbers that have decimals, you can't use '='. This problem is common in most computer languages because floating-point values are not exact values. In most cases, changing the FLOAT to a DOUBLE will fix this.

The purpose of floating-point numbers is efficient storage of decimal data. The loss of accuracy is a general known side-effect of the data type. Float can be very useful for storage of large numbers. It also existed before more database servers provided the decimal type. Using floating-point numbers in calculations is known to cause errors to creep in.

A good demonstration is to create a table that consists of a single float column. Fill the table with the values .1, .2, ..., .9, 1.0. Then perform "select * from tablename where columname = 0.3". You won't get any rows returned because no table's value exactly matches 0.3.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!