MySQL Data Types INT, VARCHAR, DATE, JSON

Numeric Data Types

MySQL provides a full range of integer and floating-point types. Choose the smallest type that fits your data to save storage and improve performance.

Type	Storage	Range (Signed)	Use Case
TINYINT	1 byte	-128 to 127	Flags, boolean-like values
SMALLINT	2 bytes	-32,768 to 32,767	Small counters
MEDIUMINT	3 bytes	-8,388,608 to 8,388,607	Medium-range IDs
INT	4 bytes	-2,147,483,648 to 2,147,483,647	General-purpose IDs
BIGINT	8 bytes	-9.2 × 10^18 to 9.2 × 10^18	Large IDs, timestamps
FLOAT	4 bytes	~7 decimal digits precision	Approximate decimals
DOUBLE	8 bytes	~15 decimal digits precision	Scientific calculations
DECIMAL(M,D)	Variable	Exact precision	Money, financial data

Numeric Column Definitions

CREATE table products (
    product_id   INT            UNSIGNED NOT NULL AUTO_INCREMENT,
    stock        SMALLINT       UNSIGNED NOT NULL DEFAULT 0,
    weight_kg    FLOAT          NOT NULL,
    rating       DECIMAL(3, 2)  NOT NULL DEFAULT 0.00,  -- e.g. 4.75
    price        DECIMAL(10, 2) NOT NULL,               -- e.g. 1999.99
    is_active    TINYINT(1)     NOT NULL DEFAULT 1,     -- boolean flag
    view_count   BIGINT         UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (product_id)
);

String Data Types

String types store text data. The key distinction is between fixed-length (CHAR) and variable-length (VARCHAR) types, and between inline storage and large object storage (TEXT family).

Type	Max Length	Use Case
CHAR(n)	255 chars	Fixed-length strings (country codes, hashes)
VARCHAR(n)	65,535 bytes	Variable-length strings (names, emails, URLs)
TINYTEXT	255 bytes	Very short text
TEXT	65,535 bytes	Articles, descriptions
MEDIUMTEXT	16 MB	Blog posts, HTML content
LONGTEXT	4 GB	Very large documents
ENUM	65,535 members	Fixed set of string values
SET	64 members	Multiple values from a fixed set

String Column Definitions

CREATE table customers (
    customer_id  INT          UNSIGNED NOT NULL AUTO_INCREMENT,
    first_name   VARCHAR(50)  NOT NULL,
    last_name    VARCHAR(50)  NOT NULL,
    email        VARCHAR(255) NOT NULL,
    country_code CHAR(2)      NOT NULL DEFAULT 'US',  -- fixed 2 chars
    status       ENUM('active', 'inactive', 'banned') NOT NULL DEFAULT 'active',
    preferences  SET('newsletter', 'sms', 'push')     NOT NULL DEFAULT '',
    bio          TEXT,
    password_hash CHAR(64)    NOT NULL,               -- SHA-256 hex = 64 chars
    PRIMARY KEY (customer_id)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Date and Time Data Types

Type	Format	Range	Use Case
DATE	YYYY-MM-DD	1000-01-01 to 9999-12-31	Birthdates, deadlines
TIME	HH:MM:SS	-838:59:59 to 838:59:59	Durations, schedules
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 to 9999-12-31	Event timestamps
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 to 2038-01-19	Row created/updated times
YEAR	YYYY	1901 to 2155	Year-only values

Date/Time, Binary, and JSON Column Definitions

CREATE table orders (
    order_id     INT       UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id  INT       UNSIGNED NOT NULL,
    order_date   DATE      NOT NULL,
    ship_time    TIME,
    ordered_at   DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    delivery_year YEAR,
    PRIMARY KEY (order_id)
);

-- Binary types: store raw bytes (images, files, hashes)
CREATE table files (
    file_id    INT          UNSIGNED NOT NULL AUTO_INCREMENT,
    file_name  VARCHAR(255) NOT NULL,
    mime_type  VARCHAR(100) NOT NULL,
    file_data  MEDIUMBLOB,           -- up to 16 MB binary data
    checksum   BINARY(16),           -- fixed 16-byte MD5 hash
    PRIMARY KEY (file_id)
);

-- JSON type (MySQL 5.7+): store structured JSON documents
CREATE table user_settings (
    user_id    INT  UNSIGNED NOT NULL,
    settings   JSON NOT NULL,
    PRIMARY KEY (user_id)
);

-- Insert and query JSON data
INSERT INTO user_settings (user_id, settings)
VALUES (1, '{"theme": "dark", "lang": "en", "notifications": true}');

SELECT user_id,
       settings->>'$.theme'         AS theme,
       settings->>'$.lang'          AS lang,
       settings->>'$.notifications' AS notifications
FROM user_settings
WHERE user_id = 1;

Choose Types from Range, Precision, and Comparison Rules

Select the smallest type that safely covers the domain, but do not trade clarity for a few bytes without measuring. Use DECIMAL for exact fixed-point values such as money, and use floating-point types only when approximation is acceptable. Distinguish date, time, datetime, and timestamp semantics before timezone conversion enters the application.

String behavior depends on character set and collation, which affect storage, equality, ordering, and index width. Bound VARCHAR lengths from real validation rules. Store structured JSON only when its flexible shape and query patterns justify it, then add generated columns or indexes for fields that must be searched efficiently.

Before you move on