144

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?

janpio
  • 10,645
  • 16
  • 64
  • 107
  • 3
    Note that ["Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision"](http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html) – David Sykes Apr 19 '11 at 12:08
  • 1
    I guess you meant this link: http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html – arun Feb 20 '14 at 03:58
  • This might be more helpful: http://stackoverflow.com/questions/5150274/difference-between-float-and-decimal-data-type – Ifti Mahmud Mar 17 '14 at 00:03
  • Also relevant: Point #5 at http://dev.mysql.com/doc/refman/5.0/en/no-matching-rows.html – rinogo Feb 06 '15 at 19:55
  • https://www.w3resource.com/mysql/mysql-data-types.php – Channa Mar 19 '21 at 16:12

6 Answers6

124

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.

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
  • 45
    can you elaborate with example – Kailas Apr 17 '14 at 09:11
  • 6
    @Kailas Floats get rounded and decimals do not. Decimal(9,3) could be for example 123456.789 whereas if you tried to store 123456.789 it would get inserted as 123456.0 as a float. – Blake Mar 06 '18 at 20:18
104

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 |
+--------------------+---------+
Kai Neuwerth
  • 158
  • 1
  • 12
Andi S.
  • 1,141
  • 1
  • 7
  • 3
  • 5
    Maybe a better comparison would have been to give fla and flb 2 decimal points as well. `float(10, 2)` – Grateful Nov 28 '17 at 09:26
  • 6
    Do _not_ use `(10,2)` on `FLOAT` or `DOUBLE`; that is useful only for `DECIMAL`. On Float or Double it leads to "double-rounding", which can manifest itself in strange ways. – Rick James Sep 29 '21 at 16:52
  • Also, according to the MySQL documentation (https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html) , "As of MySQL 8.0.17, the nonstandard FLOAT(M,D) and DOUBLE(M,D) syntax is deprecated and you should expect support for it to be removed in a future version of MySQL." – William Gérald Blondel Mar 15 '23 at 09:47
37

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

Williham Totland
  • 28,471
  • 6
  • 52
  • 68
  • 1
    Note that floats and doubles round after some point. For example 100000.1 truncates to 100000 as a float. – Blake Mar 06 '18 at 20:30
27

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
    );
Omar
  • 39,496
  • 45
  • 145
  • 213
14

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.

Ravi Patel
  • 641
  • 1
  • 10
  • 18
12

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.

user3902486
  • 131
  • 1
  • 4