Decimal vs Double vs Float in MySQL

Last Updated : 24 Apr, 2024

In MySQL, Decimal, Double, and Float are numeric data types used to represent different precision levels for numerical values. Decimal offers precise numeric storage with fixed-point arithmetic, suitable for financial data.

Double and Float are approximate numeric types, where Double provides higher precision than Float. Float is typically used for scientific calculations or where precision isn’t critical, while Double strikes a balance between precision and storage efficiency.

Decimal In MySQL

Decimal datatype is used to store the fixed precision datatypes which have the exact values.
Decimal datatypes are used where fixed precision is required such as storing quantities, percentages, and prices.

Example

Let’s create a table student with Roll_number, name, and Marks.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(255),
    percentage DECIMAL(5, 2) 
);

Here we declared the decimal where (5,2) represents the (number of total digits, digits after decimals). So here we assume the percentage is out of 100 so the total digits will be 5 and 2 digits are allowed after decimal.

Let’s insert the values into the table:

INSERT INTO students (student_id, name, percentage) VALUES
(1, 'Kartik', 85.50),
(2, 'Roshan', 72.25),
(3, 'Athul', 91.75);

To display the students table we will use the following MySQL query

select * from students

Table Students:

+------------+--------+------------+
| student_id | name   | percentage |
+------------+--------+------------+
|          1 | Kartik |      85.50 |
|          2 | Roshan |      72.25 |
|          3 | Athul  |      91.75 |
+------------+--------+------------+

Here we can see that the percentages are in decimal data type where the 2 digits are present after the decimal.

FLOAT in MySQL

Float is also a floating data type that is used to store the approximate values.
Float uses the 4 bytes i.e. 32 bits to store the values. So it has a wide range of values. But it is not always precise.
Float provides the precision up to the 8 decimal points.

Example

Let’s create a table with the float data type

CREATE TABLE students1 (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    value FLOAT
);

Here value has the data type float used.

Let’s insert the values in the table

INSERT INTO students1 (id, name, value) VALUES
(1, 'Kartik', 50.3),
(2, 'Roshan', 60.45),
(3, 'Athul', 70.21);

To display the students1 table we will use the following MySQL query

select * from students1

Table students1:

+----+--------+-------+
| id | name   | value |
+----+--------+-------+
|  1 | Kartik |  50.3 |
|  2 | Roshan | 60.45 |
|  3 | Athul  | 70.21 |
+----+--------+-------+

Here we can see that the value is stored in the Float format. But here a question will arise what is the difference between decimal data type and float data type?
To understand it, we will create a new column that will have the arithmetic operation of value divided by 3.

The query for the given condition is

ALTER TABLE students1 ADD COLUMN value_divided_by_3_d Double;
UPDATE students1 SET value_divided_by_3_d = 100 / 3;

The students1 table will look like:

| id |   name  |  value  | value_divided_by_3_d | 
|----|---------|---------|----------------------| 
| 1  | Kartik  |  50.3   | 33.333333333333336   |
| 2  | Roshan  | 60.45   | 33.333333333333336   | 
| 3  |  Athul  | 70.21   | 33.333333333333336   |

Here if we notice the divided_by_3 column, the values are rounded off to the nearest value. This is the difference between the decimal and Float data types. In Float data types values have a wide range as shown in the above table which gives more precision than the Decimal.

Double in MySQL

Double is used for the floating-point data type which stores the approximate numeric values.
Double uses the 8 bytes i.e. 64 bits to store the values. So it has a wide range of values. Double is more precise than the Float.
Double provides the precision up to the 15 decimal points.

Example

Let’s create a table with the double data type

CREATE TABLE students2 (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    value DOUBLE
);

Here value has the data type double used.

Let’s insert the values in the table

INSERT INTO students2 (id, name, value) VALUES
(1, 'Kartik', 60.3),
(2, 'Roshan', 50.45),
(3, 'Athul', 70.21);

To display the students2 table we will use the following MySQL query

select * from students2

Table students2:

+----+--------+-------+
| id | name   | value |
+----+--------+-------+
|  1 | Kartik |  60.3 |
|  2 | Roshan | 50.45 |
|  3 | Athul  | 70.21 |
+----+--------+-------+

Here we can see that the value is stored in the Double format. But here a question will arise what is the difference between decimal data type and double?
To understand it, we will create a new column that will have the arithmetic operation of value divided by 3.

The query for the given condition is

SELECT id, name, value, value / 3 AS divided_by_3
FROM students2;

The students2 table will look like

+----+--------+-------+--------------------+
| id | name   | value | divided_by_3       |
+----+--------+-------+--------------------+
|  1 | Kartik |  60.3 | 20.099999999999998 |
|  2 | Roshan | 50.45 | 16.816666666666666 |
|  3 | Athul  | 70.21 | 23.403333333333332 |
+----+--------+-------+--------------------+

Here if we notice the divided_by_3 column, the values are rounded off to the nearest value. This is the difference between the Float and Double data types. In Double data types values have a wide range as shown in the above table as values are more precise than that of float data type.