Understanding MySQL Data Types

MySQL data types define the kind of values that can be stored in a table column, such as numbers, text, dates, times, and structured JSON documents. Choosing the correct data type helps maintain data accuracy, improve query performance, manage storage efficiently, and enforce meaningful rules for database information.

MySQL Training Course: JustAcademy MySQL Training Course

Course Demo: Register for MySQL Course Demo

1. Learning Objectives

After completing this topic, learners will be able to:

• Understand what MySQL data types are and why they are important.
• Identify numeric, string, date and time, binary, and JSON data types.
• Differentiate between INT, BIGINT, DECIMAL, FLOAT, and DOUBLE.
• Understand the differences between CHAR, VARCHAR, TEXT, and BLOB.
• Choose appropriate data types for dates, timestamps, and times.
• Use data types when creating and modifying tables.
• Understand NULL, NOT NULL, DEFAULT, and AUTO_INCREMENT.
• Apply best practices when designing MySQL database tables.

2. What Are MySQL Data Types?

A data type specifies what kind of value a column can store and how MySQL interprets that value. For example, an age column can use an integer type, a product price can use DECIMAL, and a birth date can use DATE.

Every table column should use a data type appropriate for the information it stores. A suitable data type helps prevent invalid values, reduces unnecessary storage, and makes database operations more reliable.

Example

CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(100),
    salary DECIMAL(10,2),
    joining_date DATE
);

In this example:

• employee_id INT stores an integer identifier.
• employee_name VARCHAR(100) stores a variable-length character string.
• salary DECIMAL(10,2) stores a fixed-point numeric value with two fractional digits.
• joining_date DATE stores a calendar date.

3. Main Categories of MySQL Data Types

MySQL's supported data types and specific limits depend on the MySQL version and configuration. The following sections explain the major types and their practical uses.

4. Numeric Data Types

Numeric data types store whole numbers, decimal values, and approximate floating-point values. They are commonly used for identifiers, quantities, measurements, scores, and financial values.

4.1 Integer Data Types

Integer types store whole numbers without fractional parts.

The storage sizes shown are for the integer data types themselves. Signed and unsigned forms have different value ranges.

Signed and Unsigned Integers

A signed integer supports negative and positive values. An unsigned integer supports only nonnegative values and provides a larger positive range for the same storage size.

CREATE TABLE inventory (
    item_id INT UNSIGNED,
    quantity SMALLINT UNSIGNED
);

Use UNSIGNED only when negative values are not valid for the column. Note that it is not appropriate for every numeric identifier or quantity.

4.2 DECIMAL and NUMERIC

DECIMAL stores exact fixed-point numeric values. NUMERIC is a synonym for DECIMAL in MySQL. These types are useful when exact decimal precision matters, such as for prices and monetary amounts.

Syntax

DECIMAL(precision, scale)

• Precision: The maximum total number of decimal digits.
• Scale: The number of digits after the decimal point.

Example

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2)
);

INSERT INTO products
VALUES (1, 'Keyboard', 1499.50);

DECIMAL(10,2) allows up to 10 total digits, with 2 digits after the decimal point. It is a good choice for prices when the application requires exact decimal arithmetic.

4.3 FLOAT and DOUBLE

FLOAT and DOUBLE store approximate floating-point values. They are commonly used for scientific measurements, statistical calculations, and other values where approximate representation is acceptable.

CREATE TABLE measurements (
    measurement_id INT PRIMARY KEY,
    temperature FLOAT,
    distance DOUBLE
);

Important: FLOAT and DOUBLE can introduce rounding differences because many decimal fractions cannot be represented exactly in binary floating-point. Prefer DECIMAL for financial values requiring exact decimal arithmetic.

4.4 BIT

BIT stores bit values and can be useful for compact binary flags or bit-oriented data.

CREATE TABLE feature_settings (
    setting_id INT PRIMARY KEY,
    enabled BIT(1)
);

For many everyday true/false fields, BOOLEAN or BOOL may be easier to read.

5. String Data Types

String data types store text, character sequences, and other string-like values. They are used for names, addresses, descriptions, codes, and categories.

5.1 CHAR

CHAR stores fixed-length character strings. It can be suitable for values that consistently have the same length.

CREATE TABLE country_codes (
    country_code CHAR(2),
    country_name VARCHAR(100)
);

CHAR(2) is suitable for two-character codes. MySQL's handling of trailing spaces depends on the type and collation.

5.2 VARCHAR

VARCHAR stores variable-length character strings up to the specified maximum length. It is useful for names, email addresses, titles, and other text values whose lengths vary.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(150)
);

