How to Decimal Values in MySQL

In this tutorial, we will learn how to use decimal values in a MySQL database.

It is common to use integers to keep count or list products, students, packages, employees, etc.

However, integers might not always suffice our needs. In such a case, we need to use decimal point numbers.

For example, while storing product profits in a sales table for a product-based company like Amazon, it would be common to use decimal values like 12.99 or 5.55555. Such values play a key role in the statistical analysis of the products.

MySQL helps us store such decimal values in our tables using the DECIMAL() function. This function takes in two parameters - the first is the total number of characters in the number, and the second is the total number of numbers after the decimal point (.) in the number.

For example, if we want to store 12.99, we can call the function DECIMAL(4,2). It would entail four numbers or characters in our overall number and two digits after the decimal point.

Similarly, to store the number 5.55555, we can define our DECIMAL function as DECIMAL(6,5). It would mean that the programmer has assigned five values after the decimal point.

Let us understand how this method works in greater depth.

We will begin by creating a table and inserting a row with the registration date for a student in it. Here we create a table, student_details, along with a few rows.

-- create the table student_details
CREATE TABLE student_details(
  stu_id DECIMAL(7,5) NOT NULL,
  stu_firstName varchar(255) DEFAULT NULL
);

-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName) 
 VALUES(1.2345,"Preet"),
 (2.1212,"Rich"),
 (3.54,"Veron"),
 (4.67235,"Geo"),
 (5.41132,"Hash"),
 (6.66,"Sachin"),
 (7.01,"David");

To view the entries in the data, we use the following code.

SELECT * FROM student_details;

Output:

stu_id		stu_firstName	
1.23450		Preet
2.12120		Rich
3.54000		Veron
4.67235		Geo
5.41132		Hash
6.66000		Sachin
7.01000		David

As we can see in the code above block, the column named stu_id stores the identity number of a student in decimal value. It contains a maximum of seven characters with a maximum of five characters after the decimal point (.).

Therefore, with the help of the INSERT INTO statement and the DECIMAL() function, we can efficiently add decimal point values to a column in a table in MySQL.