MySQL DECIMAL and NUMERIC Data Types

MySQL DECIMAL stores exact fixed-point values. It is useful when values need a defined number of fractional digits, such as invoice amounts. NUMERIC, DEC, and FIXED are synonyms for DECIMAL in MySQL.

Syntax and defaults

DECIMAL[(M[,D])]

• M is the total number of digits (precision), from 1 to 65. If omitted, it defaults to 10.
• D is the number of digits after the decimal point (scale), from 0 to 30 and no greater than M. If omitted, it defaults to 0.
• DECIMAL is equivalent to DECIMAL(10,0), and DECIMAL(5) is equivalent to DECIMAL(5,0).

For example, DECIMAL(7,2) has five digits before the decimal point and two after it, so its range is -99999.99 through 99999.99.

Example: Store an exact amount

CREATE TABLE invoice_line (
    line_id INT PRIMARY KEY,
    amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO invoice_line (line_id, amount)
VALUES (1, 125.40);

SELECT amount
FROM invoice_line;

DECIMAL(10,2) allows up to eight digits before the decimal point and two after it. Choose the precision and scale for the valid range and rounding rules of your application; values assigned with a different scale are converted to the declared scale.

DECIMAL and approximate types

DECIMAL and its synonyms are fixed-point types. MySQL performs exact-value arithmetic for them, subject to the column precision and scale. FLOAT and DOUBLE are approximate binary floating-point types, so they are not a substitute when exact decimal behavior is required. See the MySQL fixed-point type documentation and numeric type syntax.

Deprecated numeric attributes

MySQL 9.7 deprecates the UNSIGNED attribute for DECIMAL and the ZEROFILL attribute for numeric types. Avoid relying on these attributes in new schemas. To enforce nonnegative values, use a CHECK constraint on MySQL 8.0.16 or later. Earlier MySQL versions parse but ignore CHECK constraints, so validate values in the application instead. Handle zero-padding in presentation code. See MySQL’s CHECK constraint documentation.