Float or Numeric

Many programming languages supply a data type called float in which to store numbers that contain decimals. Often this is the only numeric data type available to use to store non-integers (at least once you take into account the size variants for the two types such as longint and double).

The problem with float (and double etc) for storing decimal places is that the field is in fact using a binary storage method and so unless the value you wist to store is able to be expressed exactly in bimary (eg 0.125 - 1/8 - or 0.0009765625 - 1/1024) then you get rounding errors both in the conversion of the decimal number into binary when you store it and also in the conversion back to decimal when you display it.

You can se the same data type in your mySQL databases but there it will suffer from exactly the same problems - made worse by the fact that you are more likely to be referencing the field in comparisons rather than in calculations. In calculations the slight difference in the values will still give an answer that is almost correct and likely will be correct in many cases once rounded to the number of decimal places you expect. With comparisons, those looking for specific values will not find any results due to the slight rounding.

Unlike with most programming languages, databases support another data type that is far more suited to use for storing numbers that contain decimal places. This data type is numeric (decimal is another way to refer to this same data type). This data type allows you to specify an exact decimal precision for the values you are storing so for example numeric(9,2) will always store exactly two decimal places and will always match exactly when compared to the same value where the same two decimal places are specified. This data type is effectively the same as integer except that it has an assumed decimal point in the value that is a specific number of digits from the right of the number the field contains with the conversions to and from this format that many programming languages don't have being handled automatically. (An example of a programming language that does support this type of data is CoBOL eg. pic '9(7)v99'.)

When you store a value that you have in a float in your program into a numeric field in the database the number will be rounded back to the nearest decimal number with the specified precision. If you haven't performed any calculations on the number since it was first entered then it sould round back to the appropriate value based on the original decimal number. If you have performed calculations on the number before storing it in the database then unless the calculations have resulted in significant rounding errors when compared to the correct calculation on the original numbers then it to should round to the correct value.

There are still uses for the float data type (otherwise there'd be no reason for it to exist) but the types of application that require the use of that data type are those that are dealing with very large (trillions or perhaps trillions of trillions) or very small numbers ( trillionsths of thrillionths) where you are only working with approximate values to start with. Where you are working with regular numbers that require perhaps two or three decimal places the numeric data type is the appropriate one to use in your database.


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow