MySQL DECIMAL

Navdeep kaur Mysql

If your instead to learn about MySQL Set

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. In MySQL, the DECIMAL and NUMERIC data types store exact, fixed-point values. In MySQL, NUMERIC is implemented as a DECIMAL, so a NUMERIC and DECIMAL are the same data type. This data type is used when it is important to preserve the exact precision, such as storing money data.

Syntax

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

column_name DECIMAL (M, D);

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.31.9M651How to find Nth Highest Salary in SQL.

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.

How do I set decimal points in MySQL?

MySQL assigns the storage for integer and fractional parts separately. MySQL uses binary format to store the DECIMAL values. It packs 9 digits into 4 bytes. For example, DECIMAL(19,9) has 9 digits for the fractional part and 19-9 = 10 digits for integer part.

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:

Leftover DigitsNumber of Bytes
00
1-21
3-42
5-63
7-94

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:

mysql> CREATE TABLE orders (order_id INT NOT NULL PRIMARY KEY, prod_name VARCHAR(35) NULL, price DECIMAL(15,3) NOT NULL);

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

mysql> INSERT INTO orders(order_id, prod_name, price) VALUES (1,'Laptop', 15000.35), (2, 'iPhone', 20000.32), (3, 'Mouse', 200.32);

Finally, run this statement to fetch the “orders” table:

mysql> SELECT * FROM orders;

We will get the output as below:

MySQL DECIMAL

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

mysql> ALTER TABLE orders MODIFY price DECIMAL(15, 3) zerofill;

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

mysql> SELECT * FROM orders;

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

MySQL DECIMAL

MySQL DECIMAL data type and monetary data

We often use the DECIMAL data type for monetary data such as prices, salary, account balances, etc. If you design a database that handle the monetary data, the following syntax should be OK.

<code>amount DECIMAL(19,2);</code><small>Code language: SQL (Structured Query Language) (sql)</small>

However, if you want to comply with Generally Accepted Accounting Principles (GAAP) rules, the monetary column must have at least 4 decimal places to make sure that the rounding value does not exceed $0.01. In this case, you should define the column with 4 decimal places as follows:

<code>amount DECIMAL(19,4);</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Attributes:

As with the INTEGER datatype having attributes UNSIGNED and ZEROFILL attribute. Similarly, DECIMAL datatype also has UNSIGNED and ZEROFILL attributes that can be assigned to the decimal values. The UNSIGNED attribute, when assigned to the DECIMAL data, typed column helps us to restrict the storage of negative decimal numbers and makes sure that only positive decimal values are stored in that column.

Behavior:

Consider one example where the datatype for the column is defined in the following way –

DECIMAL(5,3) that 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 value of scale is considered as 0 and no numeric values containing decimal points can be stored in this column. Only integral numbers can be stored with a range of -999 to 999. Let us take one more example where we do not specify either of the parameter value i.e precision or scale and declare the datatype of the column as DECIMAL. In this case, the default value of the precision will be considered as 10 and for scale parameter zero value will be considered. Hence, the allowed range of the values to be stored in this column will range from -9999999999 to 9999999999. Hence, it can be concluded that the precision and scale parameter values are completely optional for specification and the default value of scale is 0 while for precision parameter default value is 10.

MySQL DECIMAL data type example

First, create a new table named materials with three columns: id, description, and cost.

<code>CREATE TABLE materials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(255),
    cost DECIMAL(19 , 4 ) NOT NULL
);</code><small>Code language: PHP (php)</small>

Second, insert data into the materials table.

<code>INSERT INTO materials(description,cost)
VALUES('Bicycle', 500.34),('Seat',10.23),('Break',5.21);</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Third, query data from the materials table.

<code>SELECT 
    *
FROM
    materials;</code><small>Code language: SQL (Structured Query Language) (sql)</small>
MySQL DECIMAL example

Fourth, change the cost column to include  the ZEROFILL attribute.

<code>ALTER TABLE materials
MODIFY cost DECIMAL(19,4) zerofill;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Fifth, query the materials table again.

<code>SELECT 
    *
FROM
    materials;</code><small>Code language: SQL (Structured Query Language) (sql)</small>
MySQL DECIMAL ZEROFILL Example

As you see, we have many zeros padded in the output values.

MySQL DECIMAL
Show Buttons
Hide Buttons