VARCHAR(150) allows up to 150 characters, subject to MySQL's limits and character set. The actual storage depends on the value length, character set, and length overhead.

5.3 CHAR vs VARCHAR

5.4 TEXT

TEXT is designed for longer character strings, such as articles, comments, descriptions, and notes. MySQL also provides TINYTEXT, MEDIUMTEXT, and LONGTEXT for different maximum storage capacities.

CREATE TABLE blog_posts (
    post_id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT
);

Choose a TEXT type when the expected content is longer than a practical VARCHAR column. Consider indexing requirements and query patterns when storing large text fields.

5.5 ENUM

ENUM stores one value chosen from a predefined list of permitted values. It can be useful for a small, stable set of categories.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    status ENUM('Pending', 'Processing', 'Shipped', 'Delivered')
);

The status column is designed to accept one of the defined values, subject to SQL mode and validation behavior.

5.6 SET

SET stores zero or more values selected from a predefined list of permitted members.

CREATE TABLE user_preferences (
    user_id INT PRIMARY KEY,
    notifications SET('Email', 'SMS', 'Push')
);

A record could store a combination such as Email and Push. SET is useful only when the allowed list is relatively fixed. For flexible, frequently changing many-to-many relationships, a separate related table is often a better design.

6. Date and Time Data Types

Date and time types store calendar dates, times of day, date-time values, and timestamps. They are important for attendance systems, employee records, booking applications, and transaction histories.

6.1 DATE

DATE stores a calendar date without a time component.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    birth_date DATE
);

6.2 TIME

TIME stores a time value and can also represent durations. Its range is not restricted to a typical 24-hour clock because it can represent elapsed time.

CREATE TABLE work_sessions (
    session_id INT PRIMARY KEY,
    start_time TIME,
    duration TIME
);

6.3 DATETIME

DATETIME stores a date and time value. It is useful for scheduled events or recording local date-time values without automatic time-zone conversion by the column type itself.

CREATE TABLE appointments (
    appointment_id INT PRIMARY KEY,
    appointment_at DATETIME
);

6.4 TIMESTAMP

TIMESTAMP is commonly used to record events such as when a row was created or last updated. MySQL converts TIMESTAMP values between the session time zone and UTC for storage and retrieval.

CREATE TABLE activity_logs (
    log_id INT PRIMARY KEY,
    activity VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Choose DATETIME or TIMESTAMP based on the application's time-zone and date-range requirements. TIMESTAMP has a more limited range than DATETIME in MySQL.

6.5 YEAR

YEAR stores year values. It may be appropriate for graduation years, model years, or other year-only information.

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    launch_year YEAR
);

6.6 DATE vs DATETIME vs TIMESTAMP

7. Binary Data Types

Binary data types store byte sequences rather than ordinary character text. They are useful for binary identifiers, encoded values, file content, and other non-text data.

7.1 BINARY

BINARY stores fixed-length binary strings.

CREATE TABLE device_keys (
    device_id INT PRIMARY KEY,
    key_bytes BINARY(16)
);

7.2 VARBINARY

VARBINARY stores variable-length binary strings.

CREATE TABLE binary_tokens (
    token_id INT PRIMARY KEY,
    token_value VARBINARY(255)
);

7.3 BLOB

BLOB stands for Binary Large Object. It is used to store binary content, such as image bytes, audio data, or other files. MySQL provides TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB types with different capacity limits.

CREATE TABLE file_storage (
    file_id INT PRIMARY KEY,
    file_name VARCHAR(255),
    file_content LONGBLOB
);

Storing large files in a database can increase database size and backup time. Depending on the application, storing files in object or file storage and keeping their references in MySQL may be more appropriate.

8. Boolean Data Types in MySQL

MySQL supports BOOLEAN and BOOL as synonyms for TINYINT(1). They do not create a separate strict Boolean storage type. Commonly, 0 represents false and 1 represents true, although the underlying type can store other numeric values.

Example

