MySQL Data Types

Overview

MySQL supports various data types that define what kind of data can be stored in table columns. Choosing the right data type is crucial for data integrity, storage efficiency, and query performance.

Data Type Categories

MySQL data types are organized into several categories:

Numeric Types - Integer, floating-point, and decimal types
String Types - Character and binary strings
Date and Time Types - Date, time, and datetime values
Spatial Types - Geographic and geometric data
JSON Types - Structured data storage

Numeric Types

Integer Types

Integer types store whole numbers (positive and negative):

| Data Type |--------------------|--------------|------------------------------|--------------------------------| | TINYINT | 1 byte | -128 to 127 | 0 to 255 | | SMALLINT | 2 bytes | -32768 to 32767 | 0 to 65535 | | MEDIUMINT | 3 bytes | -8388608 to 8388607 | 0 to 16777215 | | INT / INTEGER | 4 bytes | -2147483648 to 2147483647 | 0 to 4294967295 | | BIGINT | 8 bytes | -9223372036854775808 to 9223372036854775807 | 0 to 18446744073709551615 |

Examples

sql

CREATE TABLE integers_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tiny_num TINYINT,
    small_num SMALLINT,
    medium_num MEDIUMINT,
    int_num INT,
    big_num BIGINT,
    unsigned_num INT UNSIGNED
);

INSERT INTO integers_demo (tiny_num, small_num, int_num, unsigned_num) 
VALUES (127, 32767, 2147483647, 4294967295);

-- Using UNSIGNED
CREATE TABLE positive_numbers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    age TINYINT UNSIGNED,  -- 0 to 255
    quantity SMALLINT UNSIGNED  -- 0 to 65535
);

Floating-Point Types

Floating-point types store approximate numeric values:

| Data Type |--------------------|--------------|------------------| | FLOAT | 4 bytes | Single precision floating-point | DOUBLE | 8 bytes | Double precision floating-point | REAL | 8 bytes | Alias for DOUBLE

sql

CREATE TABLE floating_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    float_num FLOAT,
    double_num DOUBLE,
    real_num REAL
);

INSERT INTO floating_demo (float_num, double_num) 
VALUES (3.14159, 3.14159265358979);

-- Using DECIMAL for exact values
CREATE TABLE decimal_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    price DECIMAL(10, 2),    -- 10 total digits, 2 decimal places
    amount DECIMAL(15, 4),   -- 15 total digits, 4 decimal places
    rate DECIMAL(5, 3)       -- 5 total digits, 3 decimal places
);

INSERT INTO decimal_demo (price, amount, rate) 
VALUES (19.99, 100.1234, 0.123);

Fixed-Point and Numeric Types

| Data Type |--------------------|--------------|------------------| | DECIMAL | DECIMAL(M, D) | Exact numeric with M digits and D decimal places | NUMERIC | NUMERIC(M, D) | Same as DECIMAL | FIXED | FIXED(M, D) | Same as DECIMAL

sql

-- DECIMAL examples
CREATE TABLE financial_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    salary DECIMAL(10, 2),           -- 99999999.99 max
    commission DECIMAL(8, 2),        -- 999999.99 max
    total_sales DECIMAL(15, 2),      -- 9999999999999.99 max
    exchange_rate DECIMAL(10, 6)     -- 9999.999999
);

INSERT INTO financial_data (salary, commission, total_sales, exchange_rate)
VALUES (75000.00, 1250.50, 1234567890.12, 6.543210);

Bit Values

| Data Type |--------------------|--------------|-------------| | BIT(M) | (M+7)

sql

CREATE TABLE bit_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status BIT(1),              -- Single bit: 0 or 1
    flags BIT(4),               -- 4 bits: 0 to 15
    permissions BIT(8)          -- 8 bits: 0 to 255
);

INSERT INTO bit_demo (status, flags, permissions) 
VALUES (1, 5, 255);  -- status=1, flags=0101, permissions=11111111

Boolean Type

MySQL uses TINYINT(1) to represent boolean values:

sql

CREATE TABLE boolean_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    is_active BOOLEAN,          -- Synonym for TINYINT(1)
    is_verified TINYINT(1),     -- Explicit TINYINT(1)
    has_permission BOOLEAN DEFAULT TRUE
);

INSERT INTO boolean_demo (is_active, is_verified, has_permission)
VALUES (TRUE, 1, FALSE);        -- MySQL treats TRUE as 1, FALSE as 0

