Understanding INT, VARCHAR, TEXT, DATE, and Numeric Data Types in MySQL

Course: MySQL Database Management

Topic: Understanding INT, VARCHAR, TEXT, DATE, and Numeric Data Types

Explore the JustAcademy MySQL Training Course or Register for MySQL Course Demo.

1. Introduction

MySQL data types define the kind of values that a database column can store. Choosing the correct data type helps maintain data accuracy, control storage requirements, improve query performance, and enforce meaningful rules.

For example, a student ID can use INT, a student's name can use VARCHAR, a long article can use TEXT, an enrollment date can use DATE, and a course fee can use DECIMAL.

This topic explains five important areas: integer data, variable-length strings, long text, dates, and other numeric data types.

2. Learning Objectives

• Understand what MySQL data types are and why they are important.
• Learn the purpose and usage of INT.
• Understand the difference between VARCHAR and TEXT.
• Learn how to store and query dates using DATE.
• Understand integer, decimal, and floating-point numeric types.
• Learn how to define data types when creating a table.
• Practice inserting, retrieving, and filtering values.
• Choose suitable data types for real-world applications.

3. What Is a Data Type in MySQL?

A data type specifies the kind of value that can be stored in a column. When creating a table, each column is assigned a data type based on its purpose.

Example

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

In this table, each column is designed for a different kind of information. The employee ID is an integer, the name is variable-length text, the description stores longer text, the joining date stores a calendar date, and the salary stores an exact decimal amount.

4. Understanding INT Data Type

INT, also called INTEGER, is a numeric data type used to store whole numbers without a fractional part. It is commonly used for IDs, quantities, counters, and other integer values.

4.1 Syntax

column_name INT;

4.2 Example

CREATE TABLE students (
    student_id INT,
    student_name VARCHAR(100),
    age INT
);

Here, student_id and age are defined as integer columns.

4.3 Signed and Unsigned INT

A signed INT can store negative and positive values, while an INT UNSIGNED stores only nonnegative integer values.

Both signed and unsigned INT use 4 bytes of storage in MySQL.

4.4 Using INT with AUTO_INCREMENT

AUTO_INCREMENT allows MySQL to generate a new integer value automatically when a row is inserted without an explicit value for that column.

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL DEFAULT 0
);

When products are inserted without a product_id, MySQL generates an ID automatically.

4.5 Inserting Data into an INT Column

INSERT INTO products (product_name, quantity)
VALUES
    ('Keyboard', 15),
    ('Mouse', 25),
    ('Monitor', 10);

4.6 Retrieving Integer Values

SELECT product_name, quantity
FROM products;

4.7 Filtering Integer Values

SELECT *
FROM products
WHERE quantity > 10;

This query returns products with a quantity greater than 10.

4.8 When Should You Use INT?

• Employee IDs and student IDs.
• Product quantities and inventory counts.
• Number of completed orders.
• Age in whole years, when that representation is suitable.
• Numeric counters and other whole-number values.

Important: Do not use INT for phone numbers, postal codes, or other values that are identifiers rather than quantities. These may contain leading zeros, plus signs, or formatting characters and are generally better stored as strings.

5. Understanding VARCHAR Data Type

VARCHAR is a variable-length character string data type. It is suitable for text values whose lengths may vary from row to row, such as names, email addresses, city names, and titles.

5.1 Syntax

column_name VARCHAR(length);

The length specifies the maximum number of characters permitted by the column definition, subject to MySQL's row-size and character-set limits.

5.2 Example

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    city VARCHAR(80)
);

5.3 Inserting VARCHAR Values

INSERT INTO customers (customer_name, email, city)
VALUES
    ('Rahul Sharma', '[email protected]', 'Mumbai'),
    ('Priya Patel', '[email protected]', 'Pune'),
    ('Amit Verma', '[email protected]', 'Delhi');

5.4 Retrieving VARCHAR Values

SELECT customer_name, city
FROM customers;

5.5 Searching VARCHAR Values

SELECT *
FROM customers
WHERE city = 'Mumbai';

5.6 Using LIKE with VARCHAR

The LIKE operator is useful for searching text patterns.

SELECT *
FROM customers
WHERE customer_name LIKE 'R%';

This query returns customer names beginning with the letter R. The percent sign (%) matches zero or more characters.

