Learning Focus

Use this lesson to understand TINYINT with practical syntax and examples.

MySQL TINYINT Data Type: Complete Guide

What is TINYINT?

The TINYINT is the smallest integer data type in MySQL, storing whole numbers in just 1 byte (8 bits). It is ideal for storing small-range numeric values efficiently.

Key Features

Feature	Details
Storage Size	1 byte (8 bits)
Signed Range	-128 to 127
Unsigned Range	0 to 255
Synonyms	None (unique to MySQL)
Default Value	NULL (if not specified)

---

When to Use TINYINT?

[OK] Best for:

• Boolean values (0 = false, 1 = true)
• Status flags (e.g., 1=Active, 0=Inactive)
• Small counters (e.g., age, ratings out of 5)
• Enums with limited options

[X] Avoid for:

• Large numbers (use INT or BIGINT)
• Decimal numbers (use DECIMAL or FLOAT)

---

How to Define TINYINT

Basic Syntax

column_name TINYINT [OPTIONS]

Available Options

Option	Description	Example
UNSIGNED	Only allows 0 to 255 (no negatives)	TINYINT UNSIGNED
ZEROFILL	Pads with leading zeros (deprecated in MySQL 8.0+)	TINYINT(3) ZEROFILL -> 005
AUTO_INCREMENT	Rarely used (better for INT)	TINYINT AUTO_INCREMENT

---

Practical Examples

Example 1: Boolean Values (0/1)

CREATE TABLE tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    is_completed TINYINT(1) DEFAULT 0  -- 0 = false, 1 = true
);

• Used like a boolean (0=No, 1=Yes).

Example 2: Status Codes (Small Range)

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    account_status TINYINT UNSIGNED  -- 0=Inactive, 1=Active, 2=Suspended
);

• Stores small numeric codes efficiently.

Example 3: Age (Unsigned TINYINT)

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    age TINYINT UNSIGNED  -- Age can't be negative
);

• Ensures age is between 0 and 255.

---

TINYINT vs Other Integer Types

Type	Storage	Signed Range	Unsigned Range	Best For
TINYINT	1 byte	-128 to 127	0 to 255	Small numbers (status flags, booleans)
SMALLINT	2 bytes	-32K to 32K	0 to 65K	Medium-range numbers
INT	4 bytes	-2B to 2B	0 to 4B	Most common integer storage
BIGINT	8 bytes	-9Q to 9Q	0 to 18Q	Extremely large numbers

---

Common Issues & Best Practices

** Potential Problems**

1. Overflow Risk
   • Inserting 256 into TINYINT UNSIGNED causes an error.
   • Fix: Use SMALLINT if expecting larger numbers.
2. Boolean Confusion
   • MySQL has no native BOOLEAN type (it's just an alias for TINYINT(1)).
   • Best Practice: Use TINYINT(1) for true/false values.
3. Deprecated ZEROFILL
   • MySQL 8.0+ discourages ZEROFILL.
   • Alternative: Use LPAD() for zero-padding in queries.

[OK] Best Practices

Yes Use UNSIGNED if negative values aren't needed.

Yes Prefer TINYINT for status flags and booleans.

Yes Avoid AUTO_INCREMENT (better for INT).

---

Summary

• TINYINT is the smallest integer type (1 byte).
• Signed range: 128 to 127
• Unsigned range: 0 to 255
• Best for: Booleans, status codes, and small counters.

---

Next Topic Suggestion

• SMALLINT (2-byte integer)
• DECIMAL (fixed-point numbers)
• Or another section (e.g., SELECT Statement)?

Concept Map

flowchart LR
    A[Schema Context] --> B[TINYINT]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

CREATE TABLE sample_tinyint (id INT PRIMARY KEY, value TINYINT);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

• Previous: INT - Review the previous lesson to reinforce context.
• Next: SMALLINT - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.