How to use float type properly in mysql?

Question

I have a table in mysql with a field type float(10, 7). I am trying to insert value 1196.104, Even when using phpmyadmin it converts that value to 1000.0000000. What type should I use to properly store the value 1196.104 in database.

Answer 1

This is working exactly as intended by the FLOAT(10,7). When you give a value that won't fit in the number of digits, it caps the value and uses the greatest value that can fit.

In this case, the 10-digit value of 999.9999999, rounded up to 1000.0000000.

https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html says:

MySQL permits a nonstandard 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.

In other words, you gave it the constraint to max out at 1000.0000000, because you told it to max out at 10 digits, of which 7 digits are to the right of the decimal point.

mysql> create table f ( f float(10,7));

mysql> insert into f values (1196.104);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'f' at row 1 |
+---------+------+--------------------------------------------+

mysql> select * from f;
+--------------+
| f            |
+--------------+
| 1000.0000000 |
+--------------+

If you want to store larger values, declare FLOAT with enough digits to hold the values you use. For example, the value 1196.104 could be inserted to FLOAT(11,7) or FLOAT(10,6).

Or you could use FLOAT with no arguments, and it will not constrain the number of digits at all.

There are some caveats about FLOAT. It's an inexact numeric type, and it will have rounding errors. This is unavoidable because of the way FLOAT is implemented, and it affects every programming language.

Read https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html and https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html

Also see my old tweet about this: https://twitter.com/billkarwin/status/347561901460447232