Decimal and floating-point types in MySQL - Step-by-Step Execution

Concept Flow - Decimal and floating-point types

Start: Define column type

↓

Choose DECIMAL or FLOAT/DOUBLE

↓

Store value with precision

↓

Retrieve value

↓

Observe exactness or approximation

↓

Use in calculations

↓

End

This flow shows how MySQL stores and retrieves decimal and floating-point numbers, highlighting exactness for DECIMAL and approximation for FLOAT/DOUBLE.

Execution Sample

MySQL

CREATE TABLE prices (
  price_decimal DECIMAL(5,2),
  price_float FLOAT
);

INSERT INTO prices VALUES (123.45, 123.45);
SELECT * FROM prices;

UPDATE prices SET price_decimal = 0.1, price_float = 0.1;
SELECT * FROM prices;

This code creates a table with DECIMAL and FLOAT columns, inserts 123.45 into both, selects to show storage, updates to 0.1, and selects again to demonstrate exact vs approximate storage.

Execution Table

Step	Action	Input Value	Stored Value in DECIMAL	Stored Value in FLOAT	Note
1	Create table with DECIMAL(5,2) and FLOAT columns	N/A	N/A	N/A	Table schema defined
2	Insert value 123.45 into both columns	123.45	123.45	123.44999694824219	FLOAT stores approximate value
3	Select values from table	N/A	123.45	123.44999694824219	DECIMAL exact, FLOAT approximate
4	Update both columns to 0.1	0.1	0.10	0.10000000149011612	FLOAT cannot store 0.1 exactly
5	Select values from table	N/A	0.10	0.10000000149011612	Shows difference in precision
6	End	N/A	N/A	N/A	Demonstrated exact vs approximate storage

💡 Execution ends after showing how DECIMAL stores exact values and FLOAT stores approximate values.

Variable Tracker

Variable	Start	After Insert 123.45	After Update 0.1	Final
price_decimal	NULL	123.45	0.10	0.10
price_float	NULL	123.44999694824219	0.10000000149011612	0.10000000149011612

Key Moments - 3 Insights

Why does the FLOAT column show a slightly different number than what was inserted?

FLOAT stores numbers approximately using binary floating-point, so some decimal numbers like 0.1 cannot be represented exactly, as shown in execution_table rows 2 and 4.

Why does DECIMAL store the exact number I inserted?

DECIMAL stores numbers as strings of digits with fixed precision, preserving exact decimal values, as seen in execution_table rows 2 and 4.

What happens if I insert a number with more decimal places than DECIMAL(5,2) allows?

MySQL rounds or truncates the number to fit the defined precision, ensuring stored values match the DECIMAL scale, which is why we see 0.10 instead of 0.1 in row 4.

Visual Quiz - 3 Questions

Look at the execution_table at step 2. What is the stored value in the FLOAT column after inserting 123.45?

A 123.45 exactly

B 123.00

C Approximately 123.44999694824219

D NULL

💡 Hint

Check the 'Stored Value in FLOAT' column at step 2 in the execution_table.

At which step does the DECIMAL column store the value 0.10?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Look at the 'Stored Value in DECIMAL' column for step 4 in the execution_table.

If you insert 0.1234 into DECIMAL(5,2), what will happen to the stored value?

A It stores 0.12 after rounding/truncation

B It stores 0.1234 exactly

C It stores 0.13 after rounding

D It stores NULL

💡 Hint

DECIMAL(5,2) allows 2 decimal places; extra digits are rounded as per MySQL rules.

Concept Snapshot

DECIMAL stores exact decimal numbers with fixed precision.
FLOAT and DOUBLE store approximate values using binary floating-point.
Use DECIMAL for money or exact values.
FLOAT is faster but can have rounding errors.
Define DECIMAL as DECIMAL(M,D) where M=total digits, D=digits after decimal.

Full Transcript

This lesson shows how MySQL handles decimal and floating-point numbers. DECIMAL stores numbers exactly with fixed digits after the decimal point. FLOAT stores numbers approximately using binary floating-point, which can cause small rounding differences. We created a table with DECIMAL(5,2) and FLOAT columns, inserted values like 123.45 and 0.1, and selected them to see how they are stored. DECIMAL preserved the exact values, while FLOAT showed slight differences. This helps understand when to use DECIMAL for precise values like money, and FLOAT for approximate values where speed matters.

Practice

1. Which MySQL data type should you use to store exact monetary values without rounding errors?

easy

A. VARCHAR

B. FLOAT

C. DECIMAL

D. DOUBLE

Solution