-- Query with boolean conditions
SELECT * FROM boolean_demo WHERE is_active = TRUE;
SELECT * FROM boolean_demo WHERE NOT is_verified;

String Types

Character String Types

| Data Type |--------------------|---------------------|------------------| | CHAR(M) | M bytes (0-255) | Fixed-length string | VARCHAR(M) | L+1 bytes (0-65535) | Variable-length string | TINYTEXT | 255 bytes | Very short text | TEXT | 65535 bytes | Text | MEDIUMTEXT | 16777215 bytes | Medium text | LONGTEXT | 4294967295 bytes | Long text

CHAR vs VARCHAR

sql

-- CHAR: Fixed-length, padded with spaces
CREATE TABLE char_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code CHAR(10),     -- Always stores 10 characters
    name CHAR(50)      -- Always stores 50 characters
);

INSERT INTO char_demo (code, name) VALUES 
    ('ABC', 'John'),
    ('XYZ', 'Jane');

-- VARCHAR: Variable-length, no padding
CREATE TABLE varchar_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(10),  -- Stores actual length
    name VARCHAR(50)   -- Stores actual length
);

INSERT INTO varchar_demo (code, name) VALUES 
    ('ABC', 'John'),
    ('XYZ', 'Jane');

-- Observe the difference
SELECT 
    code, 
    LENGTH(code) AS char_length,
    name,
    LENGTH(name) AS char_length
FROM char_demo
UNION ALL
SELECT 
    code, 
    LENGTH(code),
    name,
    LENGTH(name)
FROM varchar_demo;

Text Types

sql

-- TINYTEXT: Very short strings (up to 255 characters)
CREATE TABLE tinytext_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    short_code TINYTEXT,
    short_description TINYTEXT
);

-- TEXT: Standard text (up to 65,535 bytes)
CREATE TABLE text_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    article TEXT,          -- For articles, posts
    description TEXT       -- For descriptions
);

-- MEDIUMTEXT: Medium-length text (up to 16 MB)
CREATE TABLE mediumtext_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    blog_content MEDIUMTEXT,   -- Blog posts
    email_body MEDIUMTEXT      -- Email content
);

-- LONGTEXT: Long text (up to 4 GB)
CREATE TABLE longtext_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    book_content LONGTEXT,     -- Long documents
    data_export LONGTEXT       -- Large data exports
);

Binary String Types

| Data Type |--------------------|---------------------|------------------| | BINARY(M) | M bytes | Fixed-length binary | VARBINARY(M) | L+1 bytes | Variable-length binary | TINYBLOB | 255 bytes | Binary large object | BLOB | 65535 bytes | Binary large object | MEDIUMBLOB | 16777215 bytes | Binary large object | LONGBLOB | 4294967295 bytes | Binary large object

sql

-- Binary data storage
CREATE TABLE binary_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    hash BINARY(32),              -- SHA-256 hash (32 bytes)
    image_data BLOB,              -- Image files
    file_content VARBINARY(1000)  -- Variable-length binary
);

-- Store SHA-256 hash
INSERT INTO binary_demo (hash) 
VALUES (UNHEX(SHA2('password', 256)));

ENUM Type

sql

-- Define possible values
CREATE TABLE enum_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status ENUM('pending', 'active', 'completed', 'cancelled'),
    priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    color ENUM('red', 'green', 'blue')
);

INSERT INTO enum_demo (status, priority, color) 
VALUES 
    ('active', 'high', 'green'),
    ('pending', 'medium', 'red'),
    ('completed', 'low', 'blue');

-- Invalid values will cause error
-- INSERT INTO enum_demo (status) VALUES ('invalid');  -- Error

-- Check valid values
SELECT * FROM enum_demo WHERE status = 'active';

SET Type

sql

-- Store multiple values from a defined set
CREATE TABLE set_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    skills SET('SQL', 'Python', 'Java', 'JavaScript', 'C++'),
    permissions SET('read', 'write', 'delete', 'admin')
);

INSERT INTO set_demo (skills, permissions) 
VALUES 
    ('SQL,Python', 'read,write'),
    ('SQL,Python,Java', 'read,write,delete'),
    ('JavaScript,C++', 'admin');

-- Query using FIND_IN_SET
SELECT * FROM set_demo WHERE FIND_IN_SET('SQL', skills) > 0;

Date and Time Types

Date and Time Overview

