SQL Data Types — Numeric, String, Date, Boolean, and Binary Explained

Introduction

Data types define the kind of value a column can hold in a database table. Choosing the right data type is one of the most important decisions when designing a database schema — it affects storage efficiency, query performance, data integrity, and the operations you can perform on the data. This tutorial covers every major SQL data type category in MySQL: numeric, string, date/time, boolean, and binary types, along with practical guidance on how to choose the correct type for your needs.

Table of Contents

1. What Are Data Types?
2. Numeric Data Types
   • Exact Numeric Types (INT, DECIMAL, SMALLINT, etc.)
   • Approximate Numeric Types (FLOAT, DOUBLE)
   • AUTO_INCREMENT
3. String Data Types
   • Character Types (CHAR, VARCHAR, TEXT)
   • ENUM Type
   • Large Object Types (TEXT, MEDIUMTEXT, LONGTEXT)
4. Date & Time Data Types
   • DATE, TIME, DATETIME, TIMESTAMP, YEAR
5. Boolean Data Types
6. Binary Data Types
   • BINARY, VARBINARY, BLOB (TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB)
7. JSON Data Type
8. Choosing the Correct Data Type
   • Storage Considerations
   • Performance Considerations
   • Data Integrity Considerations
9. Type Conversion — CAST and CONVERT
10. Best Practices

1. What Are Data Types?

A data type is an attribute that specifies what kind of data a column can store. Every column in a MySQL table must have a data type, and the database engine enforces that only valid data of that type can be inserted or updated.

Why data types matter:

• Storage efficiency — Smaller types use less disk space and memory.
• Data integrity — Prevents invalid data (e.g., storing text in a numeric column).
• Performance — Properly typed columns allow faster comparisons, indexing, and sorting.
• Constraint enforcement — Range checks, precision rules, and format validation.

Note: This tutorial uses MySQL syntax. While the core concepts apply to most SQL databases, MySQL-specific types and behaviors are noted throughout.

2. Numeric Data Types

Numeric types store numbers — both whole numbers (integers) and numbers with decimal places.

2.1 Exact Numeric Types

Exact numeric types store values with exact precision — no rounding errors.

Key MySQL detail: All integer types can take UNSIGNED attribute, doubling the upper range by disabling negative values. Always use UNSIGNED for IDs, ages, and counts.

Example — DECIMAL precision:

CREATE TABLE products (
    product_id INT UNSIGNED PRIMARY KEY,
    price DECIMAL(10, 2),        -- 10 total digits, 2 after decimal point
    tax_rate DECIMAL(5, 4)       -- 5 total digits, 4 after decimal point
);

DECIMAL(10, 2) can store values like 12345678.99 — 8 digits before the decimal and 2 after.

When to use DECIMAL:

• Always use DECIMAL for financial / monetary data. Floating-point rounding errors are unacceptable with money.

2.2 Approximate Numeric Types

Approximate numeric types store values using floating-point representation — they can represent a wide range of values but may introduce small rounding errors.

Example:

CREATE TABLE measurements (
    sensor_id INT UNSIGNED,
    temperature FLOAT(7),     -- ~6 decimal digits of precision
    humidity DOUBLE            -- ~15 decimal digits of precision
);

When to use FLOAT/DOUBLE:

• Scientific calculations where slight rounding is acceptable.
• Very large or very small numbers (e.g., 1.23 × 10⁻¹⁰⁰).
• Never for monetary or financial values.

MySQL note: FLOAT(p) precision affects storage. p from 0 to 23 → 4 bytes, 24 to 53 → 8 bytes.

2.3 AUTO_INCREMENT

MySQL provides AUTO_INCREMENT for automatically generating sequential integer values — commonly used for primary keys.

CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

INSERT INTO users (username) VALUES ('alice'), ('bob');
-- user_id automatically gets 1, 2, 3...

-- Check the last inserted ID
SELECT LAST_INSERT_ID();

Key MySQL AUTO_INCREMENT rules:

• Only one AUTO_INCREMENT column per table.
• Must be defined as a key (usually PRIMARY KEY).
• Must be an integer type (TINYINT, SMALLINT, INT, BIGINT).
• Starting value can be changed: ALTER TABLE users AUTO_INCREMENT = 1000;

3. String Data Types

String types store sequences of characters — everything from short names to large documents.

3.1 Character Types

CHAR vs VARCHAR — the key difference:

