Examples with Explanation

1. DECIMAL

Let’s explore a practical example of using DECIMAL in a MySQL query:

--SQL query

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  price DECIMAL(10,2) NOT NULL
);

INSERT INTO products (product_id, price) VALUES (1, 123.456789);

SELECT * FROM products;

Output:

Explanation: The SQL code creates a table named ‘products’ with columns for product_id as the primary key and price as a decimal with two decimal places. It then inserts a record with a product_id of 1 and a price of 123.46. The SELECT statement retrieves and displays all records from the ‘products’ table.

2. FLOAT

Let’s explore a practical example of using FLOAT in a MySQL query:

 CREATE TABLE experiments (
  experiment_id INT PRIMARY KEY,
  measurement FLOAT(7,3) NOT NULL
);

INSERT INTO experiments (experiment_id, measurement) VALUES (1, 3.141592653589793);

SELECT * FROM experiments;

Output:

Explanation: The SQL code creates a table ‘experiments‘ with columns for experiment_id as the primary key and measurement as a floating-point number with 7 total digits and 3 decimal places. It inserts a record with experiment_id 1 and a measurement of 3.142. The SELECT statement retrieves and displays all records from the ‘experiments‘ table.

3. Comparison of Precision

CREATE TABLE precision_comparison (
    float_value FLOAT(10, 5),
    decimal_value DECIMAL(10, 5)
);

INSERT INTO precision_comparison (float_value, decimal_value) VALUES (1234.56789, 1234.56789);

SELECT * FROM precision_comparison;

Output:

Explanation: The SQL code creates a table named ‘precision_comparison‘ with columns for float_value (FLOAT with a precision of 10 and 5 decimal places) and decimal_value (DECIMAL with a precision of 10 and 5 decimal places). It inserts a record with values (1234.56789, 1234.56789) and then retrieves and displays all records from the table. The comparison highlights the potential rounding difference between FLOAT and DECIMAL data types.

In MySQL, when dealing with numbers, you have two main choices: FLOAT and DECIMAL. Each has its unique strengths and best uses. Picking the right one is vital for accurate and efficient data handling in your database. Understanding their differences helps you make smart decisions for better performance and data integrity.