Decimal and floating-point types in MySQL

Introduction

Decimal and floating-point types store numbers with fractions. They help keep numbers accurate when you need decimals, like money or measurements.

When storing prices in a store database to avoid rounding errors.

When saving measurements like weight or height that need decimals.

When calculating interest rates where exact decimal values matter.

When you want to store very large or very small numbers with decimals.

When you need to do math with decimals and want to keep precision.

Syntax

MySQL

DECIMAL(M, D)
FLOAT
DOUBLE

DECIMAL stores exact numbers with fixed decimals. M is total digits, D is digits after decimal.

FLOAT and DOUBLE store approximate numbers with floating decimals, good for scientific data.

Examples

Stores prices with up to 10 digits total and 2 digits after the decimal point.

MySQL

price DECIMAL(10, 2)

Stores weight as a floating-point number, which can handle decimals but may have small rounding errors.

MySQL

weight FLOAT

Stores distance with double precision floating-point, more accurate than FLOAT.

MySQL

distance DOUBLE

Sample Program

This creates a products table with price as DECIMAL and weight as FLOAT, inserts two products, and selects their details.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(8, 2),
  weight FLOAT
);

INSERT INTO products VALUES (1, 'Apple', 0.99, 0.15);
INSERT INTO products VALUES (2, 'Banana', 1.25, 0.20);

SELECT name, price, weight FROM products;

Important Notes

Use DECIMAL when you need exact decimal values, like money.

FLOAT and DOUBLE can store very large or small numbers but may have tiny rounding errors.

DECIMAL takes more storage space than FLOAT or DOUBLE.

Summary

DECIMAL stores exact decimal numbers with fixed digits.

FLOAT and DOUBLE store approximate decimal numbers with floating precision.

Choose DECIMAL for money and precise decimals, FLOAT/DOUBLE for scientific or approximate values.