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?

Souy Soeng

MySQL DECIMAL Data Type