MySQL 5 - Using float / decimal results in 9.99

Databases
1

Hi,

I am just moving from MySQL 3.x to 5.0.45.

I have various database tables with column types of float(3,2) to store prices.

However, no matter what I do, the values are always inputted as 9.99 for both float(3,2) and decimal(3,2).

CREATE TABLE test (
id int(11) default NULL,
test_float float(3,2) default NULL,
test_decimal decimal(3,2) default NULL
);

INSERT INTO test (id,test_float,test_decimal) VALUES (‘1’,‘40.00’,‘40.00’);

It seems simple enough, but for the life of me I can’t work it out?

Regards,

Christiaan

2

Of course, having just posted, I’ve figured it out…

MySQL 3.x float(3,2) = xxx.xx (3 digits, with 2 decimal places)

MySQL 5.x float(3,2) = x.xx (3 digits TOTAL, with 2 decimal places).

So to replicate a float(3,2) I now require float(5,2).

Christiaan

3

You should be using DECIMAL and not FLOAT. FLOAT is used for extremely large or small numbers and is subject to rounding errors which are nearly inconsequential in very large or very small numbers.