Introduction to MySQL DECIMAL Data Type

DECIMAL is a MySQL data type used for storing precise numeric values. It is particularly useful for handling currency and other data that requires high-precision calculations.

Syntax

DECIMAL(M,D)

M: Represents the total number of digits, which includes both the integer and fractional parts.
D: Represents the number of digits after the decimal point.

For example, DECIMAL(10,2) indicates that there can be a maximum of 10 digits, with 2 digits after the decimal point. Therefore, the maximum value is 99999999.99, and the minimum value is -99999999.99.

Use Cases

The DECIMAL type is commonly used for storing currency amounts, percentages, and other data that requires high-precision calculations. Due to issues with floating-point precision, it is not recommended to use the FLOAT or DOUBLE data types in these cases.

Examples

Example 1: Storing Currency Amounts

Suppose you want to store the total amount of an order, including the currency symbol and decimal point. You can use the DECIMAL data type:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  total_amount DECIMAL(10,2)
);

INSERT INTO orders VALUES (1, 99.99);
INSERT INTO orders VALUES (2, 499.50);
INSERT INTO orders VALUES (3, 1099.00);

Query:

SELECT * FROM orders;

Result:

+----+--------------+
| id | total_amount |
+----+--------------+
|  1 |        99.99 |
|  2 |       499.50 |
|  3 |      1099.00 |
+----+--------------+

Example 2: Storing Percentages

Suppose you want to store the completion rate of a sales team, you can use the DECIMAL data type:

CREATE TABLE sales_team (
  id INT PRIMARY KEY,
  completion_rate DECIMAL(5,2)
);

INSERT INTO sales_team VALUES (1, 80.50);
INSERT INTO sales_team VALUES (2, 100.00);
INSERT INTO sales_team VALUES (3, 50.00);