Data Types

Every column in an SQL table must have a defined data type. The data type determines what kind of data can be stored in that column and how the database interprets it.

Data Type Categories

Data types in SQL can be grouped into three main categories: String, Numeric, and Date/Time.

String

String data types are used to store text or character data.

Data Type	Max. Length	Description
CHAR(n)	255 characters	Fixed-length string, padded with spaces if shorter
VARCHAR(n)	65,535 characters	Variable-length string, space efficient
TEXT	65,535 characters	For long text, such as articles or descriptions
TINYTEXT	255 characters	For short text
MEDIUMTEXT	16,777,215 characters	For large-capacity text
LONGTEXT	4,294,967,295 characters	For very long text

-- Example of using string data types
CREATE TABLE article (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    slug CHAR(50),
    content TEXT
);

The main difference between CHAR and VARCHAR is storage. CHAR always uses space for the full n characters even if the data is shorter, while VARCHAR only uses as much space as the actual data length requires.

Numeric

Numeric data types are used to store numbers, both integers and decimals.

Data Type	Value Range	Description
TINYINT	-128 to 127	Small integers
SMALLINT	-32,768 to 32,767	Medium integers
MEDIUMINT	-8,388,608 to 8,388,607	Mid-range integers
INT	-2,147,483,648 to 2,147,483,647	Standard integers
BIGINT	Very large	For very large numbers
FLOAT	Decimal	Standard-precision decimal numbers
DOUBLE	Decimal	High-precision decimal numbers
DECIMAL(p,s)	High precision	For financial data, p=total digits, s=decimal digits

-- Example of using numeric data types
CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    stock INT,
    berat FLOAT
);

Use DECIMAL for financial data such as prices or amounts of money, because this type guarantees exact precision without the rounding that occurs with FLOAT and DOUBLE.

Date and Time

These data types are used to store date and time information.

Data Type	Format	Description
DATE	YYYY-MM-DD	Date only
TIME	HH:MM:SS	Time only
DATETIME	YYYY-MM-DD HH:MM:SS	Date and time
TIMESTAMP	YYYY-MM-DD HH:MM:SS	Date and time, timezone-aware
YEAR	YYYY	Year only

-- Example of using date/time data types
CREATE TABLE attendance (
    id INT PRIMARY KEY,
    student_id INT,
    date DATE,
    entry_time TIME,
    exit_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

TIMESTAMP has an advantage over DATETIME because it automatically handles timezone differences. This is very useful for applications used in multiple regions.

MySQL Data Type Comparison Table

Here is a summary of data types commonly used in MySQL:

Category	Data Type	Example Use
String	VARCHAR(100)	Names, addresses, emails
String	CHAR(2)	Province codes (DK, JB)
String	TEXT	Articles, long comments
Numeric	INT	IDs, ages, stock
Numeric	DECIMAL(10,2)	Prices, salaries
Numeric	FLOAT	Weight, temperature
Date/Time	DATE	Birth dates, registration dates
Date/Time	DATETIME	Transaction times
Date/Time	TIMESTAMP	Record creation times

Tips for Choosing Data Types

Choose the smallest data type that is sufficient for your needs
Use VARCHAR for text with varying lengths
Use DECIMAL for financial data, not FLOAT
Consider future data growth when deciding the length of string columns

Summary

Choosing the right data types is very important for database performance and storage efficiency. By understanding the available data types, we can design an optimal table structure. In the next chapter, we will start creating our first database using the CREATE DATABASE statement.