5.7 Important Characteristics of VARCHAR

• Stores variable-length character strings.
• Uses a declared maximum length.
• Uses storage based on the value's encoded length plus a length prefix.
• Supports text comparison and sorting according to the column's character set and collation.
• Is commonly used for names, email addresses, titles, and short descriptions.

5.8 When Should You Use VARCHAR?

• Names and usernames.
• Email addresses and website URLs.
• City, state, and country names.
• Product names and short titles.
• Phone numbers and postal codes when treated as text identifiers.

6. Understanding TEXT Data Type

TEXT is a string data type intended for relatively long text content. It is useful for articles, descriptions, comments, reviews, and other text that may be much longer than typical VARCHAR values.

6.1 Syntax

column_name TEXT;

6.2 Example

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

Here, the title uses VARCHAR because it is expected to be relatively short, while the post content uses TEXT for longer writing.

6.3 Inserting TEXT Values

INSERT INTO blog_posts (title, content)
VALUES (
    'Introduction to MySQL',
    'MySQL is a relational database management system used to store, organize, and retrieve structured data. It supports SQL queries, constraints, relationships, and transactions.'
);

6.4 Retrieving TEXT Values

SELECT title, content
FROM blog_posts;

6.5 Searching TEXT Values

SELECT *
FROM blog_posts
WHERE content LIKE '%database%';

This query returns posts whose content contains the word database. For large datasets and more advanced search requirements, consider an appropriate full-text index and MySQL's full-text search features.

6.6 TEXT Family in MySQL

The effective amount of text that can be stored depends on the character set, row and server limits, and configuration. Multibyte characters may require more than one byte each.

6.7 When Should You Use TEXT?

• Blog posts and article bodies.
• Product descriptions that may be lengthy.
• Customer reviews and feedback.
• Long comments and notes.
• Documentation and other extended text content.

Best practice: Use VARCHAR for shorter, bounded text fields and TEXT when longer text is expected. Do not choose TEXT automatically for every string column.

7. Difference Between VARCHAR and TEXT

Neither type is always faster or better. Select the type based on expected content, indexing needs, constraints, and the design of your application.

8. Understanding DATE Data Type

DATE stores a calendar date without a time component. It is commonly used for birth dates, joining dates, booking dates, deadlines, and event dates.

8.1 Standard Date Format

MySQL commonly represents a date as YYYY-MM-DD, where YYYY is the year, MM is the month, and DD is the day.

Example: 2026-10-16

8.2 Creating a Table with DATE

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    joining_date DATE
);

8.3 Inserting DATE Values

INSERT INTO employees (employee_name, joining_date)
VALUES
    ('Rahul Sharma', '2026-01-15'),
    ('Priya Patel', '2026-02-10'),
    ('Amit Verma', '2026-03-05');

8.4 Retrieving DATE Values

SELECT employee_name, joining_date
FROM employees;

8.5 Filtering by Date

SELECT *
FROM employees
WHERE joining_date >= '2026-02-01';

This query returns employees whose joining date is on or after February 1, 2026.

8.6 Extracting Year, Month, and Day

SELECT
    employee_name,
    YEAR(joining_date) AS joining_year,
    MONTH(joining_date) AS joining_month,
    DAY(joining_date) AS joining_day
FROM employees;

8.7 Using a Date Range

SELECT *
FROM employees
WHERE joining_date BETWEEN '2026-01-01' AND '2026-03-31';

BETWEEN includes both endpoints. For a DATE column, this example includes dates from January 1 through March 31, 2026.

8.8 DATE vs. DATETIME vs. TIMESTAMP

Use DATE when the time of day is not needed. Use DATETIME or TIMESTAMP when time information is important. MySQL handles TIMESTAMP values with session time-zone conversion, while DATETIME does not automatically perform the same conversion.

9. Understanding Numeric Data Types

MySQL provides several numeric data types for storing whole numbers, exact decimal values, approximate decimal values, and bit values. The correct choice depends on the required range and precision.

9.1 Integer Types

Integer types store whole numbers without a fractional part.

For unsigned integer types, the lower bound is zero and the upper bound is higher. For example, INT UNSIGNED ranges from 0 to 4,294,967,295.

9.2 DECIMAL and NUMERIC

