It is surprising how often I see confusion around the MySQL float and decimal data types – especially when it comes to deciding which one to use when storing financial figures. Let’s compare the float and decimal definitions, and then compare the two in real use.

From the MySQL documentation:

Float:
A small (single-precision) floating-point number. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.

Decimal:
A packed “exact” fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the “-” sign are not counted in M. If D is 0, values have no decimal point or fractional part.
The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.

What I understand from this is that floats should generally be used for large numbers where exact precision isn’t necessary, while decimals on the other hand do store the number to an exact precision.  This already a positive for the decimal data type, but let’s continue to an example:

mysql> create table floater_decimal(f float(10,2), d decimal(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> desc floater_decimal;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| f     | float(10,2)   | YES  |     | NULL    |       |
| d     | decimal(10,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

We have created table floater_decimal, with two fields – f float(10,2) and d decimal(10,2).

mysql> insert into floater_decimal values (5.33, 5.33);
Query OK, 1 row affected (0.02 sec)
mysql> select * from floater_decimal;
+------+------+
| f    | d    |
+------+------+
| 5.33 | 5.33 |
+------+------+
1 row in set (0.04 sec)

If we insert the same value (5.33) into both fields and do a normal select on both fields, it appears that we are pulling the same value back out of both fields.

mysql> select (1.0000000000*f) from floater_decimal;
+------------------+
| (1.0000000000*f) |
+------------------+
|     5.3299999237 |
+------------------+
1 row in set (0.00 sec)

However, if we select (1.0000000000*f) to force the query to show the more exact number actually being stored, it can be seen that 5.33 isn’t actually being stored to an exact precision.

mysql> select (1.0000000000*d) from floater_decimal;
+------------------+
| (1.0000000000*d) |
+------------------+
|   5.330000000000 |
+------------------+
1 row in set (0.00 sec)

If we apply the same logic to the decimal field, it can be seen that 5.33 really is being stored to an exact precision.

What does this mean?  It means that floats can be a very dangerous data type to use for storing financial figures.  It may seem okay to use from a first glance, but really the fact that the float isn’t storing an exact number can lead to mathematical issues when pulling the number back out.  Sure, the float is less than .000001 off from the intended number and should be rounded correctly when processed in the code – but imagine how far this .000001 difference can carry when adding/subtracting 1 million rows that are all slightly off.

If you intend to store financial figures to an exact precision, while avoiding errors that may seem impossible to find – use decimal instead of float!