Problem With Behaviour Of Float Fields

[larsen]

Hello,

I have this table:

Code:

CREATE TABLE `tablename1` (
`float_` float default NULL,
`tinyint_` tinyint default NULL
)

Now, I enter the value '0.12345678' into the float_ column and '123' into the tinyint_ column. The float value is shown as entered, but when I press 'refresh' it is shown as '0.123457'.

Then I want to change the tinyint value from '123' to '22', but when I press cursor_down the old value is restored. This happens because SQLyog wants to update the table with

Code:

update `test`.`tablename1` set `float_`='0.123457', `tinyint_`='22' where `float_`='0.123457' and `tinyint_`='2'

but the float value is not '0.123457' as the following select doesn´t return the row:

Code:

select * from tablename1
where float_='0.123457'

What´s the reason for all this? I suppose it´s not a bug.

From http://dev.mysql.com/doc/refman/5.1/en/num…e-overview.html : “A single-precision floating-point number is accurate to approximately 7 decimal places.”

So, this explains why the value is rounded off, but which value is actually stored in the DB?

Lars

[TomBez]

have a look at: http://dev.mysql.com/doc/refman/5.1/en/pro…with-float.html

the problem is, that stored float values differ somehow, so try something like:

Quote:

select * from tablename1 where float_>'0.12345' and float_<'0.12346';

(this only helps you view the data, but not the handling with sqlyog)

[larsen]

ok, I see.

thx =)

[peterlaursen]

The solution is to have a Primary Key in the table. Then SQLyog will ONLY use this this PK in the where-clause.

[Author]

Posts