MySQL Data Types

Every column in a database table must have a name and a data type. The data type is a guideline for MySQL to understand what type of data is expected in each individual column, and it also determines how SQL will interact with the stored data. In MySQL, there are four main categories of data types: strings, numeric types, dates with times, and binary data.

EXAMPLE

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    password BINARY(60),
    age TINYINT UNSIGNED,
    salary DECIMAL(10,2),
    profile_picture MEDIUMBLOB,
    birth_date DATE,
    last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    settings SET("dark_mode", "notifications", "newsletter"),
    status ENUM("active", "inactive", "blocked") DEFAULT "active"
);

RESULT

Data types in MySQL are crucial when creating tables, as they determine the type of data that can be stored in individual columns. When using CREATE TABLE, we must specify the appropriate data type for each column. The example above shows the creation of a table with various data types. When you click the "Run Code" button, nothing will happen in this case, as this is just a demonstration example of a table structure. In a real database, this command would create a new table named "users" with all the defined columns.

Every column in a table has:

a column name (e.g., "id", "first_name", "email")
a data type (e.g., INT, VARCHAR, DATE)
optional attributes (e.g., NOT NULL, UNIQUE, AUTO_INCREMENT)

This example demonstrates the most commonly used data types:

INT for integer numbers.
VARCHAR for variable-length text.
DATE for dates.
TIMESTAMP for timestamps.
DECIMAL for decimal numbers.
BINARY for binary data.
SET for sets of values.
ENUM for enumeration types.

MySQL Data Types Quick Cheat Sheet

💡 SIMULATOR TIP: You can test the CREATE TABLE command in our simulator using the data types listed below! The simulator will validate your syntax and return a confirmation message showing the successfully created table and its constraints. Use this table as a reference guide to choose the best data type for your columns.

Data types in MySQL specify what kind of data (text, numbers, dates, binary data) can be stored in each specific database column. Selecting the correct type optimizes execution speed and memory usage.

Data Type	Description and Size	Syntax (HTML)	Usage Example
CHAR(size)	Fixed-length character string (from 0 to 255 characters). Right-padded with spaces if shorter.	`CHAR(length)`	`country_code CHAR(2)` (e.g., 'US')
VARCHAR(size)	Variable-length character string (from 0 to 65,535 characters). The most popular text data type.	`VARCHAR(max_length)`	`first_name VARCHAR(50)` (in the employees table)
TINYTEXT	A very short text string (maximum of 255 characters / bytes).	`TINYTEXT`	`short_description TINYTEXT`
TEXT	Standard text string intended for longer text bodies (maximum of 65,535 characters / 64 KB).	`TEXT`	`note TEXT` (in the null_table)
MEDIUMTEXT	Medium-length text string (maximum of 16,777,215 characters / 16 MB).	`MEDIUMTEXT`	`article_body MEDIUMTEXT`
LONGTEXT	Extremely long text string (maximum of 4,294,967,295 characters / 4 GB).	`LONGTEXT`	`full_book LONGTEXT`
ENUM	A string object that can accept exactly one value chosen from a predefined static list.	`ENUM('v1', 'v2', ...)`	`department ENUM('Management', 'Sales', 'Store')`
SET	A string object that can contain zero, one, or multiple values chosen from a predefined list.	`SET('v1', 'v2', ...)`	`hobbies SET('Sports', 'Coding', 'Music')`
BINARY(size)	Fixed-length binary byte string. Similar to CHAR but stores bytes instead of characters.	`BINARY(size)`	`encryption_key BINARY(16)`
VARBINARY(size)	Variable-length binary byte string. Similar to VARCHAR but for bytes.	`VARBINARY(size)`	`password_hash VARBINARY(64)`
TINYBLOB	A very small Binary Large Object (BLOB) used for small assets or icons (maximum 255 bytes).	`TINYBLOB`	`small_icon TINYBLOB`
BLOB	Standard Binary Large Object used for storing images, audio, or files (maximum 65 KB).	`BLOB`	`employee_photo BLOB`
MEDIUMBLOB	Medium-sized Binary Large Object (maximum of 16 MB).	`MEDIUMBLOB`	`video_clip MEDIUMBLOB`
LONGBLOB	Extremely large Binary Large Object used for massive file archives (maximum of 4 GB).	`LONGBLOB`	`file_archive LONGBLOB`
BIT(size)	A bit-field type used to store bit values (from 1 to 64 bits).	`BIT(size)`	`status_bits BIT(8)`
TINYINT	A very small integer. Range from -128 to 127 (or 0 to 255 if UNSIGNED).	`TINYINT`	`grade TINYINT` (e.g., scores from 1 to 5)
BOOL / BOOLEAN	A logical boolean value. Zero (0) is considered FALSE, anything else (usually 1) is TRUE.	`BOOLEAN`	`is_active BOOLEAN`
SMALLINT	A small integer value. Range from -32,768 to 32,767.	`SMALLINT`	`birth_year SMALLINT`
MEDIUMINT	A medium integer value. Range from -8,388,608 to 8,388,607.	`MEDIUMINT`	`postal_code MEDIUMINT`
INT / INTEGER	Standard integer value. The most common type used for primary keys / IDs (range approx. +-2 billion).	`INT`	`id INT` (in the employees table)
BIGINT	A large integer value. Used when standard INT ranges become insufficient.	`BIGINT`	`page_views BIGINT`
FLOAT	A small floating-point number with single precision (saves storage space).	`FLOAT`	`coefficient FLOAT`
DOUBLE	A floating-point number with double precision (more accurate than FLOAT).	`DOUBLE`	`distance DOUBLE`
DECIMAL(size, d)	An exact fixed-point numeric type. Ideal for financial data where rounding errors are unacceptable.	`DECIMAL(total_digits, decimals)`	`salary DECIMAL(10, 2)` (e.g., 2450.50)
DATE	Stores only a date value in the YYYY-MM-DD format. Range from '1000-01-01' to '9999-12-31'.	`DATE`	`hire_date DATE`
TIME	Stores only a time value in the hh:mm:ss format.	`TIME`	`arrival_time TIME`
DATETIME	A combination of both date and time in the YYYY-MM-DD hh:mm:ss format.	`DATETIME`	`created_at DATETIME`
TIMESTAMP	A timestamp storing seconds since 1970. Automatically updates when a row changes.	`TIMESTAMP`	`last_modified TIMESTAMP`
YEAR	Stores a year value in a 4-digit format (range from 1901 to 2155).	`YEAR`	`release_year YEAR`