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.

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.