Chapter 5:Data Types
Choosing the right data type for each column is like choosing the right size of box when packing things for a move:
- Too small → your data gets cut off or you get errors
- Too big → you waste space and your database becomes slow and expensive
- Wrong type → your queries behave strangely or you can’t do math/sort correctly
Today we’re going to learn all the major data types that you’ll use 95% of the time, explained like we’re sitting together and I’m showing you real examples from our bookshop database.
We’ll cover four big groups:
- Numeric types (numbers: whole, decimal, money)
- String types (text: short, long, fixed)
- Date and time types (when things happen)
- Boolean and other useful types
Let’s dive in!
1. Numeric Types – Storing Numbers
| Data Type | What it stores | Range / Size | When to use it | Example Declaration |
|---|---|---|---|---|
| TINYINT | Very small whole numbers | -128 to 127 (or 0–255 if UNSIGNED) | Status flags (0=inactive, 1=active) | status TINYINT UNSIGNED |
| SMALLINT | Small whole numbers | -32,768 to 32,767 | Age, number of items in stock (small) | age SMALLINT |
| MEDIUMINT | Medium whole numbers | -8 million to 8 million | Rarely used | — |
| INT / INTEGER | Standard whole numbers | -2 billion to 2 billion (or 0–4 billion UNSIGNED) | IDs, quantities, years, most common number | book_id INT UNSIGNED AUTO_INCREMENT |
| BIGINT | Very large whole numbers | -9 quintillion to 9 quintillion | Very big IDs, counters, financial transactions | transaction_id BIGINT UNSIGNED |
| FLOAT | Floating-point (approximate decimals) | ~6–7 decimal digits accuracy | Scientific calculations, percentages | temperature FLOAT |
| DOUBLE | Double-precision float | ~15 decimal digits accuracy | When you need more precision than FLOAT | gps_latitude DOUBLE |
| DECIMAL / NUMERIC | Exact decimal numbers (fixed-point) | You specify precision & scale | Money, prices, financial data – NEVER use FLOAT for money! | price DECIMAL(10,2) |
Important notes on DECIMAL:
- DECIMAL(10,2) means:
- Total digits = 10
- 2 after decimal point → Max value: 99,999,999.99 → Perfect for ₹ prices in India
Real example – books table:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE books ( book_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, stock_quantity SMALLINT UNSIGNED DEFAULT 0, -- up to 65,535 books in stock pages MEDIUMINT UNSIGNED, -- up to 16 million pages (rare!) price DECIMAL(10,2) NOT NULL DEFAULT 0.00, -- ₹0.00 to ₹99,999,999.99 rating DECIMAL(3,2) DEFAULT 0.00, -- 0.00 to 9.99 discount DECIMAL(5,2) DEFAULT 0.00 -- e.g., 25.00 for 25% ); |
Never do this for money:
SQL
|
0 1 2 3 4 5 6 |
price FLOAT -- BAD! 9.99 might become 9.989999999 or other rounding errors |
2. String / Text Types
| Data Type | What it stores | Max length | Storage method | Best use case |
|---|---|---|---|---|
| CHAR(n) | Fixed-length strings | Up to 255 characters | Always uses n bytes | Fixed codes: PIN, ISBN, PAN, gender ‘M’/’F’ |
| VARCHAR(n) | Variable-length strings | Up to 65,535 (usually 255–5000) | Only uses space needed + 1–2 bytes | Names, titles, emails, addresses |
| TEXT | Very long text | Up to 65,535 bytes (64KB) | Outside main table (slower) | Blog posts, descriptions |
| MEDIUMTEXT | Even longer | Up to 16MB | Outside main table | Articles, long comments |
| LONGTEXT | Extremely long | Up to 4GB | Outside main table | Books, legal documents |
Real example – books table:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE books ( isbn CHAR(13) UNIQUE, -- ISBN is always exactly 13 characters title VARCHAR(255) NOT NULL, -- Most book titles < 255 chars author VARCHAR(150) NOT NULL, description TEXT, -- Long book summary publisher VARCHAR(100), language CHAR(2) DEFAULT 'EN' -- 'EN', 'HI', 'MR', 'TA', etc. ); |
Pro Tip (2026 best practice):
- Use VARCHAR(255) for most names, titles, emails
- Use VARCHAR(500) or VARCHAR(1000) for longer fields like addresses or short descriptions
- Only use TEXT when you expect > 1000–2000 characters
3. Date and Time Types
| Data Type | What it stores | Format / Range | Best use case |
|---|---|---|---|
| DATE | Just the date (no time) | ‘YYYY-MM-DD’ (1000-01-01 to 9999-12-31) | Birthdays, publication dates |
| DATETIME | Date + time | ‘YYYY-MM-DD HH:MM:SS’ | Order placed time, event start time |
| TIMESTAMP | Date + time (with timezone awareness) | Same as DATETIME, but stored in UTC | Created/updated timestamps (auto-updates) |
| TIME | Just time of day | ‘-838:59:59’ to ‘838:59:59’ | Duration, opening hours |
| YEAR | Just the year | 1901–2155 | Rarely used (publication year better as INT) |
Modern best practice (2026):
- Use TIMESTAMP for almost everything that needs automatic created/updated tracking
- Use DATETIME when you want to store local time without timezone conversion
Real example:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE orders ( order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_date DATE NOT NULL, -- When the order was placed (date only) order_datetime DATETIME NOT NULL, -- Exact time customer clicked "Place Order" created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); |
Handy functions you’ll use a lot:
SQL
|
0 1 2 3 4 5 6 7 8 9 |
NOW() -- Current date + time CURRENT_DATE -- Just today’s date CURRENT_TIME -- Just current time DATE_FORMAT() -- Format dates nicely |
4. Boolean and Other Useful Types
| Data Type | What it stores | Possible values | Notes / Best use |
|---|---|---|---|
| BOOLEAN | True / False | TRUE / FALSE (or 1 / 0) | MySQL stores as TINYINT(1) |
| ENUM | Predefined list of values | You define: ‘Small’,’Medium’,’Large’ | Status: ‘pending’, ‘shipped’, ‘delivered’ |
| SET | Multiple values from a list | Can store multiple choices | Tags: ‘fiction’,’mystery’,’bestseller’ |
| JSON | Structured JSON data | Full JSON documents | Flexible data (very popular in 2026!) |
Real examples:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE customers ( customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, is_active BOOLEAN DEFAULT TRUE, -- 1 = active, 0 = inactive account_type ENUM('regular', 'premium', 'vip') DEFAULT 'regular', tags SET('loyal', 'student', 'corporate'), -- can be multiple preferences JSON -- e.g., {"theme": "dark", "newsletter": true} ); |
JSON example insert:
SQL
|
0 1 2 3 4 5 6 7 |
INSERT INTO customers (name, preferences) VALUES ('Raj', '{"preferred_language": "Marathi", "wishlist": ["book123", "book456"]}'); |
Quick Summary – Most Common Choices in 2026
| Purpose | Recommended Type |
|---|---|
| Primary Key / ID | INT UNSIGNED or BIGINT UNSIGNED |
| Price / Money | DECIMAL(12,2) or DECIMAL(15,2) |
| Name, Title, Email | VARCHAR(100–255) |
| Long text (description) | TEXT or MEDIUMTEXT |
| Publication / Birth date | DATE |
| Exact order time | DATETIME or TIMESTAMP |
| Auto created/updated | TIMESTAMP with defaults |
| Yes/No flag | BOOLEAN or TINYINT(1) |
| Status (few options) | ENUM |
| Flexible data | JSON |