1. Step 1: Understand the purpose of DECIMAL

   DECIMAL stores exact decimal numbers with fixed digits, ideal for money.

2. Step 2: Compare with FLOAT and DOUBLE

   FLOAT and DOUBLE store approximate values, which can cause rounding errors.

3. Final Answer:

   DECIMAL -> Option C

4. Quick Check:

   Exact decimal storage = DECIMAL [OK]

Hint: Use DECIMAL for exact money values, not FLOAT or DOUBLE [OK]

Common Mistakes:

• Choosing FLOAT or DOUBLE for money causing rounding errors
• Using VARCHAR which stores text, not numbers

2. Which of the following is the correct syntax to define a DECIMAL column with 8 digits total and 2 digits after the decimal point in MySQL?

easy

A. DECIMAL(8,2)

B. DECIMAL(2,8)

C. DECIMAL(10,2)

D. DECIMAL(8)

Solution

1. Step 1: Recall DECIMAL syntax

   DECIMAL(M,D) means M total digits, D digits after decimal point.

2. Step 2: Match given digits

   We want 8 total digits and 2 after decimal, so DECIMAL(8,2) is correct.

3. Final Answer:

   DECIMAL(8,2) -> Option A

4. Quick Check:

   DECIMAL(total, decimals) = DECIMAL(8,2) [OK]

Hint: DECIMAL(M,D): M=total digits, D=digits after decimal [OK]

Common Mistakes:

• Swapping M and D values
• Using DECIMAL(8) which lacks decimal precision

3. Given the table prices with a column cost FLOAT(7,2), what will be the result of inserting 12345.6789 and then selecting cost?

medium

A. 12345.679

B. 12345.68

C. 12345.67

D. 12345.000

Solution

1. Step 1: Understand FLOAT(7,2) meaning

   FLOAT(7,2) means 7 digits total, 2 after decimal, but FLOAT is approximate.

2. Step 2: Insert and round value

   12345.6789 rounds to 12345.68 when stored with 2 decimals.

3. Final Answer:

   12345.68 -> Option B

4. Quick Check:

   FLOAT rounds to 2 decimals = 12345.68 [OK]

Hint: FLOAT rounds approximate values to specified decimals [OK]

Common Mistakes:

• Expecting exact storage like DECIMAL
• Ignoring rounding behavior of FLOAT

4. You defined a column as FLOAT(5,2) but notice values like 1234.567 are stored as 1234.56. What is the likely cause and how to fix it?

medium

A. MySQL truncates decimals by default; enable rounding mode

B. FLOAT(5,2) syntax is invalid; use FLOAT without parameters

C. The value exceeds FLOAT range; use DOUBLE instead

D. FLOAT stores approximate values; use DECIMAL(7,3) for exact decimals

Solution

1. Step 1: Recognize FLOAT behavior

   FLOAT stores approximate values and rounds to specified decimals, causing truncation.

2. Step 2: Choose correct type for exact decimals

   DECIMAL stores exact decimals and preserves precision, so use DECIMAL(7,3).

3. Final Answer:

   FLOAT stores approximate values; use DECIMAL(7,3) for exact decimals -> Option D

4. Quick Check:

   Approximate FLOAT -> switch to DECIMAL for precision [OK]

Hint: Use DECIMAL for exact decimals, FLOAT is approximate [OK]

Common Mistakes:

• Thinking FLOAT(5,2) is invalid syntax
• Assuming DOUBLE fixes decimal truncation
• Expecting MySQL to round automatically without changing type

5. You need to store scientific measurements with very large or very small values and some decimal precision. Which MySQL data type is best and why?

hard

A. FLOAT or DOUBLE, because they store approximate floating-point numbers with wide range

B. DECIMAL, because it stores exact fixed-point numbers

C. VARCHAR, to store numbers as text for precision

D. INT, because it stores whole numbers efficiently

Solution

1. Step 1: Understand scientific measurement needs

   Scientific data often requires wide range and floating precision, not exact fixed decimals.

2. Step 2: Choose FLOAT or DOUBLE for wide range

   FLOAT and DOUBLE support very large/small values with approximate precision, ideal for science.

3. Final Answer:

   FLOAT or DOUBLE, because they store approximate floating-point numbers with wide range -> Option A

4. Quick Check:

   Wide range + approximate decimals = FLOAT/DOUBLE [OK]

Hint: Use FLOAT/DOUBLE for scientific data, DECIMAL for money [OK]

Common Mistakes:

• Choosing DECIMAL which limits range
• Using VARCHAR which wastes space and loses numeric features
• Using INT which cannot store decimals