Which is best for monetary data: double, float, decimal

For floating-point data types, MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

The FLOAT and DOUBLE data types are used to represent approximate numeric data values. For FLOAT the SQL standard allows an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. MySQL also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in a four-byte single-precision FLOAT column. A precision from 24 to 53 results in an eight-byte double-precision DOUBLE column.

MySQL allows a non-standard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

MySQL treats DOUBLE as a synonym for DOUBLE PRECISION (a non-standard extension). MySQL also treats REAL as a synonym for DOUBLE PRECISION (a non-standard variation), unless the REAL_AS_FLOAT SQL mode is enabled.

For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of digits.

The DECIMAL and NUMERIC data types are used to store exact numeric data values. In MySQL, NUMERIC is implemented as DECIMAL. These types are used to store values for which it is important to preserve exact precision, for example with monetary data.

As of MySQL 5.0.3, DECIMAL and NUMERIC values are stored in binary format. Previously, they were stored as strings, with one character used for each digit of the value, the decimal point (if the scale is greater than 0), and the “-” sign (for negative numbers). See Chapter 23, Precision Math.

When declaring a DECIMAL or NUMERIC column, the precision and scale can be (and usually is) specified; for example:

salary DECIMAL(5,2)

In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point. If the scale is 0, DECIMAL and NUMERIC values contain no decimal point or fractional part.

Standard SQL requires that the salary column be able to store any value with five digits and two decimals. In this case, therefore, the range of values that can be stored in the salary column is from -999.99 to 999.99. MySQL enforces this limit as of MySQL 5.0.3. Before 5.0.3, on the positive end of the range, the column could actually store numbers up to 9999.99. (For positive numbers, MySQL 5.0.2 and earlier used the byte reserved for the sign to extend the upper end of the range.)

In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0), where the implementation is allowed to decide the value of M. MySQL supports both of these variant forms of the DECIMAL and NUMERIC syntax. The default value of M is 10.

The maximum number of digits for DECIMAL or NUMERIC is 65 (64 from MySQL 5.0.3 to 5.0.5). Before MySQL 5.0.3, the maximum range of DECIMAL and NUMERIC values is the same as for DOUBLE, but the actual range for a given DECIMAL or NUMERIC column can be constrained by the precision or scale for a given column. When such a column is assigned a value with more digits following the decimal point than are allowed by the specified scale, the value is converted to that scale. (The precise behavior is operating system-specific, but generally the effect is truncation to the allowable number of digits.)

source: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html