MySQL Data Types

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;

Previous Next

MySQL Data Types

Numeric Data Types

String Data Types

Date and Time Data Types

Ready to Level Up Your Skills?