🔢 MySQL Numeric Types – INT, FLOAT, DECIMAL, TINYINT, BOOLEAN, BIT Explained

---

🧲 Introduction – Why Numeric Data Types Matter in MySQL?

Numeric types in MySQL are used to store quantities, identifiers, prices, scores, flags, and measurements. Choosing the right type affects:

• ✅ Data accuracy
• ✅ Storage size
• ✅ Query performance
• ✅ Validation and precision

MySQL offers integer, fixed-point, and floating-point types, as well as binary and boolean-like types for flags and status checks.

🎯 In this guide, you’ll learn:

• The syntax, range, and purpose of each numeric type
• Differences between INT, FLOAT, and DECIMAL
• Special types like BOOLEAN, BIT, and TINYINT
• Real-world examples and best practices

---

🔢 1. Integer Types – Whole Numbers

🔹 Common Integer Types

Type	Storage	Signed Range	Unsigned Range
TINYINT	1 byte	-128 to 127	0 to 255
SMALLINT	2 bytes	-32,768 to 32,767	0 to 65,535
MEDIUMINT	3 bytes	-8.3M to 8.3M	0 to 16.7M
INT	4 bytes	-2.1B to 2.1B	0 to 4.2B
BIGINT	8 bytes	~±9 quintillion	0 to ~18 quintillion

🔹 Example

CREATE TABLE employees (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  age TINYINT,
  years_with_company SMALLINT
);

Explanation:

• id: Unique, positive integer (UNSIGNED) for employee ID.
• age: Small number, stored efficiently with TINYINT.
• years_with_company: Range of years, using SMALLINT.

---

🧮 2. Floating Point Types – Approximate Precision

Type	Storage	Precision	Use Case
FLOAT(p)	4 bytes	~7 digits (p ≤ 24)	Light-weight approximate values
DOUBLE(p)	8 bytes	~15-16 digits (p > 24)	High-range scientific values

🔹 Example

CREATE TABLE weather (
  temperature FLOAT,
  humidity DOUBLE
);

Explanation:

• temperature: Approximate float with 7-digit precision.
• humidity: Higher-precision value for scientific storage.

⚠️ Warning: Avoid for financial data—may cause rounding errors.

---

💰 3. DECIMAL(p, s) – Exact Precision (Fixed-Point)

🔹 Syntax

DECIMAL(p, s)

• p: Total number of digits.
• s: Digits after the decimal point.

🔹 Example

CREATE TABLE products (
  product_name VARCHAR(100),
  price DECIMAL(10, 2)
);

Explanation:

• price: Stores values like 99999999.99 with exact precision.
• ✅ Ideal for money, tax, billing, currency.

---

✅ 4. BOOLEAN – True/False Alias

CREATE TABLE flags (
  is_active BOOLEAN
);

Explanation:

• MySQL maps BOOLEAN to TINYINT(1).
• 0 = FALSE, 1 = TRUE.
• ✅ Useful for status flags and checkboxes.

---

🔹 Insert & Select Examples

INSERT INTO flags (is_active) VALUES (TRUE), (FALSE);
SELECT * FROM flags WHERE is_active = TRUE;

---

🧷 5. BIT(n) – Binary Flags

🔹 Syntax

BIT(n)  -- where n = number of bits (1–64)

🔹 Example

CREATE TABLE permissions (
  role_id INT,
  perms BIT(4)
);

Explanation:

• BIT(4) stores binary flags like 0001, 1110, etc.
• Efficient way to store multiple YES/NO flags in one column.

---

🔹 Insert Example

INSERT INTO permissions (role_id, perms) VALUES (1, b'1010');

Explanation:
Stores the permission mask 1010 (binary) for role 1.

---

📊 Numeric Types Comparison Table

Type	Exact?	Range / Size	Best For
INT	✅ Exact	±2B	IDs, counters
TINYINT	✅ Exact	±128	Age, flags, booleans
BIGINT	✅ Exact	±9 quintillion	Large keys, UUIDs
FLOAT	❌ Approx	~7 digits	Sensor, analytics
DOUBLE	❌ Approx	~15 digits	Scientific, large-scale measurements
DECIMAL	✅ Exact	Controlled by (p, s)	Currency, billing
BOOLEAN	✅ Alias	Internally TINYINT(1)	TRUE/FALSE values
BIT	✅ Exact	Binary 1–64 bits	Compact flag storage

---

📘 Best Practices

✅ Tip	💡 Why It Matters
Use DECIMAL for financial calculations	Avoids rounding errors from floating point math
Use smallest integer that fits your range	Saves storage and improves performance
Use UNSIGNED for IDs if never negative	Doubles positive range
Use BOOLEAN for true/false logic	Cleaner than raw TINYINT
Use BIT for compact, fast permission flags	Avoids extra columns or separate flag tables

---

🚀 Real-World Use Cases

Scenario	Type	Reason
Employee ID	INT UNSIGNED	Positive, auto-increment primary key
Product Price	DECIMAL(10, 2)	Exact pricing values
User Active Status	BOOLEAN	Simple on/off flag
Sensor Readings	FLOAT	Approximate with fast processing
Role Permissions	BIT(8)	Bitmask encoding for roles

---

📌 Summary – Recap & Next Steps

Numeric types in MySQL are fundamental to data accuracy, size efficiency, and logical correctness. Choosing the right one for each scenario helps avoid bugs, boosts performance, and keeps schemas optimized.

🔍 Key Takeaways

• Use INT/BIGINT for IDs and counters.
• Use DECIMAL for accurate currency or calculations.
• Avoid FLOAT/DOUBLE for precise math.
• Use BOOLEAN or TINYINT(1) for flags.
• Use BIT(n) for compact binary storage.

⚙️ Real-World Relevance

From billing apps to IoT platforms, smart numeric type design drives accuracy, performance, and clarity across systems.

---

❓ FAQ – MySQL Numeric Types

---

❓ When should I use DECIMAL instead of FLOAT?

✅ Use DECIMAL for money, billing, and finance where rounding is unacceptable.

---

❓ Can BOOLEAN store more than TRUE/FALSE?

❌ No. It’s an alias for TINYINT(1), and only 0 and 1 are valid in strict mode.

---

❓ Can I use BIT as a boolean?

✅ Yes, but it’s less readable than BOOLEAN or TINYINT(1).

---

❓ What’s the max value for an INT?

Signed: -2,147,483,648 to 2,147,483,647
Unsigned: 0 to 4,294,967,295

---

❓ Are FLOAT and DOUBLE accurate?

❌ No. They are approximate. Use only for scientific, sensor, or large-range data—not financials.

---

Share Now :