MySQL DECIMAL Data Type
The DECIMAL data type in MySQL is used to store exact numeric values with a specified precision and scale. It is especially useful when dealing with financial calculations, percentages, and other situations where accuracy is important. Unlike the FLOAT or DOUBLE data types, which may introduce rounding errors due to their approximation, the DECIMAL type allows you to store exact values.
1. DECIMAL Syntax
DECIMAL(M, D)
Where:
M is the total number of digits (precision). This includes both digits before and after the decimal point.
D is the number of digits after the decimal point (scale).
M can range from 1 to 65.
D can range from 0 to M (but typically, D ≤ M).
Example:
DECIMAL(10, 2)
- M = 10 means you can store up to 10 digits in total.
- D = 2 means there are 2 digits after the decimal point.
2. Using DECIMAL in Table Definitions
You can use the DECIMAL data type in a table to store exact numeric values. Here's an example of defining a table with a price
column:
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10, 2) -- 10 total digits, 2 after the decimal point
);
- This allows you to store prices up to 99999999.99 (i.e., 8 digits before the decimal and 2 digits after).
3. DECIMAL Examples
Inserting Data
INSERT INTO products (name, price) VALUES ('Laptop', 999.99);
INSERT INTO products (name, price) VALUES ('Smartphone', 499.50);
- The
price
values are stored exactly as entered without rounding.
Selecting Data
SELECT name, price FROM products;
- The result will show the exact values as stored: 999.99 and 499.50.
4. Arithmetic Operations with DECIMAL
You can perform arithmetic operations on DECIMAL values, and MySQL will maintain precision without rounding errors:
SELECT price * 1.10 AS price_with_tax FROM products;
- This will multiply the
price
by 1.10 (e.g., adding a 10% tax), and the result will be accurate without floating-point rounding errors.
5. Precision and Scale Behavior
1. When the Scale is Greater Than Precision
If D > M, MySQL will return an error.
DECIMAL(4, 5) -- Invalid! Scale cannot exceed precision.
2. When the Precision is Greater Than Needed
If the value exceeds the specified precision, MySQL will round the value:
DECIMAL(5, 2) -- Precision of 5, Scale of 2
INSERT INTO products (name, price) VALUES ('Car', 99999.999); -- Rounded to 100000.00
3. When the Scale is 0
If D = 0, the value will be rounded to the nearest integer:
DECIMAL(5, 0) -- No digits after decimal point
INSERT INTO products (name, price) VALUES ('Table', 299.99); -- Stored as 300
6. DECIMAL vs. FLOAT/DOUBLE
Data Type | Storage | Use Case | Accuracy | Example |
---|---|---|---|---|
DECIMAL | Exact | Financial data, monetary values | High, no rounding errors | 99999.99 |
FLOAT | Approximate | Scientific calculations, statistics | Low, rounding errors possible | 99999.99 (approx.) |
DOUBLE | Approximate | High-precision scientific work | Low, rounding errors possible | 99999.99999 (approx.) |
- DECIMAL is ideal when you need to store exact numbers, especially for money or financial data where precision is critical.
7. Best Practices for DECIMAL in MySQL
- Choose the right precision and scale based on your data needs. For example, store prices as
DECIMAL(10, 2)
to handle most cases of currency. - Use DECIMAL for data that needs to be exact, such as currency, percentages, and other monetary calculations.
- Use FLOAT or DOUBLE for scientific or statistical data where small approximations are acceptable.
- Avoid rounding errors by using DECIMAL for high-precision values in financial applications.
8. Summary
- DECIMAL allows you to store exact numeric values with a defined precision and scale.
- It is most suitable for financial and monetary data where rounding errors are not acceptable.
- Precision (M) defines the total number of digits, and Scale (D) defines how many digits are after the decimal point.
- DECIMAL provides higher accuracy compared to FLOAT and DOUBLE.
Would you like to see an example of using DECIMAL for financial calculations in more detail?