How to use MySQL DECIMAL?

Solution 1:

DOUBLE columns are not the same as DECIMAL columns, and you will get in trouble if you use DOUBLE columns for financial data.

DOUBLE is actually just a double precision (64 bit instead of 32 bit) version of FLOAT. Floating point numbers are approximate representations of real numbers and they are not exact. In fact, simple numbers like 0.01 do not have an exact representation in FLOAT or DOUBLE types.

DECIMAL columns are exact representations, but they take up a lot more space for a much smaller range of possible numbers. To create a column capable of holding values from 0.0001 to 99.9999 like you asked you would need the following statement

CREATE TABLE your_table
(
    your_column DECIMAL(6,4) NOT NULL
);

The column definition follows the format DECIMAL(M, D) where M is the maximum number of digits (the precision) and D is the number of digits to the right of the decimal point (the scale).

This means that the previous command creates a column that accepts values from -99.9999 to 99.9999. You may also create an UNSIGNED DECIMAL column, ranging from 0.0000 to 99.9999.

As an example, if you want a column that accepts values from -9999.99 to 9999.99 the command would be DECIMAL(6,2). As you can see, you still use a precision of 6, but only allow a scale of 2.

For more information on MySQL DECIMAL the official docs are always a great resource.

Bear in mind that all of this information is true for versions of MySQL 5.0.3 and greater. If you are using previous versions, you really should upgrade.

Update on MySQL 8.0.17+

Unsigned is deprecated for FLOAT, DOUBLE, and DECIMAL columns.

Solution 2:

Although the answers above seems correct, just a simple explanation to give you an idea of how it works.

Suppose that your column is set to be DECIMAL(13,4). This means that the column will have a total size of 13 digits where 4 of these will be used for precision representation.

So, in summary, for that column you would have a max value of: 999999999.9999