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.
Decimal vs Double vs Float in MySQL
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.