SMALLINT

Learning Focus

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

Example of SMALLINT

Certainly! Here's an example of a table using SMALLINT with sample data, presented in a table format:

Table: product_inventory

product_id (SMALLINT)	product_name	quantity_in_stock (SMALLINT)	reorder_level (SMALLINT)
1	Laptop	50	10
2	Smartphone	200	20
3	Printer	15	5
4	Monitor	75	10
5	Keyboard	150	30

SQL to create this table:

CREATE TABLE product_inventory (
    product_id SMALLINT PRIMARY KEY,
    product_name VARCHAR(50),
    quantity_in_stock SMALLINT,
    reorder_level SMALLINT
);

SQL to insert the sample data:

INSERT INTO product_inventory (product_id, product_name, quantity_in_stock, reorder_level) VALUES
(1, 'Laptop', 50, 10),
(2, 'Smartphone', 200, 20),
(3, 'Printer', 15, 5),
(4, 'Monitor', 75, 10),
(5, 'Keyboard', 150, 30);

MySQL SMALLINT Data Type: Complete Guide

What is SMALLINT?

The SMALLINT is a 2-byte (16-bit) integer data type in MySQL, designed for storing medium-range whole numbers more efficiently than INT but with a larger range than TINYINT.

Key Features

Feature	Details
Storage Size	2 bytes (16 bits)
Signed Range	-32,768 to 32,767
Unsigned Range	0 to 65,535
Synonyms	None (unique to MySQL)
Default Value	NULL (if not specified)

---

When to Use SMALLINT?

[OK] Best for:

• Medium-range numbers (e.g., product quantities, exam scores)
• Foreign keys where values won't exceed 65K
• Statistical data (e.g., daily user counts)

[X] Avoid for:

• Very small numbers (use TINYINT for 1-byte storage)
• Large numbers (use INT or BIGINT)
• Decimal numbers (use DECIMAL or FLOAT)

---

How to Define SMALLINT

Basic Syntax

column_name SMALLINT [OPTIONS]

Available Options

Option	Description	Example
UNSIGNED	Only allows 0 to 65,535 (no negatives)	SMALLINT UNSIGNED
ZEROFILL	Pads with leading zeros (deprecated in MySQL 8.0+)	SMALLINT(5) ZEROFILL -> 00327
AUTO_INCREMENT	Rarely used (better for INT)	SMALLINT AUTO_INCREMENT

---

Practical Examples

Example 1: Storing Exam Scores

CREATE TABLE exam_results (
    student_id INT PRIMARY KEY,
    score SMALLINT UNSIGNED  -- Scores can't be negative or > 65,535
);

• Ensures scores are between 0 and 65,535.

Example 2: Product Inventory

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    stock_quantity SMALLINT UNSIGNED DEFAULT 0
);

• Efficiently tracks stock quantities (assuming < 65K items).

Example 3: Status Codes (Extended Range)

CREATE TABLE server_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    error_code SMALLINT  -- Supports negative codes if needed
);

• Handles wider numeric codes than TINYINT.

---

SMALLINT vs Other Integer Types

Type	Storage	Signed Range	Unsigned Range	Best For
TINYINT	1 byte	-128 to 127	0 to 255	Booleans, tiny counters
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 65,536 into SMALLINT UNSIGNED causes an error.
   • Fix: Use INT if expecting larger numbers.
2. Unnecessary Use
   • If values rarely exceed 255, TINYINT saves space.
   • Best Practice: Analyze data range before choosing.
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 SMALLINT over INT for known medium-range data.

Yes Avoid AUTO_INCREMENT (better for INT).

---

Summary

• SMALLINT is a 2-byte integer for medium-range numbers.
• Signed range: 32,768 to 32,767
• Unsigned range: 0 to 65,535
• Best for: Counters, quantities, and codes with predictable ranges.

---

Next Topic Suggestion

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

Concept Map

flowchart LR
    A[Schema Context] --> B[SMALLINT]
    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_smallint (id INT PRIMARY KEY, value SMALLINT);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

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