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.
The following are the syntax used for defining a column whose data type is DECIMAL:
Let us discuss the arguments M and D:
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: