# MySQL Float vs. Decimal Money Datatype

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!

## 10 thoughts on “MySQL Float vs. Decimal Money Datatype”

Great post, bookmarked, will be back later.

You have tested it and writing form your personal experience or you find some information online?

Your post gives me valuable informations of difference between float and decimal data type.

Thank you!

stupid entry

mysql> insert into floater_decimal values (9999999999, 9999999999);

Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from floater_decimal;

+————–+————-+

| f | d |

+————–+————-+

| 5.33 | 5.33 |

| 100000000.00 | 99999999.99 |

| 100000000.00 | 99999999.99 |

+————–+————-+

9999999999 = 99999999.99 ???

stupido

Sorry information is correct. I fool

size field 10

Excelente aporte…

Adicionalmente, el tipo de dato double, se comporta bien y no cambia la precision de los decimales.

——

Excellent input …

Additionally, the double data type, behaves well and does not change the precision of the decimal.

I always use int to store my financial figures. Just multiply by 100 then store as int.

Then when you want to display the data, divide by 100. This prevents any errors in storing this sometimes critical information.

just my 2c worth (2*100/100 = 2)

Gteat expalnation.

Excelente aporte, muchas gracias por tomarse su tiempo para explicarlo => excelent job thanks for taking your time to explain it…

good what is maximum value we store in float means up to what digit its show precision like 99999999.99 ???