| Data Type |--------------------|--------------|-------------|------------------| | DATE | 3 bytes | 1000-01-01 to 9999-12-31 | Date only | TIME | 3 bytes | -838:59:59 to 838:59:59 | Time only | DATETIME | 8 bytes | 1000-01-01 00:00:00 to 9999-12-31 23:59:59 | Date and time | TIMESTAMP | 4 bytes | 1970-01-01 00:00:01 to 2038-01-19 03:14:07 | Unix timestamp | YEAR | 1 byte | 1901 to 2155 | Year value

DATE Type

sql

-- Date only (YYYY-MM-DD)
CREATE TABLE date_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    birth_date DATE,
    hire_date DATE NOT NULL,
    graduation_date DATE
);

INSERT INTO date_demo (birth_date, hire_date, graduation_date) VALUES
    ('1990-05-15', '2020-01-15', '2012-06-20'),
    ('1985-12-25', '2019-06-01', '2008-05-15');

-- Date functions
SELECT 
    birth_date,
    YEAR(birth_date) AS birth_year,
    MONTH(birth_date) AS birth_month,
    DAY(birth_date) AS birth_day,
    DAYOFWEEK(birth_date) AS day_of_week,
    DAYNAME(birth_date) AS day_name,
    MONTHNAME(birth_date) AS month_name
FROM date_demo;

-- Date arithmetic
SELECT 
    hire_date,
    DATE_ADD(hire_date, INTERVAL 1 YEAR) AS one_year_later,
    DATE_SUB(hire_date, INTERVAL 30 DAY) AS thirty_days_before,
    DATEDIFF(CURDATE(), hire_date) AS days_since_hired
FROM date_demo;

TIME Type

sql

-- Time only (HH:MM:SS or HH:MM:SS.microseconds)
CREATE TABLE time_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    start_time TIME,
    end_time TIME,
    duration TIME
);

INSERT INTO time_demo (start_time, end_time, duration) VALUES
    ('09:00:00', '17:30:00', '08:30:00'),
    ('08:00:00', '16:00:00', '08:00:00');

-- Time functions
SELECT 
    start_time,
    HOUR(start_time) AS hour_part,
    MINUTE(start_time) AS minute_part,
    SECOND(start_time) AS second_part,
    TIME_FORMAT(start_time, '%h:%i:%s %p') AS formatted
FROM time_demo;

DATETIME Type

sql

-- Date and time (YYYY-MM-DD HH:MM:SS)
CREATE TABLE datetime_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
    event_datetime DATETIME NOT NULL
);

INSERT INTO datetime_demo (event_datetime) VALUES
    ('2024-01-15 14:30:00'),
    ('2024-02-20 09:15:30'),
    (NOW());

-- DATETIME functions
SELECT 
    created_at,
    DATE(created_at) AS date_only,
    TIME(created_at) AS time_only,
    YEAR(created_at) AS year,
    MONTH(created_at) AS month,
    DAY(created_at) AS day,
    HOUR(created_at) AS hour,
    MINUTE(created_at) AS minute,
    SECOND(created_at) AS second,
    QUARTER(created_at) AS quarter,
    WEEK(created_at) AS week,
    DAYOFYEAR(created_at) AS day_of_year
FROM datetime_demo;

-- DATETIME arithmetic
SELECT 
    created_at,
    created_at + INTERVAL 1 DAY AS tomorrow,
    created_at - INTERVAL 2 HOUR AS two_hours_ago,
    TIMESTAMPDIFF(DAY, created_at, NOW()) AS days_ago
FROM datetime_demo;

TIMESTAMP Type

sql

-- Timestamp (stored as UTC, displayed in session timezone)
CREATE TABLE timestamp_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO timestamp_demo (last_login) VALUES (NOW());

-- TIMESTAMP characteristics
-- - Automatically converts between timezones
-- - Default value is CURRENT_TIMESTAMP
-- - Updates automatically on row modification
-- - Range limited to 1970-2038

YEAR Type

sql

-- Year value (4-digit format)
CREATE TABLE year_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    birth_year YEAR,
    graduation_year YEAR(4),
    fiscal_year YEAR
);

INSERT INTO year_demo (birth_year, graduation_year, fiscal_year) VALUES
    (1990, 2012, 2024),
    (1985, 2008, 2023);

SELECT * FROM year_demo WHERE birth_year > 1990;

JSON Type

MySQL 5.7.8+ supports native JSON data type:

sql

-- JSON type for structured data
CREATE TABLE json_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_data JSON,
    preferences JSON,
    metadata JSON
);

