Introduction
MySQL is an open-source relational database management system (RDBMS) developed by a Swedish company called MySQL AB. MySQL utilizes structured query language (SQL) for managing and manipulating data. It is widely used for various applications like Web Applications, E-commerce Platforms, Social Media and Content Platforms, Data Warehousing and Business Intelligence, etc.
MySQL was first released in 1995 and latter in the year 2008, MySQL AB was acquired by Sun Microsystems, which was subsequently acquired by Oracle Corporation in 2010. The Oracle organization continues to develop and support MySQL.
Data Types in MySQL
Data Types are classifications of data that determine its properties, such as its range of possible values and different operations that can be performed on data stored. Data types defines the type of variable stored in a variable and they instruct a computer system on how to handle and processing of data stored in a variable efficiently in order to prevent errors.
Common examples in data types include integers (whole numbers), strings (text), floats (numbers with decimals), and booleans (true or false values). Given below is the list of different data types used in MySQL, along with their description and examples.
1. Numeric Data Types
These data types are used to store numeric values like integer, decimal, float, etc. The numeric data type is subdivided into further categories, which include:
• Integer Type: The INT keyword is used for storing integer values. TINYINT is used for storing very small integer values ranging from (-128 to 127). SMALLINT is used for storing a small range of integer values ranging from (-32,768 to 32,767), MEDIUMINT is used for storing the integer values whose size is between small and big integer values.
The INT keyword is used for storing the integer values ranging from (-2147483648 to 2147483647). BIGINT is used for storing very large integer values. Given below is an example of Integer data types in MySQL.
Example
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Age TINYINT,
Salary SMALLINT
);
Explanation: The above MySQL query will create a table consisting of
rows and columns named Employees in the database. EmployeeID is
the unique identifier. Age uses a small data type (TINYINT) since ages
are small numbers. Salary uses SMALLINT, enough for mid range
salary of an employee. The above query will create a table that will
look like the structure given below:
• Decimal and Floating Types: Decimal stores exact numerical values. It is a fixed-point data type, meaning it stores values with the exact position of the decimal point in its value. The DECIMAL keyword is used for storing the decimal values.
o Float and Double can be defined as approximate decimal values that store approximate values using floating-point representation. Float is faster and uses less storage than DECIMAL, but it can introduce rounding errors because of binary representation. They are used in applications like scientific measurement, graphics, or large-scale calculations where exact precision is less important. Given below is an example of Decimal and float data types in MySQL.
Example
CREATE TABLE Demonstration (
ExactValue DECIMAL(7, 2)
ApproxValue Float
);
Explanation: The above MySQL query will create a new table named Demonstration consisting of rows and columns. The resultant table will have two columns named ExactValue and ApproxValue. DECIMAL (7,2) is a fixed-point decimal number that allows 7 digits of the maximum number with two digits after the decimal point. So, the column can store values up to 99999.99 (5 digits before the decimal point, 2 digits after).
Values are stored in ExactValue exactly as entered (no rounding error). Examples for decimal value according to the above query include 12.15, 12345.67, 99999.99, etc.
FLOAT stores a floating point number that stores numbers in an approximate binary representation. Examples for floating-point numbers according to the above query include 0.12345678, 3.1426867, etc.
2. String (Character) Data Types
The String data type is used to represent and store sequences of characters. These characters include letters, numbers, symbols, and spaces. String data type used to handle textual data. Given below is the list of different string data types.
• CHAR AND VARCHAR: CHAR keyword stores a fixed-length string (always stores n characters), whereas VARCHAR is also a keyword that stores a variable-length string (up to n characters). Given below is an example of CHAR and VARCHAR data types in MySQL
Example
CREATE TABLE User (
UserID INT,
UserName VARCHAR(55),
Gender CHAR(1)
);
Explanation: The above query creates a table named User in a database that will hold user-related information. In the table there will be a column named UserName for storing user names.
VARCHAR(55) means it can store up to 55 characters. Examples include “TpointTech Website”, “Technical Content”, “Programming Covered”.
There will be another column named Gender that stores a single character for storing the gender codes like ‘M’ for Male, ‘F’ for FEMALE, and ‘O’ for Other. Example include: ‘M’, ‘F’. Given below is the structure of the table that will look like using the above query.
3. Date and Time Data Types
These data types are used for storing dates, times, and timestamps. The date in MySQL is stored in a format of Year-Month-Date (YYYY-MM-DD), datetime is stored in a format of Year-Month-Date Hour:Minute:Seconds (YYYY-MM-DD HH:MM:SS), timestamp format is the same as datetime, but it auto-updated with current time, time is stored in the format of hour:Minute:Second (HH:MM:SS). Given below is an example of a date and time data type in MySQL.
Example
CREATE TABLE Orders (
OrderID INT,
OrderDate DATE,
DeliveryTime TIME,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Explanation
The above MySQL query will create a table named Orders that will store information about customer orders. OrderID stores a numeric id for each order that will be set as a primary key and often auto-incremented as a new id generated. Examples include 1, 2, 3.
• OrderDate stores the date when the order was placed in the column named DATE. Example: 2025-09-10.
• DeliveryTime Time: Column DeliveryTime stores the time of delivery, but not the date. Example: 11:42:00.
• CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP: Column named CreatedAt stores the date and time when the record was created. The keyword DEFAULT CURRENT_TIMESTAMP means MySQL will automatically insert the current date and time when a new row is created. Given below is the structure of the table that will look like using the above SQL query.
4. Boolean Data Type
MySQL does not have a real Boolean type. It instead is an alias for TINYINT(1) (0 = false, 1 = true). Given below is the MySQL query for the Boolean data type.
Example
CREATE TABLE Students (
StudentID INT,
Name VARCHAR(50),
IsActive BOOLEAN
);
Explanation: The above MySQL query will create a table named Students that will store information about students, including their ID, name, and whether they are active.
• StudentID INT: Column names StudentID is an integer (whole number) used as a unique identifier for each student (like roll number). Example: 1, 2, 3.
• Name VARCHAR(50): Column name Name stores the student’s name. VARCHAR(50) means it can hold up to 50 characters. Example: “Rahul”, “Zoro”, “Riya”.
• IsActive BOOLEAN: Column named IsActive to store whether a student is active or not. Example: TRUE/FALSE or 1/0 (1 = TRUE -> student is active, 0 = FALSE -> student is not active). Given below is the structure of the table that will look like using the above query.
Conclusion
This article gives a detailed description of the data types in MySQL, along with their example and table created using the MySQL queries. I hope this article has provided you with valuable information. If you are looking for more such article, I suggest you visit the Tpoint Tech Website, where you can find various articles in programming and other technology, along with interview questions, working codes, and an online compiler where you can run and test your code.