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?

Solution 1:

They both represent floating point numbers. A FLOAT is for single-precision, while a DOUBLE is for double-precision numbers.

MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

There is a big difference from floating point numbers and decimal (numeric) numbers, which you can use with the DECIMAL data type. This is used to store exact numeric data values, unlike floating point numbers, where it is important to preserve exact precision, for example with monetary data.

Solution 2:

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 |
+--------------------+---------+

Solution 3:

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

Solution 4:

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
    );