• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Data Types

← Previous Next →

When you create a table in MySQL, each column must have a data type that defines what kind of values it can store. Data types tell MySQL how much space to use, how to sort and compare values, and how to handle them during calculations. Choosing the right data type helps you save storage space, improve performance, and maintain data accuracy.

In this tutorial, we’ll explore all major MySQL data types — numbers, strings, dates, and more — along with examples to help you use them correctly.

What Are Data Types in MySQL?

A data type specifies the kind of value that a column can hold. For example:

  • Use an INT type for whole numbers like age or quantity.

  • Use a VARCHAR type for text like names or email addresses.

  • Use a DATE type for storing calendar dates.

Every column in a table must have a defined data type when it’s created. MySQL uses that information to store, retrieve, and compare values efficiently.

Categories of MySQL Data Types

MySQL data types are broadly divided into four categories:

  1. Numeric Data Types

  2. String (Character) Data Types

  3. Date and Time Data Types

  4. Boolean and JSON Data Types

Let’s look at each category in detail.

1. Numeric Data Types

Numeric data types store numbers — both whole numbers and decimals. These are often used for quantities, IDs, prices, and other numeric values.

a. Integer Types

Data Type Description Range (Signed) Example
TINYINT Very small integer -128 to 127 25
SMALLINT Small integer -32,768 to 32,767 1500
MEDIUMINT Medium integer -8 million to 8 million 2500000
INT / INTEGER Regular integer -2 billion to 2 billion 65000
BIGINT Large integer -9 quintillion to 9 quintillion 9876543210

If you use the UNSIGNED keyword, the column only stores positive values, doubling the upper range.
Example:

CREATE TABLE products (
  product_id INT UNSIGNED,
  quantity SMALLINT UNSIGNED
);

b. Decimal and Floating-Point Types

For prices, averages, or percentages, you often need numbers with decimal places.

Data Type Description Example
DECIMAL(M,D) Exact numeric value (fixed point). DECIMAL(10,2) → 99999999.99
FLOAT Approximate value (single precision). 45.67
DOUBLE Approximate value (double precision). 1234.56789

DECIMAL is ideal for financial data because it avoids rounding errors that can happen with FLOAT or DOUBLE.

Example:

CREATE TABLE payments (
  id INT,
  amount DECIMAL(8,2)
);

2. String (Character) Data Types

String types store text, such as names, addresses, or comments. MySQL offers several types depending on the length and nature of the text.

Data Type Description Maximum Length Example
CHAR(n) Fixed-length string Up to 255 characters 'IN'
VARCHAR(n) Variable-length string Up to 65,535 bytes 'Mumbai'
TEXT Large text data Up to 65,535 characters 'This is a long description'
MEDIUMTEXT Medium text Up to 16 million characters 'Blog content...'
LONGTEXT Very large text Up to 4GB 'Entire book text...'
ENUM('value1','value2',...) List of predefined values Depends on list 'Yes' or 'No'
SET('a','b',...) Multiple predefined values Depends on list 'Option1,Option2'

Example:

CREATE TABLE users (
  id INT,
  name VARCHAR(50),
  gender ENUM('Male','Female','Other'),
  bio TEXT
);

Here:

  • VARCHAR(50) stores short text like names.

  • ENUM restricts input to specific options.

  • TEXT stores long descriptions.

3. Date and Time Data Types

Date and time types are used to store temporal values — for example, order dates, birth dates, or timestamps.

Data Type Description Example
DATE Stores date only 2025-10-23
TIME Stores time only 14:30:00
DATETIME Stores both date and time 2025-10-23 14:30:00
TIMESTAMP Stores UTC date and time, updates automatically 2025-10-23 09:00:00
YEAR Stores year value 2025

Example:

CREATE TABLE events (
  id INT,
  event_name VARCHAR(100),
  event_date DATETIME
);

You can then insert and retrieve dates:

INSERT INTO events VALUES (1, 'Conference', '2025-11-10 10:00:00');

4. Boolean and JSON Data Types

a. BOOLEAN

MySQL doesn’t have a separate BOOLEAN type. Instead, it treats BOOLEAN as a synonym for TINYINT(1) — meaning it stores values as 0 (false) and 1 (true).

Example:

CREATE TABLE status (
  id INT,
  is_active BOOLEAN
);

b. JSON

The JSON data type allows you to store structured data (key-value pairs) inside a column. It’s useful when the structure of your data might vary from record to record.

Example:

CREATE TABLE user_data (
  id INT,
  details JSON
);

INSERT INTO user_data VALUES
(1, '{"age": 25, "city": "Pune", "skills": ["PHP","MySQL"]}');

You can query specific keys using MySQL’s JSON functions:

SELECT details->>'$.city' AS city FROM user_data;

Choosing the Right Data Type

Choosing the correct data type is important for accuracy and performance. Here are some simple tips:

Data Type When to Use
INT / BIGINT For counting, IDs, or quantities
DECIMAL For currency or precise calculations
FLOAT / DOUBLE For scientific or approximate values
CHAR For fixed-length values like country codes
VARCHAR For general text like names or emails
TEXT For long descriptions or comments
ENUM / SET For predefined values
DATE / DATETIME / TIMESTAMP For time-based data
BOOLEAN For true/false flags
JSON For flexible or nested data

Practical Example

Here’s a complete example that combines various data types:

CREATE TABLE products (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(100),
  price DECIMAL(10,2),
  available BOOLEAN,
  category ENUM('Electronics','Furniture','Clothing'),
  description TEXT,
  added_on DATETIME,
  specifications JSON
);

INSERT INTO products (product_name, price, available, category, description, added_on, specifications)
VALUES
('Smartphone', 19999.99, 1, 'Electronics', 'Latest model with 8GB RAM', NOW(), '{"color": "black", "warranty": "1 year"}');

This table stores:

  • IDs and prices using numeric types

  • Availability as a boolean

  • Product details as text and JSON

  • Timestamp using DATETIME

Summary of the Tutorial

MySQL provides a wide range of data types to handle almost every kind of data.
You learned about:

  • Numeric types for numbers and calculations

  • String types for text and character data

  • Date and time types for temporal values

  • Boolean and JSON types for logical and structured data

Choosing the right data type keeps your database efficient, accurate, and scalable. Whenever you design a new table, take a few extra minutes to decide which data type fits each column best — it makes a big difference in performance and reliability.

Practice Questions

  1. Create a table named students with columns student_id (INT, primary key), name (VARCHAR(50)), age (TINYINT), and marks (DECIMAL(5,2)).

  2. Write an SQL query to insert three records into the students table with different ages and marks.

  3. Create a table called employees with columns: id (INT AUTO_INCREMENT), full_name (VARCHAR(100)), salary (DECIMAL(10,2)), and is_permanent (BOOLEAN).

  4. Insert two records into employees where one employee is permanent and one is not.

  5. Create a table orders with the following columns: order_id (INT), order_date (DATE), delivery_time (TIME), and total_amount (FLOAT).

  6. Write a query to insert an order with today’s date and current time into the orders table.

  7. Create a table named articles with columns article_id (INT), title (VARCHAR(255)), content (TEXT), and published_on (DATETIME).

  8. Create a table products with columns id (INT), name (VARCHAR(50)), category (ENUM('Electronics','Furniture','Clothing')), and in_stock (BOOLEAN).

  9. Insert three rows into the products table using different categories and stock values.

  10. Create a table user_profiles with columns user_id (INT), username (VARCHAR(50)), and details (JSON) to store information like age, city, and interests. Insert one record with sample JSON data.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top