-- Insert JSON data
INSERT INTO json_demo (user_data, preferences, metadata) VALUES
(
    '{"name": "John", "age": 30, "city": "New York"}',
    '{"theme": "dark", "notifications": true, "language": "en"}',
    '{"version": "1.0", "source": "registration"}'
),
(
    '{"name": "Jane", "age": 25, "city": "Los Angeles"}',
    '{"theme": "light", "notifications": false, "language": "es"}',
    '{"version": "1.0", "source": "import"}'
);

-- JSON extraction
SELECT 
    user_data->>'$.name' AS name,
    user_data->>'$.age' AS age,
    user_data->>'$.city' AS city,
    preferences->>'$.theme' AS theme
FROM json_demo;

-- JSON validation
SELECT * FROM json_demo WHERE JSON_VALID(user_data);

-- JSON comparison
SELECT * FROM json_demo 
WHERE JSON_EXTRACT(user_data, '$.age') > 27;

-- Create JSON
SELECT 
    JSON_OBJECT('name', 'John', 'age', 30) AS json_data,
    JSON_ARRAY('SQL', 'Python', 'Java') AS languages,
    JSON_MERGE('{"a":1}', '{"b":2}') AS merged;

Spatial Types

MySQL supports spatial data types for geographic and geometric data:

sql

-- Spatial data types
CREATE TABLE spatial_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    point_data POINT,
    line_data LINESTRING,
    polygon_data POLYGON,
    geometry_data GEOMETRY
);

-- Insert spatial data
INSERT INTO spatial_demo (point_data, line_data, polygon_data) VALUES
(
    ST_GeomFromText('POINT(0 0)'),
    ST_GeomFromText('LINESTRING(0 0,10 10,20 20)'),
    ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0))')
);

-- Spatial functions
SELECT 
    ST_X(point_data) AS x_coordinate,
    ST_Y(point_data) AS y_coordinate,
    ST_Length(line_data) AS line_length,
    ST_Area(polygon_data) AS polygon_area
FROM spatial_demo;

Choosing Data Types

Best Practices

Use appropriate integer sizes - Choose the smallest integer type that fits your data range
Use DECIMAL for monetary values - Floating-point is not exact for financial data
Use VARCHAR instead of CHAR for variable-length data - Saves storage space
Use appropriate TEXT types - Choose based on expected data size
Use ENUM for fixed sets - Efficient storage for limited options
Use DATETIME vs TIMESTAMP appropriately - Consider timezone requirements
Use JSON for semi-structured data - Flexible schema for complex data

Data Type Selection Guide

| Data Type |--------------------|---------------------| | TINYINT | Boolean flags, small counters | INT | Primary keys, counters, quantities | BIGINT | Large counters, financial amounts | DECIMAL | Money, precise measurements | FLOAT | CHAR | Fixed-length codes, status values | VARCHAR | Names, descriptions, variable text | TEXT | Long articles, comments | DATE | Birth dates, hire dates, deadlines | DATETIME | Timestamps, scheduled events | TIMESTAMP | Session timestamps, auto-update fields | JSON | Configuration data, flexible attributes | ENUM | Status, priority, category with limited options | SET | Multiple selections from fixed options

Summary

MySQL provides comprehensive data types for various data storage needs:

Numeric Types: From TINYINT to BIGINT with signed and unsigned options
String Types: CHAR, VARCHAR, TEXT for different length requirements
**Date
JSON Type: Native JSON support for semi-structured data
Spatial Types: Geographic and geometric data support
**ENUM

Choosing the right data type affects:

Storage efficiency
Query performance
Data integrity
Application functionality

Next Steps

Continue to Create Database to learn how to create and manage MySQL databases.

Previous: Select Database

Next: Create Table

MySQL Data Types ​

Overview ​

Data Type Categories ​

Numeric Types ​

Integer Types ​

Examples ​

Floating-Point Types ​

Fixed-Point and Numeric Types ​

Bit Values ​

Boolean Type ​

String Types ​

Character String Types ​

CHAR vs VARCHAR ​

Text Types ​

Binary String Types ​

ENUM Type ​

SET Type ​

Date and Time Types ​

Date and Time Overview ​

DATE Type ​

TIME Type ​

DATETIME Type ​

TIMESTAMP Type ​

YEAR Type ​

JSON Type ​

Spatial Types ​

Choosing Data Types ​

Best Practices ​

Data Type Selection Guide ​

Summary ​

Next Steps ​