Summary: in this tutorial, you will learn about MySQL DECIMAL
data type and how to use it to store exact numeric values in the databases.
Introduction to MySQL DECIMAL data type
The MySQL DECIMAL
data type allows you to store exact numeric values in the database. In practice, you often use the DECIMAL
data type for columns that preserve exact precision e.g., monetary data in financial systems.
To define a column whose data type is DECIMA
L, you use the following syntax:
column_name DECIMAL(P,D);
Code language: SQL (Structured Query Language) (sql)
In this syntax:
P
is the precision that represents the number of significant digits. The range ofP
is 1 to 65.D
is the scale that represents the number of digits after the decimal point. The range ofD
is 0 and 30. MySQL requires thatD
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
.
The following example defines the amount
column with DECIMAL
data type.
amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)
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 you to use the following syntax:
column_name DECIMAL(P);
Code language: SQL (Structured Query Language) (sql)
This is equivalent to:
column_name DECIMAL(P,0);
Code language: SQL (Structured Query Language) (sql)
In this case, the column contains no fractional part or decimal point.
In addition, you can even use the following syntax:
column_name DECIMAL;
Code language: SQL (Structured Query Language) (sql)
The default value of P is 10 and D is 0, which is equivalent to the following:
column_name DECIMAL(10,0);
MySQL DECIMAL storage
MySQL assigns the storage for integer and fractional parts separately. MySQL uses a 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
data type for monetary data such as prices, salary, account balances, and so on. If you design a database that handles the monetary data, the following syntax should be fine.
amount DECIMAL(19,2);
Code language: SQL (Structured Query Language) (sql)
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);
Code language: SQL (Structured Query Language) (sql)
MySQL DECIMAL data type example
First, create a new table named materials
:
CREATE TABLE materials (
id INT AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(255) NOT NULL,
cost DECIMAL(19,4) NOT NULL
);
Code language: PHP (php)
The materials
table has three columns:
id
is the auto-increment primary key column with theINT
data type.description
represents the material’s description with theVARCHAR
data type.cost
represents the cost of the material, which has theDECIMAL(19,4)
.
The cost
column can store up to 19 digits with 4 decimal places.
Second, insert data into the materials
table.
INSERT INTO materials(description, cost)
VALUES
('Bicycle', 500.34),
('Seat', 10.23),
('Break', 5.21);
Code language: SQL (Structured Query Language) (sql)
Third, query data from the materials
table.
SELECT
*
FROM
materials;
Code language: SQL (Structured Query Language) (sql)
Output:
+----+-------------+----------+
| id | description | cost |
+----+-------------+----------+
| 1 | Bicycle | 500.3400 |
| 2 | Seat | 10.2300 |
| 3 | Break | 5.2100 |
+----+-------------+----------+
3 rows in set (0.00 sec)
Code language: JavaScript (javascript)
Summary
- Use MySQL
DECIMAL
data type to store exact numeric values such as financial data in the database. - Use
column_name DECIMAL (P, D)
to define a column with theDECIMAL
data type that has up toP
digits andD
decimal places. - The
DECIMAL(P)
is equivalent toDECIMAL(P,0)
andDECIMAL
is equivalent toDECIMAL(P, 0)
.