MySQL DECIMAL

This data type is used for storing the exact numeric values. It is similar to the INT data type and used for columns that preserve exact precision, such as monetary data in an accounting system like salary, price, etc. MySQL always used a binary format for storing the DECIMAL values.

Syntax

The following are the syntax used for defining a column whose data type is DECIMAL:

Let us discuss the arguments M and D:

• M: It is the precision that represents the maximum number of significant digits stored for values. The range of M must be between 1 to 65. Here, the maximum value for M is 65, which means the calculations on DECIMAL values are not more than 65 digits.
• D: It is a scale that represents the total number of digits right to the decimal point M. The range of D must be between 0 to 30 and should not be greater than M.

The above declaration tells that the column can store M digits with D decimal means it depends on the precision and scale. If D is not defined, it assumes default value 0. If M is not specified, it assumes default value 10. We can also use DEC, FIXED, NUMERIC keyword instead of DECIMAL because they are synonyms to this data type.

Since DECIMAL is similar to INT data type, it can also accept UNSIGNED and ZEROFILL attributes. If we use Unsigned, the DECIMAL value cannot accept negative values. While if the user uses the ZEROFILL attribute, MySQL gives the value of M by 0 up to the width specified in the column definition.

MySQL DECIMAL storage

MySQL uses the binary format to store values in a DECIMAL column that takes 4 bytes for each multiple of nine decimal digits. It assigns the storage requirements for integer and fractional parts of each value separately. MySQL packs first nine decimal digits into four bytes, and remaining digits (leftover digits) require a fraction of 4 bytes.

The storage required for remaining (leftover) digits can be understood with the following table:

We can understand the illustration of the above table with the following example:

Suppose DECIMAL(20, 9) has nine digits for the fractional part and 20-9=11 digits for integer parts. Next, the fractional part takes 4 bytes, and the integer part takes 4 bytes for the first nine digits and requires one more byte for leftover digits that is 2. Thus, the above data type column requires a total of 9 bytes.

MySQL DECIMAL Example

Let us first create a new table "orders" in the database that contains three columns: order_id, prod_name, and price. Execute the following query in the command prompt:

Next, we need to insert the data into newly created table orders. Execute the below query that inserts three records into the table:

Finally, run this statement to fetch the "orders" table:

We will get the output as below:

Now, we are going to see the use of ZEROFILL attribute by modifying the price column as below:

Now, run the below statement again to fetch the "orders" table:

We will get the output as below where many zeros added in the price column: