MySQL FLOAT & decimals

Datatype of field in the DB is FLOAT and the value is 18.开发者_开发问答7. I'd like to store and display this on page as 18.70. Whenever I enter the extra 0 it still only stores it as 18.7

How can I store the extra 0 ? I can change the data type of the field.

In a FLOAT column, what MySQL stores for 18.7, is actually:

01000001 10010101 10011001 10011010

which, being retrieved from the DB and converted back into your display format, is 18.7.

In reality, the stored value is a binary fraction represented by the decimal number 18.70000076293945 which you can see by issuing this query:

CREATE TABLE t_f (value FLOAT);

INSERT
INTO    t_f
VALUES  (18.7);

SELECT  CAST(value AS DECIMAL(30, 16))
FROM    t_f;

IEEE-754 representation of number stores them as binary fractions, so a value like 0.1 can only be represented with continued fraction and hence be not exact.

DECIMAL, on the other hand, stores decimal digits, packing 9 digits into 4 bytes.

Floating point types do not store the number of insignificant zeros on the left side of a number before decimal digit or on the right side of the number after the decimal digit. You'll need to use a string-based type (or store the precision in a separate field) if you want to store the exact numeric string entered by the user and be able to distinguish 12.7 from 12.70. You can, however, round things that you display by two digits in your application.

if two decimal points needed use:
decimal(n,2); where n>=2

the decimal data type will persist the decimal points formatting and gives more accurate results than float and double data types.

Are you attempting to store a currency as a float? If so, please use a decimal with more decimal digits than 2.

You really want fixed-point arithmetic on currencies.

This is just very broad rule of thumb and my own observation, but in regular business logic as serialized in a database, you almost never want floating point. I know there are lots of exceptions, but I'm suspicious whenever I see a float typed column in a table because of this. I'd be interested in what others have found.