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.
- Create a Database:
CREATE DATABASE store;
- Select the Database:
USE store;
- 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)
);
- 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);
- Query the Products Table:
SELECT * FROM products;
Output
id | name | price | discount |
---|---|---|---|
1 | Laptop | 999.99 | 10.00 |
2 | Smartphone | 499.49 | 5.00 |
3 | Tablet | 299.99 | 7.50 |
4 | Smartwatch | 199.95 | 3.00 |
5 | Headphones | 99.99 | 2.50 |
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
andDOUBLE
),DECIMAL
is used for storing exact values. UseDECIMAL
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.