How to Use DECIMAL Data Type in MySQL?

In MySQL, the DECIMAL data type assists the user to store precise decimal numbers with specific precision and scale in the database. It is useful in many scenarios where the user needs to store the exact numeric value such as percentage without any change.

This blog aims to discuss the usage of decimal data types in MySQL.

How to Use DECIMAL Data Type in MySQL?

The DECIMAL data type can store decimal numbers with precision and scale in a MySQL database. Precision refers to the total digits that can be stored in a column, while scale refers to the number/digits to the right of the fractional point.

Utilize the given syntax to create a column with the DECIMAL data type:

DECIMAL(precision, scale)

Example 1: Creating Column With Decimal Data Type

Run the given query to create a table with DECIMAL type:

CREATE TABLE linuxhint_products (

p_id INT PRIMARY KEY,

p_name VARCHAR(50),

p_price DECIMAL(10, 2)

);

The above-given code will create a table named “linuxhint_products” having three columns: p_id, p_name, and p_price. The “p_price” column has the datatype DECIMAL with “10” precision and “2” digits after the decimal point.

Output

The output demonstrates that the table is created.

It’s time to add some data into the newly created table with DECIMAL values by running the given “INSERT” query:

INSERT INTO linuxhint_products (p_id, p_name, p_price)

VALUES

(1, 'Product 1', 10.99),

(2, 'Product 2', 15.50),

(3, 'Product 3', 7.25);

Here in the query, three new rows of data will be added to the table.

Output

Execute this command to list the stored data from the table “linuxhint_products”:

SELECT * FROM linuxhint_products;

Output

It can be observed that the “p_price” column contains the exact numeric values that were stored.

Example 2: Altering Data Type of Existing Table Column

Let’s create another table but with any other data type, let’s suppose “INT” by executing the below-given query:

CREATE TABLE linuxhint_products (

p_id INT PRIMARY KEY,

p_name VARCHAR(50),

p_price INT

);

Output

The output displays that the table is created successfully.

Now let’s insert the same data as we inserted in the above table by running this command:

INSERT INTO linuxhint_products (p_id, p_name, p_price)

VALUES

(1, 'Product 1', 10.99),

(2, 'Product 2', 15.50),

(3, 'Product 3', 7.25);

List the data of the table using the “SELECT” query:

SELECT * FROM linuxhint_products;

Output

The output displayed the data of the table with the roundoff values stored in the column “p_price” instead of displaying the exact stored values.

To convert any existing data type into the DECIMAL data type, run the provided query:

ALTER TABLE linuxhint_products MODIFY p_price DECIMAL(10, 2);

The above command will modify the data type of the “p_price” column of the table “linuxhint_products” into DECIMAL with “10” precision and “2” scale.

Output

The output displayed the message that “3” rows are affected by this command which means the data type is modified for the “p_price” column.

Conclusion

In MySQL, users can utilize the DECIMAL data type to store precise decimal numbers with specific precision and scale in the database. The user can define the data type of a column while creating the table or in the “ALTER” command to modify the existing data type. Moreover, “DECIMAL(precision, scale)” is the syntax for the DECIMAL data type. This post discussed the usage of DECIMAL data type in MySQL.

How to Use DECIMAL Data Type in MySQL?