Updated June 6, 2023
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.
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 –
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 –
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 –
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 –
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 –
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.
