[mysql] MySQL: What's the difference between float and double?

Checking in the new database structure I saw that someone changed a field from float to double. Wondering why, I checked the mysql documentation, but honestly didn't understand what the difference is.

Can someone explain?

Thought I'd add my own example that helped me see the difference using the value 1.3 when adding or multiplying with another float, decimal, and double .

1.3 float ADDED to 1.3 of different types:

|float              | double | decimal |
+-------------------+------------+-----+
|2.5999999046325684 | 2.6    | 2.60000 |

1.3 float MULTIPLIED by 1.3 of different types:

| float              | double             | decimal      |
+--------------------+--------------------+--------------+
| 1.6899998760223411 | 1.6900000000000002 | 1.6900000000 |

This is using MySQL 6.7

Query:

SELECT 
    float_1 + float_2 as 'float add',
    double_1 + double_2 as 'double add',
    decimal_1 + decimal_2 as 'decimal add',

    float_1 * float_2 as 'float multiply',
    double_1 * double_2 as 'double multiply',
    decimal_1 * decimal_2 as 'decimal multiply'
FROM numerics

Create Table and Insert Data:

CREATE TABLE `numerics` (
  `float_1` float DEFAULT NULL,
  `float_2` float DEFAULT NULL,
  `double_1` double DEFAULT NULL,
  `double_2` double DEFAULT NULL,
  `decimal_1` decimal(10,5) DEFAULT NULL,
  `decimal_2` decimal(10,5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `_numerics` 
    (
        `float_1`,
        `float_2`,
        `double_1`,
        `double_2`,
        `decimal_1`,
        `decimal_2`
    )
VALUES
    (
        1.3,
        1.3,
        1.3,
        1.3,
        1.30000,
        1.30000
    );

FLOAT stores floating point numbers with accuracy up to eight places and has four bytes while DOUBLE stores floating point numbers with accuracy upto 18 places and has eight bytes.

Float has 32 bit (4 bytes) with 8 places accuracy. Double has 64 bit (8 bytes) with 16 places accuracy.

If you need better accuracy, use Double instead of Float.

Perhaps this example could explain.

CREATE TABLE `test`(`fla` FLOAT,`flb` FLOAT,`dba` DOUBLE(10,2),`dbb` DOUBLE(10,2)); 

We have a table like this:

+-------+-------------+
| Field | Type        |
+-------+-------------+
| fla   | float       |
| flb   | float       |
| dba   | double(10,2)|
| dbb   | double(10,2)|
+-------+-------------+

For first difference, we try to insert a record with '1.2' to each field:

INSERT INTO `test` values (1.2,1.2,1.2,1.2);

The table showing like this:

SELECT * FROM `test`;

+------+------+------+------+
| fla  | flb  | dba  | dbb  |
+------+------+------+------+
|  1.2 |  1.2 | 1.20 | 1.20 |
+------+------+------+------+

See the difference?

We try to next example:

SELECT fla+flb, dba+dbb FROM `test`;

Hola! We can find the difference like this:

+--------------------+---------+
| fla+flb            | dba+dbb |
+--------------------+---------+
| 2.4000000953674316 |    2.40 |
+--------------------+---------+

Doubles are just like floats, except for the fact that they are twice as large. This allows for a greater accuracy.