MySQL DECIMAL(M,D): Precision, Scale, Range, and Examples

MySQL DECIMAL(M,D) stores exact decimal numbers, making it suitable for prices, financial totals, and other quantities that must not accumulate binary floating-point rounding error. Unlike FLOAT or DOUBLE, it represents decimal values using an exact-value format.

DECIMAL precision, scale, and range

DECIMAL(M,D)

• M is the total number of decimal digits (precision), including digits on both sides of the decimal point. MySQL supports 1 through 65.
• D is the number of digits after the decimal point (scale). MySQL supports 0 through 30, with D <= M.
• Omitting D defaults it to 0; omitting M defaults it to 10. Thus DECIMAL alone means DECIMAL(10,0).

For a signed DECIMAL(M,D), there are M - D digits available to the left of the decimal point:

DECIMAL(10,2) does not mean 10 digits before the decimal point. It means 10 digits in total, of which two are fractional. See the official MySQL DECIMAL characteristics.

Example: store exact prices

Store the amount as a number and the currency separately. A DECIMAL column does not store a currency symbol:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    currency_code CHAR(3) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL
);

INSERT INTO orders (id, currency_code, total_amount)
VALUES (1, 'USD', 99.99),
       (2, 'EUR', 499.50);

SELECT id, currency_code, total_amount
FROM orders
ORDER BY id;

id  currency_code  total_amount
--  -------------  ------------
1   USD            99.99
2   EUR            499.50

The total_amount column preserves two decimal places in MySQL’s exact-value representation. The application should also use decimal-safe numeric types when reading and calculating these values; converting them to binary floating-point types can reintroduce rounding error.

DECIMAL versus FLOAT and DOUBLE

MySQL NUMERIC(M,D) is a synonym for DECIMAL(M,D). Choosing DECIMAL does not eliminate the need to select an appropriate scale, validate inputs, and handle out-of-range values. For more examples, see the MySQL DECIMAL guide and the official precision-math documentation.