EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Decimal

MySQL Decimal

By Payal UdhaniPayal Udhani

MySQL Decimal

Introduction to MySQL Decimal

For storing the numeric values, we can use integer datatype in MySQL. But when we have to maintain the precision of the numeric values, we have to use the decimal data type that allows us to select the precision and scale of the decimal numbers that will include a decimal point in its value. People and organizations often use data types to store values that require accuracy and precision, such as weights and amounts.

This article will teach us about the decimal datatype, its declaration, attributes supported by it, alternative synonyms for the decimal data type, the storage space required to store the decimal quantities, and how we can use the decimal datatype efficiently and effectively in our database.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax

The syntax for the declaration of the decimal datatype in the MySQL table column is as follows –

nameOfColumn DECIMAL(Precision,Scale);

Where nameOfColumn is the column name for which you want to save the data in decimal datatype.

  • DECIMAL is the keyword to declare the decimal datatype, which is case insensitive.
  • The entire number of digits that can be stored in a decimal number is referred to as precision. This value can be between 1 to 65.
  • The scale parameter defines the number of decimal digits and the number of digits that can be stored following the decimal point. This value should be between 0 to 30 and always be less than the precision value, i.e., Scale < Precision.

The DECIMAL datatype has synonyms such as NUMERIC, FIXED, or DEC.

Attributes

The DECIMAL datatype also features the UNSIGNED and ZEROFILL attributes like the INTEGER datatype. Assigning the UNSIGNED attribute to a column with the DECIMAL datatype will limit the storage of negative decimal numbers. This ensures that the column only stores positive decimal values.

ZEROFILL attribute, when assigned to the decimal datatype column, will always display the value of the decimal value by padding the zeroes up to the width of the decimal number declared for that column.

Behavior

Let’s consider an example where the datatype for a column is as follows:

DECIMAL(5,3) will allow us to store the values ranging from 99.999 to 99.999 as the scale is 3. The decimal digits after the decimal point can be maximum up to 3 places, and the precision is 5; hence, 5-3=2 two digits will be allowed to store in the decimal value of that column. As none of the attributes is specified, the negative values that are signed values can also be stored in this field.

Consider one more example in which we don’t specify the scale value, DECIMAL(3). In this case, the default scale value is 0, which means that decimal values with decimal points cannot be stored in this column. Only whole numbers ranging from -999 to 999 can be stored.

Let us take another example where we do not specify either parameter value, i.e., precision or scale, and declare the column’s datatype as DECIMAL. In this case, the default precision value is ten, and the scale parameter is zero. This allows a range of values to be stored in the column from -9999999999 to 9999999999.

Therefore, we can conclude that specifying precision and scale parameters is optional, and the default value for the scale is 0, while the default value for the precision parameter is 10.

DECIMAL Data Type Storage in MySQL

MYSQL allocates separate storage space for a decimal value’s fractional and integer parts, distinguishing between the values before and after the decimal point. As the binary format stores the values in MYSQL for the decimal data type, it requires 4 bytes of memory to keep 9 digits. Four bytes of storage space are required for each pack of 9 digits before and after the decimal point. For the leftover digits for storage, they need storage space in the following fashion –

1 or 2 leftover digits take 1 byte of storage space, 3 or 4 leftover digits take 2 bytes of storage space, 5 or 6 leftover digits take 3 bytes of storage space, and 7 or 9 leftover digits take 4 bytes of storage space.

Consider one example, DECIMAL(20,8) datatype will require 4 bytes of storage space for 8 digits after the decimal point, and for the remaining 20-8=12, i.e., 12 digits will be split in two. Out of 12, 9 digits will require 4 bytes; for the remaining 12-9, i.e., three digits, 2 bytes will be required. Hence, 4+4+2 =10 bytes of storage space will be required to store the decimal value of this type.

Example of MySQL Decimal

Let us create one table containing the decimal data type column in it. Here, I will make a table named educba_stock containing column cost_in_rupees of decimal datatype with precision 20 and scale 2 using the following create query –

CREATE TABLE educba_stock (
identifier INT AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(100),
cost_in_rupees DECIMAL(20 , 2 ) NOT NULL
);

that results in the following output –

MySQL Decimal 1

Let us insert some records in it by executing the following command

INSERT INTO educba_stock(description,cost_in_rupees)
VALUES('Java', 4500.26),('Angular',8512.6),('Mysql',4587.45);

that gives the following output in the sqlyog editor –

MySQL Decimal 2

Let us now retrieve the data stored in the educba_stock table using the following select query –

SELECT * FROM educba_stock;

that results in the following output –

Select Query 3

Let us alter the column cost_in_rupees and set the attribute ZEROFILL to it using the following ALTER query –

ALTER TABLE educba_stock
MODIFY cost_in_rupees DECIMAL(20,2) ZEROFILL;

that will result in the following output –

MySQL Decimal 4

Please note that when you assign the ZEROFILL attribute to the DECIMAL datatype, MYSQL internally adds the UNSIGNED attribute to it automatically.

Let’s retrieve the values from the table and examine their display after assigning the ZEROFILL attribute to the decimal datatype of the “cost_in_rupees” column using the following select query:

SELECT * FROM educba_stock;

That now results in the following output –

snapshot

Add zeroes before and after the value to ensure it fits within the maximum display width of the column.

Conclusion

The decimal data type stores numeric values while maintaining their precision and accuracy. Its design allows for the storage of values with decimal points. To pad leftover digits with zeroes and restrict negative values in the column, you can utilize the ZEROFILL and UNSIGNED attributes in conjunction with the decimal data type.

Recommended Articles

We hope that this EDUCBA information on “MySQL Decimal” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL ROUND
  2. BigInt in MySQL
  3. MySQL Update Set
  4. MySQL Merge
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more