DECIMAL and NUMERIC are exact-value numeric types. They are suitable for monetary amounts, prices, and other values where predictable decimal precision is important.

Syntax

DECIMAL(precision, scale)

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

Example

CREATE TABLE course_fees (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    fee DECIMAL(10,2) NOT NULL
);

DECIMAL(10,2) allows up to 10 total digits, including 2 digits after the decimal point. For example, 12500.50 is a valid value.

INSERT INTO course_fees (course_name, fee)
VALUES
    ('MySQL Fundamentals', 5000.00),
    ('Python Programming', 7500.50);

SELECT course_name, fee
FROM course_fees
WHERE fee > 6000.00;

Best practice: Prefer DECIMAL for prices, fees, balances, and other monetary amounts where exact decimal storage is needed.

9.3 FLOAT

FLOAT stores approximate numeric values using floating-point representation. It can be useful when a large range and compact storage matter more than exact decimal representation.

CREATE TABLE sensor_readings (
    reading_id INT AUTO_INCREMENT PRIMARY KEY,
    temperature FLOAT
);

Floating-point values may have small representation and rounding differences. Avoid relying on exact equality for calculations that require precise decimal results.

9.4 DOUBLE

DOUBLE is a double-precision floating-point type. It generally provides more precision than FLOAT, but it still stores approximate values.

CREATE TABLE measurements (
    measurement_id INT AUTO_INCREMENT PRIMARY KEY,
    measurement_value DOUBLE
);

Use DOUBLE for scientific measurements and calculations that require a wider range or more approximate precision. Use DECIMAL instead when exact decimal arithmetic is required.

9.5 BIT

BIT stores bit values. It can be useful for compact bit-oriented data, though a BOOLEAN alias or an integer with a suitable constraint may be clearer for ordinary application flags.

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

10. Difference Between INT, DECIMAL, FLOAT, and DOUBLE

11. Choosing the Right Data Type

12. Practical Project: Student Management Database

Build a simple student management database that demonstrates the use of INT, VARCHAR, TEXT, DATE, and numeric data types.

Step 1: Create the Database

CREATE DATABASE IF NOT EXISTS student_management;
USE student_management;

Step 2: Create the Students Table

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    address TEXT,
    enrollment_date DATE NOT NULL
);

Step 3: Create the Courses Table

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    course_description TEXT,
    course_fee DECIMAL(10,2) NOT NULL,
    duration_months INT UNSIGNED NOT NULL
);

Step 4: Insert Sample Data

INSERT INTO students
    (student_name, email, address, enrollment_date)
VALUES
    ('Rahul Sharma', '[email protected]', 'Mumbai, Maharashtra', '2026-01-10'),
    ('Priya Patel', '[email protected]', 'Pune, Maharashtra', '2026-02-15'),
    ('Amit Verma', '[email protected]', 'Delhi, India', '2026-03-05');

INSERT INTO courses
    (course_name, course_description, course_fee, duration_months)
VALUES
    ('MySQL Training', 'Learn SQL queries, tables, joins, and database design.', 6000.00, 3),
    ('Python Training', 'Learn Python programming and practical development.', 8000.50, 4);

Step 5: Retrieve Student Records

SELECT *
FROM students;

Step 6: Find Students Enrolled After a Date

SELECT student_name, enrollment_date
FROM students
WHERE enrollment_date > '2026-02-01';

Step 7: Find Courses Within a Fee Range

SELECT course_name, course_fee
FROM courses
WHERE course_fee BETWEEN 5000.00 AND 7000.00;

Step 8: Search Course Descriptions

SELECT course_name, course_description
FROM courses
WHERE course_description LIKE '%database%';

Step 9: Sort Courses by Fee

SELECT course_name, course_fee
FROM courses
ORDER BY course_fee ASC;

This project demonstrates how different data types work together in a practical database. You can extend it by adding an enrollment table, course categories, and student progress records.

13. Common Mistakes to Avoid