CREATE TABLE example (
    country_code CHAR(2),      -- Always 2 characters (e.g., 'US', 'IN')
    title VARCHAR(255)         -- Variable length up to 255
);

• CHAR(10) always stores 10 characters — shorter values are right-padded with spaces.
• VARCHAR(10) stores only the actual string plus 1-2 bytes of length overhead.

When to use CHAR:

• Fixed-length codes (country codes, ISBNs, hashes).
• Values that are always the same length.

When to use VARCHAR:

• Variable-length text (names, emails, descriptions).
• Most general-purpose string storage.

-- Insert examples
INSERT INTO example (country_code, title) VALUES ('IN', 'SQL Tutorial');
-- country_code becomes 'IN' (padded with spaces to 2 chars)
-- title stores 'SQL Tutorial' (14 characters)

MySQL note: VARCHAR values may be automatically converted to TEXT if the defined length exceeds the row-size limit (65,535 bytes total across all columns).

3.2 ENUM Type

ENUM is a MySQL-specific string type that restricts a column to a predefined set of values.

CREATE TABLE orders (
    order_id INT UNSIGNED PRIMARY KEY,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
);

INSERT INTO orders (order_id, status) VALUES (1, 'pending');
INSERT INTO orders (order_id, status) VALUES (2, 'shipped');
-- INSERT INTO orders (order_id, status) VALUES (3, 'unknown');
-- ERROR: Data truncated for column 'status'

ENUM advantages:

• Compact storage (1-2 bytes depending on number of values).
• Built-in validation — invalid values are rejected automatically.
• Queries are more readable than integer codes.

-- Sorting is by the order values were defined, not alphabetically
SELECT * FROM orders ORDER BY status;
-- pending → processing → shipped → delivered → cancelled

Important: Adding new values requires an ALTER TABLE statement. For frequently changing value lists, use a lookup table with a foreign key instead.

3.3 Large Object Types

For storing very large text data:

CREATE TABLE articles (
    article_id INT UNSIGNED PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,                          -- Main article content
    change_log MEDIUMTEXT               -- Optional: full revision history
);

MySQL note: TEXT columns cannot have default values and require a prefix length for indexing (e.g., INDEX (body(100))).

4. Date & Time Data Types

Date/time types store temporal data — dates, times, timestamps, and years.

