Data Types Deep Dive

Why Data Types Matter

Choosing the correct data type ensures:

Data integrity — prevents invalid data entry
Storage efficiency — uses minimal disk/memory space
Query performance — comparisons and indexes work faster
Correct operations — math works on numbers, pattern matching on strings

💡 Using wrong data types is a common source of bugs. Storing dates as strings, for example, breaks sorting and date arithmetic.

Numeric Types

Integer Types

Type	Storage	Range
`TINYINT`	1 byte	-128 to 127
`SMALLINT`	2 bytes	-32,768 to 32,767
`MEDIUMINT`	3 bytes	-8,388,608 to 8,388,607
`INT`	4 bytes	-2.1 billion to 2.1 billion
`BIGINT`	8 bytes	±9.2 quintillion

CREATE TABLE measurements (
    small_count SMALLINT,    -- Sensor readings
    population BIGINT,       -- Country population
    flag TINYINT             -- Boolean-like (0 or 1)
);

Decimal Types

Type	Description	Example
`DECIMAL(p,s)`	Exact precision	`DECIMAL(10,2)` → 12345678.90
`NUMERIC(p,s)`	Same as DECIMAL	`NUMERIC(5,3)` → 12.345
`FLOAT`	Approximate (4 bytes)	3.14159
`DOUBLE`	Approximate (8 bytes)	More precise than FLOAT

CREATE TABLE financial_data (
    price DECIMAL(10,2),     -- Exact: $99999999.99
    interest_rate FLOAT,     -- Approximate: 4.5%
    precise_amount NUMERIC(15,6)  -- 6 decimal places
);

⚠️ Never use FLOAT/DOUBLE for financial data. Floating-point rounding errors cause accounting discrepancies. Always use DECIMAL for money.

String Types

Type	Description	Max Length
`CHAR(n)`	Fixed-length string	255 bytes
`VARCHAR(n)`	Variable-length string	65,535 bytes
`TEXT`	Long text	65,535 bytes
`MEDIUMTEXT`	Medium text	16 MB
`LONGTEXT`	Long text	4 GB
`ENUM`	Predefined list	One of the listed values

CREATE TABLE products (
    code CHAR(5),           -- Fixed: 'ABC12'
    name VARCHAR(100),      -- Variable: 'Wireless Mouse'
    description TEXT,       -- Long: product details
    category ENUM('Electronics', 'Clothing', 'Food')  -- Restricted
);

CHAR vs VARCHAR

Feature	CHAR(10)	VARCHAR(10)
Storage	Always 10 bytes	1-10 bytes + 1-2 overhead
Performance	Faster (fixed size)	Slightly slower
Best For	Fixed-length codes (ZIP, state)	Variable-length names, descriptions

Date and Time Types

Type	Format	Example
`DATE`	YYYY-MM-DD	'2024-01-15'
`TIME`	HH:MM:SS	'14:30:00'
`DATETIME`	YYYY-MM-DD HH:MM:SS	'2024-01-15 14:30:00'
`TIMESTAMP`	Auto-updates, UTC	'2024-01-15 19:30:00'
`YEAR`	YYYY	2024

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,
    event_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Choosing the Right Type

Data	Recommended Type	Why
Person's age	`TINYINT`	0-150 fits in 1 byte
Product price	`DECIMAL(10,2)`	Exact money values
Email address	`VARCHAR(255)`	Variable length, max 255 typical
Country name	`VARCHAR(100)`	Variable length
Boolean flag	`BOOLEAN` or `TINYINT`	True/false or 0/1
Timestamp	`TIMESTAMP`	Auto-updates, timezone-aware
ZIP code	`CHAR(5)` or `VARCHAR(10)`	Fixed/variable length
UUID	`CHAR(36)` or `BINARY(16)`	Fixed or binary format

Type Conversion

-- Explicit conversion
SELECT CAST('123' AS INT);
SELECT CONVERT('2024-01-15', DATE);

-- Implicit conversion (avoid in production)
SELECT * FROM orders WHERE order_id = '123';  -- String compared to INT

⚠️ Implicit type conversions can cause unexpected results and prevent index usage. Always use matching types in comparisons.

✏️ Exercise: Design a table for a library with appropriate data types: book_id, title, author, isbn, page_count, price, published_date, is_available.

See Solution

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200),
    author VARCHAR(100),
    isbn CHAR(13),
    page_count SMALLINT,
    price DECIMAL(8,2),
    published_date DATE,
    is_available BOOLEAN
);

✅ Key Takeaways

Choose data types based on the actual data, not convenience
Use DECIMAL for financial data — never FLOAT/DOUBLE
VARCHAR is preferred over CHAR for variable-length data
TIMESTAMP auto-updates and is timezone-aware; DATETIME does not
Implicit type conversions can break queries — always match types explicitly