MySQL DECIMAL Data Type

Introduction

In this chapter, we will learn about the DECIMAL data type in MySQL. The DECIMAL data type is used to store exact numeric values, which is especially useful for storing financial data where precision is crucial. Unlike floating-point numbers, DECIMAL values are stored as strings, which allows for precise representation of decimal values without rounding errors. We will cover the syntax for defining DECIMAL columns, examples, and important considerations for using DECIMAL in MySQL.

Syntax

The basic syntax for defining a DECIMAL column in MySQL is:

DECIMAL(precision, scale)

• precision: The total number of digits that can be stored, both to the left and right of the decimal point. The maximum precision is 65.
• scale: The number of digits that can be stored to the right of the decimal point. The scale must be less than or equal to the precision.

Example

Here is an example of how to define a DECIMAL column in a table:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    discount DECIMAL(5, 2)
);

In this example, price is defined as a DECIMAL(10, 2), meaning it can store up to 10 digits in total, with 2 digits to the right of the decimal point. Similarly, discount is defined as DECIMAL(5, 2).

Full Example

Let’s go through a complete example where we create a database and a table with DECIMAL columns, insert data, and demonstrate their usage.

1. Create a Database:

CREATE DATABASE store;

2. Select the Database:

USE store;

3. Create the Products Table with DECIMAL Columns:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    discount DECIMAL(5, 2)
);

4. Insert Data into the Products Table:

INSERT INTO products (name, price, discount) VALUES
('Laptop', 999.99, 10.00),
('Smartphone', 499.49, 5.00),
('Tablet', 299.99, 7.50),
('Smartwatch', 199.95, 3.00),
('Headphones', 99.99, 2.50);

5. Query the Products Table:

SELECT * FROM products;

Output

In this example, the price and discount columns are defined as DECIMAL to store precise monetary values.

Important Considerations

• Precision and Scale: Choose appropriate precision and scale based on the expected range and required precision of the values to be stored. For example, for financial data, ensure that the scale is sufficient to capture all necessary decimal places.
• Storage Requirements: The storage requirements for DECIMAL values depend on the precision. MySQL allocates 4 bytes for each multiple of 9 digits.
• Comparison with Floating-Point Types: Unlike floating-point types (FLOAT and DOUBLE), DECIMAL is used for storing exact values. Use DECIMAL when precision is critical, such as in financial calculations.
• Performance: While DECIMAL provides precise representation, it may have a performance overhead compared to integer types. Consider the performance implications if the table will store a large number of rows.

Conclusion

The DECIMAL data type is essential for storing exact numeric values in MySQL, making it especially useful for financial and monetary data. This chapter covered the syntax for defining DECIMAL columns, provided a full example with different decimal attributes, and discussed important considerations.