What are MySQL Data Types?

In MySQL, data types define the type of data a column can hold. They are essential for structuring and optimizing your database. The choice of data type affects performance, storage, and accuracy.

Types of MySQL Data Types

MySQL data types are broadly categorized into:

  1. String Data Types
  2. Numeric Data Types
  3. Date and Time Data Types

1. String Data Types

Data TypeDescriptionExample
CHAR(size)Fixed-length string. Default size is 1.'A', 'Hello'
VARCHAR(size)Variable-length string. Max size: 65535 bytes.'Database', 'MySQL'
TEXTLarge text, up to 65,535 characters.'This is a long text.'
ENUM(values)A string object with predefined values.'small', 'medium'
BLOBBinary large objects for storing binary data. 

Example

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    bio TEXT
);

2. Numeric Data Types

Data TypeDescriptionExample
TINYINT(size)Integer from -128 to 127.1, -50
SMALLINT(size)Integer from -32,768 to 32,767.32000
INT(size)Integer from -2,147,483,648 to 2,147,483,647.1000000
BIGINT(size)Large integer values.9223372036854775807
DECIMAL(size, d)Exact decimal values.12.345
FLOAT(size, d)Approximate floating-point numbers.12.34

Example

CREATE TABLE products (
    product_id INT AUTO_INCREMENT,
    price DECIMAL(10, 2),
    stock TINYINT
);

3. Date and Time Data Types

Data TypeDescriptionExample
DATEStores date values. Format: YYYY-MM-DD.'2024-12-26'
DATETIMEStores date and time. Format: YYYY-MM-DD HH:MM:SS.'2024-12-26 15:30:00'
TIMESTAMPStores timestamp values.'2024-12-26 15:30:00'
TIMEStores time values. Format: HH:MM:SS.'15:30:00'
YEARStores year values. Format: YYYY.'2024'

Example

CREATE TABLE events (
    event_id INT,
    event_date DATE,
    event_time TIME,
    created_at TIMESTAMP
);

Choosing the Right Data Type

  1. Match Data Type to Data: Use VARCHAR for flexible strings, INT for numbers, and DATE for dates.
  2. Optimize Storage: Avoid larger data types if smaller ones suffice. For example, use TINYINT instead of INT for small values.
  3. Plan for Growth: Anticipate future needs. If a VARCHAR(50) might exceed its limit, choose VARCHAR(100).

Best Practices

Use ENUM for Limited Choices
 
CREATE TABLE shirts (
    size ENUM('small', 'medium', 'large')
);

Avoid NULL Where Possible
Use NOT NULL to enforce data integrity.

Index Numeric and Date Columns
Indexing improves query performance, especially for numeric and date columns.

Choose Fixed vs. Variable Length Wisely
Use CHAR for fixed-length data (e.g., codes) and VARCHAR for variable-length data.

Minimize Usage of TEXT and BLOB
Use TEXT and BLOB only when necessary, as they require special handling.

MySQL Data type

Quiz: Test Your Knowledge