MySQL | DECIMAL and NUMERIC
Start your free 7-days trial now!
In MySQL, DECIMAL and NUMERIC are collectively referred to as Fixed-Point types. This just means they store exact values with a specific precision, which is particularly important for information such as monetary data.
In MySQL NUMERIC is implemented as DECIMAL hence please treat everything that applies to DECIMAL as being applicable to NUMERIC also.
DECIMAL
The general syntax for column declaration:
column_name DECIMAL(M,D)
M: total number of digits. Defaults to 10. Maximum is 65.
D: number of digits after the decimal point. Defaults to 0. Maximum is 30.
The decimal point and - sign (if applicable) are not counted in M (total number of digits).
Example
If we made the below column declaration for a column called score:
score DECIMAL(4,1)
We are able to store any value with 4 digits including 1 decimal. Therefore the range of values that could be stored in this column would be -999.9 to 999.9
To create a table test with column score declared as NUMERIC:
CREATE TABLE test (score NUMERIC(4,1));
Query OK, 0 rows affected (0.01 sec)
To check the attributes of table test:
DESCRIBE test;
+-------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| score | decimal(4,1) | YES | | NULL | |+-------+--------------+------+-----+---------+-------+
We can see that the type of score appears as decimal(4,1) due to the fact NUMERIC is implemented as DECIMAL in MySQL.
