How to use MySQL DECIMAL?

Question

I can't quite get a grasp of MySQL's DECIMAL. I need the row to be able to contain a number anywhere from 00.0001 to 99.9999. How would I structure it to work like so?

Answer 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.

Answer 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

Answer 3

There are correct solutions in the comments, but to summarize them into a single answer:

You have to use DECIMAL(6,4).

Then you can have 6 total number of digits, 2 before and 4 after the decimal point (the scale). At least according to this.

Answer 4

MySQL 5.x specification for decimal datatype is: DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]. The answer above is wrong (now corrected) in saying that unsigned decimals are not possible.

To define a field allowing only unsigned decimals, with a total length of 6 digits, 4 of which are decimals, you would use: DECIMAL (6,4) UNSIGNED.

You can likewise create unsigned (ie. not negative) FLOAT and DOUBLE datatypes.

---

Update on MySQL 8.0.17+, as in MySQL 8 Manual: 11.1.1 Numeric Data Type Syntax:

"Numeric data types that permit the UNSIGNED attribute also permit SIGNED. However, these data types are signed by default, so the SIGNED attribute has no effect.*

As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms); you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.