CREATE TABLE user_accounts (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

The is_active column indicates whether the account is active. If the application requires only 0 and 1, consider adding a suitable CHECK constraint where supported and enforced by your MySQL version.

9. JSON Data Type

JSON stands for JavaScript Object Notation. MySQL's native JSON data type stores JSON documents and validates their syntax. It is useful when a record contains structured attributes that may vary between records.

Example

CREATE TABLE product_details (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    specifications JSON
);

INSERT INTO product_details
(product_id, product_name, specifications)
VALUES
(1, 'Laptop', '{"ram": "16GB", "storage": "512GB SSD"}');

The specifications column stores a JSON object containing RAM and storage details.

Reading a JSON Value

SELECT
    product_name,
    JSON_EXTRACT(specifications, '$.ram') AS ram
FROM product_details;

JSON can be useful for flexible structured attributes. However, values used frequently in filtering, sorting, joins, or constraints may be better represented as ordinary columns or indexed generated columns, depending on the design.

10. Spatial Data Types

Spatial data types represent geometric and geographic information. They are useful for maps, delivery applications, location-based services, and geographic analysis.

Example

CREATE TABLE store_locations (
    store_id INT PRIMARY KEY,
    store_name VARCHAR(100),
    location POINT
);

The location column can store a point geometry. Working with spatial data may require appropriate spatial reference systems and MySQL spatial functions.

11. NULL, NOT NULL, DEFAULT, and AUTO_INCREMENT

These column attributes are important when designing tables and deciding how data should be stored.

11.1 NULL

NULL represents a missing, unknown, or unavailable value. It is not the same as zero or an empty string.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NULL
);

In this example, a customer can have no phone number recorded, but the customer name is required.

11.2 NOT NULL

NOT NULL requires a column to contain a non-NULL value.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

11.3 DEFAULT

DEFAULT specifies a value that MySQL uses when an INSERT statement omits the column and the column's default applies.

CREATE TABLE tasks (
    task_id INT PRIMARY KEY,
    task_name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'Pending'
);

11.4 AUTO_INCREMENT

AUTO_INCREMENT generates successive numeric values for a suitable integer column when a value is not supplied explicitly.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

When a student is inserted without a student_id value, MySQL generates the identifier.

12. Complete Practical Example

The following example combines several MySQL data types in a student management database.

Step 1: Create the Database

CREATE DATABASE IF NOT EXISTS student_db;
USE student_db;

Step 2: Create a Table Using Different Data Types

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    age TINYINT UNSIGNED,
    fees DECIMAL(10,2),
    average_score FLOAT,
    birth_date DATE,
    admission_time DATETIME,
    is_active BOOLEAN DEFAULT TRUE,
    additional_details JSON
);

Step 3: Insert Sample Records

INSERT INTO students
(student_name, age, fees, average_score, birth_date, admission_time, is_active, additional_details)
VALUES
('Aarav Sharma', 21, 25000.00, 87.5, '2005-04-12', '2026-10-01 10:30:00', TRUE, '{"course": "MySQL", "batch": "Morning"}'),
('Priya Patel', 22, 27500.00, 91.2, '2004-08-20', '2026-10-02 11:00:00', TRUE, '{"course": "SQL", "batch": "Evening"}'),
('Rahul Verma', 20, 22000.00, 78.5, '2006-01-15', '2026-10-03 09:15:00', FALSE, '{"course": "Database Basics", "batch": "Morning"}');

Step 4: Retrieve All Records

SELECT * FROM students;

Step 5: Retrieve Selected Columns

SELECT student_name, fees, birth_date
FROM students;

Step 6: Filter Records

SELECT student_name, average_score
FROM students
WHERE average_score >= 85;

Step 7: Read a JSON Field

SELECT
    student_name,
    JSON_UNQUOTE(JSON_EXTRACT(additional_details, '$.course')) AS course_name
FROM students;

This example demonstrates how different data types help store and retrieve different kinds of information in a structured way.

13. Choosing the Right MySQL Data Type

14. Common Mistakes When Choosing Data Types

15. Best Practices for MySQL Data Types

• Choose the smallest suitable type that can safely represent all valid values.
• Use DECIMAL for monetary amounts that require exact decimal representation.
• Use FLOAT and DOUBLE only when approximate numeric representation is appropriate.
• Use VARCHAR for variable-length text and CHAR for suitable fixed-length values.
• Use DATE for calendar dates and choose DATETIME or TIMESTAMP according to application requirements.
• Define NOT NULL for fields that must always contain a value.
• Use DEFAULT when a meaningful standard value should be supplied automatically.
• Use AUTO_INCREMENT when automatically generated integer identifiers are appropriate.
• Choose JSON for genuinely flexible structured documents, not as a replacement for every relational column.
• Consider character sets and collations when storing multilingual text.
• Validate application inputs even when database column types and constraints are defined.
• Test schema changes before applying them to production databases.

16. Frequently Asked Questions (FAQs)

Q1. What are MySQL data types?

MySQL data types define the kinds of values that can be stored in table columns, including numbers, text, dates, binary values, JSON documents, and spatial data.

