MySQL DECIMAL Data Type
Summary: in this tutorial, we will introduce you to the MySQL DECIMAL data type and how to use it effectively in your database table.
Introduction to MySQL DECIMAL data type
The MySQL DECIMAL data type is used to store exact numeric values in the database. We often use the DECIMAL data type for columns that preserve exact precision e.g., money data in accounting systems.
To define a column whose data type is DECIMAL you use the following syntax:
column_name DECIMAL(P,D);
In the syntax above:
- P is the precision that represents the number of significant digits. The range of P is 1 to 65.
- D is the scale that represents the number of digits after the decimal point. The range of D is 0 and 30. MySQL requires that D is less than or equal to (<=) P.
The DECIMAL(P,D)
means that the column can store up to P digits with D decimals. The actual range of the decimal column depends on the precision and scale.
Besides the DECIMAL
keyword, you can also use DEC
, FIXED
, or NUMERIC
because they are synonyms for DECIMAL
.
Like the INT data type, the DECIMAL
type also has UNSIGNED
and ZEROFILL
attributes. If we use the UNSIGNED
attribute, the column with DECIMAL UNSIGNED
will not accept negative values.
In case we use ZEROFILL
, MySQL will pad the display value by 0 up to the display width specified by the column definition. In addition, if we use ZERO FILL
for the DECIMAL
column, MySQL will add the UNSIGNED
attribute to the column automatically.
The following example defines the amount column with DECIMAL
datatype.
amount DECIMAL(6,2);
In this example, the amount column can store 6 digits with 2 decimal places; therefore, the range of the amount column is from 9999.99 to -9999.99.
MySQL allows us to use the following syntax:
column_name DECIMAL(P);
This is equivalent to:
column_name DECIMAL(P,0);
In this case, the column contains no fractional part or decimal point.
In addition, we can even use the following syntax.
column_name DECIMAL;
The default value of P is 10 in this case.
MySQL DECIMAL storage
MySQL assigns the storage for integer and fractional parts separately. MySQL uses the binary format to store the DECIMAL
values. It packs 9 digits into 4 bytes.
For each part, it takes 4 bytes to store each multiple of 9 digits. The storage required for leftover digits is illustrated in the following table:
Leftover Digits | Bytes |
---|---|
0 | 0 |
1–2 | 1 |
3–4 | 2 |
5–6 | 3 |
7–9 | 4 |
For example, DECIMAL(19,9)
has 9 digits for the fractional part and 19-9 = 10 digits for the integer part. The fractional part requires 4 bytes. The integer part requires 4 bytes for the first 9 digits, for 1 leftover digit, it requires 1 more byte. In total, the DECIMAL(19,9)
column requires 9 bytes.
MySQL DECIMAL data type and monetary data
We often use the DECIMAL
the data type for monetary data such as prices, salary, account balances, etc. If you design a database that handles the monetary data, the following syntax should be OK.
amount DECIMAL(19,2);
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:
amount DECIMAL(19,4);
MySQL DECIMAL data type example
First, create a new table named materials
with three columns: id, description, and cost.
CREATE TABLE materials (
id INT AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(255),
cost DECIMAL(19 , 4 ) NOT NULL
);
Second, insert data into the materials
table.
INSERT INTO materials(description,cost)
VALUES('Bicycle', 500.34),('Seat',10.23),('Break',5.21);
Third, query data from the materials
table.
SELECT
*
FROM
materials;
Fourth, change the cost column to include the ZEROFILL
attribute.
ALTER TABLE materials
MODIFY cost DECIMAL(19,4) zerofill;
Fifth, query the materials table again.
SELECT
*
FROM
materials;
As you see, we have many zeros padded in the output values.
In this tutorial, we have shown gave you detailed information on MySQL DECIMAL data type and shown you how to apply it to the columns that store exact numeric data such as financial data.
0 Comments
CAN FEEDBACK
Emoji