• Using INT for decimal amounts: Use DECIMAL when fractional monetary values must be stored.
• Using FLOAT for money: Floating-point values are approximate and may introduce rounding differences.
• Using VARCHAR for every column: Use numeric and date types when they accurately represent the data.
• Choosing VARCHAR lengths without planning: Set a reasonable maximum length based on application requirements.
• Using TEXT for short values unnecessarily: A suitable VARCHAR is often more convenient for short, bounded fields.
• Storing dates in inconsistent text formats: Use DATE for calendar dates so date comparisons and functions work correctly.
• Storing phone numbers as integers: Phone numbers are identifiers, not quantities, and can contain leading zeros or a plus sign.
• Ignoring numeric ranges: Ensure the chosen integer type can store the largest expected value.
• Confusing NULL and zero: NULL represents a missing or unknown value, while zero is a numeric value.
• Ignoring constraints: Use NOT NULL, UNIQUE, and other appropriate constraints to enforce data rules.

14. Frequently Asked Questions (FAQs)

Q1. What is INT in MySQL?

INT is a 4-byte integer data type used for whole numbers such as IDs, quantities, and counters.

Q2. What is the difference between INT and INT UNSIGNED?

INT supports negative and positive values, while INT UNSIGNED supports values from zero to a larger positive maximum.

Q3. What is VARCHAR used for?

VARCHAR stores variable-length character strings such as names, email addresses, city names, and titles.

Q4. What is TEXT used for?

TEXT stores longer text content, such as article bodies, comments, and extended descriptions.

Q5. What is the main difference between VARCHAR and TEXT?

VARCHAR has a declared maximum length and is commonly used for bounded text. TEXT is designed for longer content and has its own capacity and indexing considerations.

Q6. What does DATE store?

DATE stores a calendar date without a time component, commonly represented as YYYY-MM-DD.

Q7. Which data type is best for salary or fees?

DECIMAL is generally suitable for salary, course fees, prices, and other monetary values that need exact decimal representation.

Q8. What is the difference between FLOAT and DOUBLE?

Both are approximate floating-point types. DOUBLE generally provides greater precision and range than FLOAT, at the cost of more storage.

Q9. Can VARCHAR store numbers?

Yes, but those values are treated as text rather than numeric values. Use a numeric type when you need numeric calculations or comparisons.

Q10. Can TEXT store very large content?

The standard TEXT type can store up to 65,535 bytes, but effective capacity depends on character encoding and MySQL limits. Larger variants include MEDIUMTEXT and LONGTEXT.

15. Practice Questions

1. What is a data type in MySQL, and why is it important?
2. Explain the difference between signed and unsigned INT.
3. Write SQL to create a table containing an integer ID and a variable-length name.
4. When should you use VARCHAR instead of TEXT?
5. Write a query to find customers from Mumbai.
6. What is the standard date format commonly used by MySQL?
7. Write a query to find records between two dates.
8. Explain the difference between INT, DECIMAL, FLOAT, and DOUBLE.
9. Why is DECIMAL generally preferred over FLOAT for monetary values?
10. Design an employee table using INT, VARCHAR, DATE, and DECIMAL.

16. Quick Revision

• INT stores whole numbers.
• VARCHAR stores variable-length text with a declared maximum length.
• TEXT stores longer text content.
• DATE stores a calendar date without a time component.
• DECIMAL stores exact decimal values within defined precision and scale.
• FLOAT and DOUBLE store approximate floating-point values.
• Use the appropriate integer range for numeric data.
• Use suitable string types for names, email addresses, phone numbers, and descriptions.
• Use date types instead of arbitrary text for calendar dates.
• Choose data types according to the application's requirements, expected values, and constraints.

17. Official Documentation and Further Learning

Read the official MySQL 8.4 Data Types Documentation for details about integer, numeric, string, and date/time data types.

You can also refer to the specific documentation pages for Integer Types, DECIMAL and NUMERIC Types, FLOAT and DOUBLE Types, CHAR and VARCHAR Types, TEXT and BLOB Types, and DATE, DATETIME, and TIMESTAMP Types.

Conclusion

Understanding MySQL data types is an essential step in building reliable databases. INT is suitable for whole numbers, VARCHAR for variable-length text, TEXT for longer content, DATE for calendar dates, and numeric types such as DECIMAL, FLOAT, and DOUBLE for different kinds of numerical values.

Choosing the correct type helps prevent invalid data, supports accurate calculations, and makes database tables easier to maintain. Practice these examples and adapt them to real-world database projects.

Continue learning with the JustAcademy MySQL Training Course or Register for MySQL Course Demo.