Q2. Which data type should be used for whole numbers?

Use an integer type such as TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT according to the required value range.

Q3. What is the difference between INT and BIGINT?

Both store integers, but BIGINT uses more storage and supports a much larger range of values than INT.

Q4. Why is DECIMAL useful for prices?

DECIMAL stores exact fixed-point decimal values, making it suitable for monetary amounts that require exact decimal arithmetic.

Q5. What is the difference between FLOAT and DOUBLE?

Both store approximate floating-point values. DOUBLE generally provides greater precision than FLOAT.

Q6. What is the difference between CHAR and VARCHAR?

CHAR is a fixed-length character type, while VARCHAR is designed for variable-length character strings.

Q7. What is the difference between TEXT and BLOB?

TEXT is designed for character text, while BLOB is designed for binary data.

Q8. Which data type should be used for a date of birth?

DATE is normally appropriate because a date of birth does not require a time component.

Q9. What is the difference between DATETIME and TIMESTAMP?

Both store date and time values, but TIMESTAMP uses session time-zone conversion behavior and has a more limited range than DATETIME.

Q10. Is BOOLEAN a separate storage type in MySQL?

No. BOOLEAN and BOOL are synonyms for TINYINT(1) in MySQL.

Q11. What is the JSON data type used for?

JSON stores validated JSON documents and is useful for flexible structured information.

Q12. What does NULL mean?

NULL represents a missing, unknown, or unavailable value. It is different from zero and an empty string.

17. Practice Questions

1. What is a data type in MySQL?
2. List the major categories of MySQL data types.
3. Explain the difference between INT and BIGINT.
4. What is the difference between DECIMAL and DOUBLE?
5. When should you use CHAR instead of VARCHAR?
6. Explain the purpose of TEXT and BLOB.
7. What is the difference between DATE, DATETIME, and TIMESTAMP?
8. What is the purpose of AUTO_INCREMENT?
9. Explain the difference between NULL and zero.
10. How does MySQL's JSON data type help with structured information?
11. Which data type would you choose for a product price, and why?
12. Why is selecting a suitable data type important for database performance and accuracy?

Practical Exercise

Create a database named company_db and a table named employees containing the following columns:

• employee_id: automatically generated integer primary key.
• employee_name: required variable-length text.
• salary: decimal value with two digits after the decimal point.
• birth_date: calendar date.
• joining_date: date and time.
• is_active: Boolean-like status.
• employee_details: JSON document containing additional attributes.

Insert at least three records, retrieve the employees whose salaries exceed a specified amount, and display the employee names and joining dates.

Sample Solution

CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    birth_date DATE,
    joining_date DATETIME,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    employee_details JSON
);

INSERT INTO employees
(employee_name, salary, birth_date, joining_date, is_active, employee_details)
VALUES
('Amit Shah', 45000.00, '1998-04-12', '2026-01-10 09:00:00', TRUE, '{"department": "IT"}'),
('Neha Singh', 52000.00, '1996-08-20', '2025-11-15 10:00:00', TRUE, '{"department": "HR"}'),
('Karan Mehta', 38000.00, '2000-01-15', '2026-03-01 09:30:00', FALSE, '{"department": "Finance"}');

SELECT * FROM employees;

SELECT employee_name, salary
FROM employees
WHERE salary > 40000;

SELECT employee_name, joining_date
FROM employees;

18. Quick Revision

• INT and BIGINT: Store whole numbers.
• DECIMAL: Stores exact fixed-point decimal values.
• FLOAT and DOUBLE: Store approximate floating-point values.
• CHAR and VARCHAR: Store character strings.
• TEXT: Stores longer text content.
• BINARY and BLOB: Store binary data.
• DATE: Stores calendar dates.
• DATETIME and TIMESTAMP: Store date and time values with different behavior.
• BOOLEAN: Is a synonym for TINYINT(1) in MySQL.
• JSON: Stores validated JSON documents.
• POINT and other spatial types: Represent geographic and geometric data.
• NULL: Represents a missing or unknown value.

19. Conclusion

Understanding MySQL data types is essential for designing reliable, accurate, and efficient databases. Numeric types support calculations, string types store text, date and time types represent temporal values, binary types store bytes, and JSON and spatial types support specialized data requirements. Selecting the appropriate type for each column helps improve data integrity, query behavior, and long-term database maintainability.

Continue learning through the JustAcademy MySQL Training Course.

To explore the course or arrange a demonstration, visit Register for MySQL Course Demo.

Official Reference: MySQL 8.4 Reference Manual – Data Types.