MySQL Data Types and usage examples

MySQL, one of the world's most popular relational database management systems, is known for its flexibility and robustness. One key feature of MySQL is its diverse range of data types, which provide the means to store different types of information based on the data's nature and intended use.

Understanding these data types is paramount to efficient database design and management. In this article, we will cover the major MySQL data types, exploring their uses and presenting examples as codes.

Numeric Data Types

MySQL offers several numeric data types for different use cases. Some of the main ones are:

INT

This is the standard integer type, which stores whole numbers. Its range spans from -2147483648 to 2147483647.

Example:

SQL
REATE TABLE Customers (    CustomerID INT,    Name VARCHAR(50),    Age INT
);

Here, Age and CustomerID are INT, storing the age of customers and their unique identification numbers.

DECIMAL(M, N)

This is used for exact numeric values, with M representing the total number of digits and N denoting the number of digits after the decimal point.

Example:

SQL
CREATE TABLE Orders (    OrderID INT,    TotalPrice DECIMAL(7,2)
);

In this instance, TotalPrice is DECIMAL(7,2), meaning it can store a value with up to 7 digits in total, 2 of them after the decimal point.

FLOAT(M, D)

FLOAT is a floating-point number used for large numbers. M represents the total number of digits and D represents the number of digits after the decimal.

Example:

SQL
CREATE TABLE Products (    ProductID INT,    Weight FLOAT(5,2)
);

Here, Weight is FLOAT(5,2), which means it can store the weight of the product with a precision of up to 2 decimal places.

TINYINT

This is used to store small integers. TINYINT's range is from -128 to 127.

Example:

SQL
CREATE TABLE Users (    UserID INT,    IsActive TINYINT
);

In the Users table, IsActive is a TINYINT, storing either 0 (inactive) or 1 (active).


Date and Time Data Types

MySQL supports several date and time types to suit various needs:

DATE

The DATE type stores a date in the format 'YYYY-MM-DD'.

Example:

SQL
CREATE TABLE Employees (    ID INT,    Name VARCHAR(50),    DateOfBirth DATE
);

In this table, DateOfBirth is a DATE, storing the employees' dates of birth.

DATETIME

This type is used to store a date and time combination. The format is 'YYYY-MM-DD HH:MM:SS'.

Example:

SQL
CREATE TABLE Events (    EventID INT,    EventDateTime DATETIME
);

Here, EventDateTime is a DATETIME type, storing both the date and time of an event.

TIMESTAMP

The TIMESTAMP type is used to store a timestamp, automatically initialized and updated to the current date and time.

Example:

SQL
CREATE TABLE BlogPosts (    PostID INT,    Content TEXT,    LastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

In the BlogPosts table, LastUpdated is a TIMESTAMP, automatically recording the latest update time.

TIME

The TIME type stores time in hours, minutes, and seconds.

Example:

SQL
CREATE TABLE TimeCards (    CardID INT,    ClockInTime TIME,    ClockOutTime TIME
);

Here, ClockInTime and ClockOutTime are TIME types, storing when an employee clocks in and out.


String Data Types

MySQL also offers several types for storing text or string data:

CHAR(M)

CHAR is a fixed-length string. M indicates the maximum length.

Example:

SQL
CREATE TABLE Employees (    ID INT,    Gender CHAR(1)
);

In this table, Gender is CHAR(1), storing the gender of employees ('M' or 'F').

VARCHAR(M)

Unlike CHAR, VARCHAR is a variable-length string. M represents the maximum length.

Example:

SQL
CREATE TABLE Employees (    ID INT,    Name VARCHAR(50)
);

Here, Name is VARCHAR(50), allowing for employee names up to 50 characters long.

TEXT

The TEXT type is a string with a maximum length of 65,535 characters, ideal for lengthy content.

Example:

SQL
CREATE TABLE BlogPosts (    PostID INT,    Content TEXT
);

In the BlogPosts table, Content is TEXT, accommodating long blog post contents.

BLOB

BLOB is used to store binary large objects, such as images or files.

Example:

SQL
CREATE TABLE Files (    FileID INT,    File BLOB
);

Here, File is a BLOB, which can store binary data, such as a PDF file.

ENUM Type

The ENUM type is a string object with a value chosen from a list of permitted values defined at the time of column creation.

Example:

SQL
CREATE TABLE Tasks (    TaskID INT,    Status ENUM('Not Started', 'In Progress', 'Completed')
);

In the Tasks table, Status is an ENUM type, which can store either 'Not Started', 'In Progress', or 'Completed'.