CREATE TABLE events (
    event_id INT UNSIGNED PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,                    -- e.g., '2026-07-29'
    start_time TIME,                    -- e.g., '09:00:00'
    created_at DATETIME,                -- e.g., '2026-07-01 10:30:00'
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

MySQL detail: TIMESTAMP has a limited range (up to 2038 — the "Year 2038 Problem"). For dates beyond 2038, use DATETIME.

DATETIME vs TIMESTAMP — the critical difference

Working with MySQL date/time functions

-- Current date and time
SELECT CURRENT_DATE;                    -- 2026-07-29
SELECT CURRENT_TIMESTAMP;               -- 2026-07-29 14:30:00
SELECT NOW();                           -- 2026-07-29 14:30:00

-- Date arithmetic
SELECT DATE_ADD('2026-07-29', INTERVAL 7 DAY);       -- 2026-08-05
SELECT DATEDIFF('2026-12-31', '2026-07-29');          -- 155 (days)
SELECT DATE_SUB('2026-07-29', INTERVAL 1 MONTH);      -- 2026-06-29

-- Extract parts
SELECT EXTRACT(YEAR FROM '2026-07-29');               -- 2026
SELECT MONTH('2026-07-29');                           -- 7
SELECT DAY('2026-07-29');                             -- 29

-- Formatting
SELECT DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d');         -- 2026-07-29
SELECT DATE_FORMAT(CURRENT_DATE, '%W, %M %d, %Y');   -- Wednesday, July 29, 2026
SELECT TIME_FORMAT('14:30:00', '%h:%i %p');           -- 02:30 PM

-- Last day of month
SELECT LAST_DAY('2026-07-29');                        -- 2026-07-31

Best practice: Always store timestamps in UTC in your database and convert to local time in your application layer. Set MySQL's timezone with SET time_zone = '+00:00';

5. Boolean Data Types

MySQL does not have a native BOOLEAN type. BOOL and BOOLEAN are aliases for TINYINT(1) — values are stored as 0 (false) or 1 (true).

CREATE TABLE tasks (
    task_id INT UNSIGNED PRIMARY KEY,
    task_name VARCHAR(100),
    is_completed BOOL DEFAULT FALSE      -- Actually TINYINT(1)
);

INSERT INTO tasks (task_name, is_completed) VALUES
    ('Learn SQL', TRUE),                 -- TRUE = 1
    ('Build a project', FALSE),          -- FALSE = 0
    ('Review code', NULL);               -- NULL means "unknown / not yet set"

Querying boolean columns:

-- These are equivalent in MySQL
SELECT * FROM tasks WHERE is_completed = TRUE;
SELECT * FROM tasks WHERE is_completed = 1;

-- Check for NULL
SELECT * FROM tasks WHERE is_completed IS NULL;    -- NOT: is_completed = NULL

MySQL note: Because BOOLEAN is just TINYINT(1), you can technically insert values other than 0 and 1. Add a CHECK constraint if you need strict enforcement (MySQL 8.0.16+):

is_completed TINYINT(1) DEFAULT 0 CHECK (is_completed IN (0, 1))

6. Binary Data Types

Binary types store raw byte data — images, files, encrypted data, serialized objects.

BLOB stands for Binary Large Object. TEXT and BLOB variants are identical except TEXT stores characters and BLOB stores bytes.

CREATE TABLE files (
    file_id INT UNSIGNED PRIMARY KEY,
    file_name VARCHAR(255),
    file_data BLOB,                      -- The raw file content
    thumbnail BINARY(1024),              -- Fixed-size thumbnail (1024 bytes)
    metadata VARBINARY(500)              -- Variable metadata up to 500 bytes
);

When to store binary data in the database:

• Small files (icons, thumbnails, avatars under 64 KB).
• Files where transactional consistency matters.
• Encrypted data that must never leave the database.
• Audit records and compliance requirements.

When NOT to store binary data in the database:

• Large media files (videos, high-res images) — use object storage (S3, etc.).
• Frequently accessed files that would bloat InnoDB's buffer pool.
• Files that need CDN delivery.

MySQL note: BLOB columns can significantly slow down SELECT queries because blob data isn't always kept in memory. Consider storing file paths instead of file contents for larger files.

7. JSON Data Type

MySQL 5.7+ supports a native JSON data type that stores JSON documents in an efficient binary format.

CREATE TABLE users (
    user_id INT UNSIGNED PRIMARY KEY,
    username VARCHAR(50),
    preferences JSON                      -- Stores JSON objects / arrays
);

INSERT INTO users (username, preferences) VALUES
    ('alice', '{"theme": "dark", "notifications": true, "language": "en"}'),
    ('bob', '{"theme": "light", "language": "fr"}');

Querying JSON data

-- Extract a single field
SELECT 
    username,
    JSON_UNQUOTE(JSON_EXTRACT(preferences, '$.theme')) AS theme
FROM users;

-- Shorthand using -> and ->>
SELECT username, preferences->>'$.theme' AS theme FROM users;

-- Query inside JSON
SELECT * FROM users 
WHERE JSON_EXTRACT(preferences, '$.language') = '"en"';

-- Simpler with ->> operator
SELECT * FROM users WHERE preferences->>'$.language' = 'en';

-- Check if a path exists
SELECT * FROM users WHERE JSON_CONTAINS_PATH(preferences, 'one', '$.notifications');

Modifying JSON

-- Update a single JSON key
UPDATE users 
SET preferences = JSON_SET(preferences, '$.theme', 'light')
WHERE username = 'alice';

-- Remove a key
UPDATE users 
SET preferences = JSON_REMOVE(preferences, '$.notifications')
WHERE username = 'alice';

When to use JSON:

• Flexible / evolving schemas (user preferences, settings).
• Storing data from external APIs as-is.
• Prototyping before normalizing into separate columns.

When NOT to use JSON:

• Data that's queried frequently — JSON fields cannot be indexed as efficiently as regular columns (though MySQL supports generated columns + virtual indexes on JSON).
• Relational data that belongs in normalized tables.
• Large text content — use TEXT or MEDIUMTEXT instead.

MySQL note: JSON columns are stored internally in a binary format (JSONB-like) that allows fast lookups without reparsing the string. They are automatically validated when data is inserted.

8. Choosing the Correct Data Type

This is the most practical skill in schema design. Here are the decision criteria:

8.1 Storage Considerations

Choose the smallest type that fits your data.

-- BAD: Overly large types
CREATE TABLE users (
    age INT,                   -- Only stores 0-150, needs only TINYINT UNSIGNED
    country_code VARCHAR(255), -- Country codes are always 2-3 chars, needs CHAR(3)
    is_active VARCHAR(5)       -- Boolean values, needs TINYINT(1) / BOOL
);

-- GOOD: Appropriate types
CREATE TABLE users (
    age TINYINT UNSIGNED,       -- 0-255 range is enough
    country_code CHAR(3),       -- Exactly 2-3 characters
    is_active BOOL              -- TRUE or FALSE (TINYINT(1))
);

Storage size comparison (MySQL):

* VARCHAR only stores the actual data length, so an overestimated maximum doesn't waste space for individual rows — but it does affect MySQL's memory allocation in temporary tables.

8.2 Performance Considerations

Example — choosing a primary key type:

-- BAD: String primary key (slower joins, more index size)
CREATE TABLE orders (
    order_code VARCHAR(50) PRIMARY KEY,  -- e.g., 'ORD-2026-07-29-001'
    ...
);

-- GOOD: Integer surrogate key (AUTO_INCREMENT)
CREATE TABLE orders (
    order_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    order_code VARCHAR(50) UNIQUE,       -- Still unique, but not the PK
    ...
);

8.3 Data Integrity Considerations

The phone number trap:

-- BAD: Integer phone number -- loses leading zeros, can't store dashes/+
CREATE TABLE contacts (
    phone INT                           -- 0212345678 becomes 212345678
);

-- GOOD: String phone number
CREATE TABLE contacts (
    phone VARCHAR(20)                   -- '+1 (555) 123-4567' stays as-is
);

9. Type Conversion — CAST and CONVERT

MySQL automatically converts between compatible types (implicit conversion), but sometimes you need explicit conversion to control the result.

CAST

CAST(expression AS type)

-- Examples
SELECT CAST('2026-07-29' AS DATE);              -- String → Date
SELECT CAST('123' AS UNSIGNED);                  -- String → Integer
SELECT CAST(price AS DECIMAL(10, 2)) FROM products;
SELECT CAST(12345 AS CHAR(10));                  -- Integer → String

CONVERT

CONVERT(expression, type)
-- or with USING for character sets
CONVERT(expression USING charset)

-- Examples
SELECT CONVERT('2026-07-29', DATE);              -- Same as CAST
SELECT CONVERT('Hello' USING utf8mb4);            -- Convert character set

Common conversion scenarios

-- String to number (automatic in arithmetic)
SELECT '10' + '20';                              -- 30 (implicit conversion)

-- Explicit type safety
SELECT CAST('10' AS UNSIGNED) + CAST('20' AS UNSIGNED);  -- 30

-- Date to string
SELECT CAST(CURRENT_DATE AS CHAR(10));           -- '2026-07-29'

-- Number to formatted string
SELECT CONCAT('Order #', CAST(order_id AS CHAR(10))) FROM orders;

-- Prevent truncation warnings
INSERT INTO products (price) VALUES (CAST('99.99' AS DECIMAL(10, 2)));

Warning: Implicit conversion can cause performance issues in WHERE clauses — it prevents index usage. Always match data types to avoid it. For example, WHERE user_id = '1' (string) is worse than WHERE user_id = 1 (integer) if user_id is INT.

10. Best Practices

1. Always use UNSIGNED for integer columns that can never be negative — IDs, ages, counts, foreign keys. This doubles your range for free.

2. Use DECIMAL for money, FLOAT/DOUBLE for measurements — Never swap them. Rounding errors in financial data are unacceptable.

3. Use TIMESTAMP with DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP for automatic tracking of row creation and modification times.

4. Use VARCHAR with a reasonable max, not TEXT, for short-to-medium strings. TEXT columns cannot have default values and are more complex to index.

5. Use CHAR only for truly fixed-length values — country codes, hashes, fixed codes. Ensure they are always the same length.

6. Use BOOL / TINYINT(1) for binary flags — it's compact and clear.

7. Store file paths, not file contents in the database for large binary files. Keep the actual files on the filesystem or object storage.

8. Use ENUM for small, stable value lists — but prefer a lookup table if the list changes frequently.

9. Use JSON sparingly — great for flexible schemas and settings, but not a replacement for normalized relational tables.

10. Choose DATE over DATETIME when you only need the date — saves space and documents intent.

11. Be consistent — don't use INT UNSIGNED for one foreign key and BIGINT UNSIGNED for another in the same schema.

12. Match data types in JOINs — joining INT to VARCHAR forces MySQL to convert every row and breaks index usage.