++++

Data Engineering

May 2026×10 min read

Choose practical MySQL data types for numbers, strings, dates, booleans, and controlled values.

14. Data Types Deep Dive

Driptanil DattaSoftware Developer

Data Types Deep Dive

Data types define what a column can store. Good type choices make data smaller, faster, and harder to misuse.

Numeric types

Use integer types for whole numbers.

CREATE TABLE inventory (
  product_id INT PRIMARY KEY,
  quantity INT,
  reorder_level INT
);

Use DECIMAL for money and exact decimal values.

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  amount DECIMAL(10, 2)
);

FLOAT and DOUBLE are approximate. They are useful for scientific values, not exact currency.

String types

VARCHAR stores variable-length text up to a limit.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  email VARCHAR(100)
);

Use TEXT for longer text such as comments, descriptions, and article bodies.

CREATE TABLE product_reviews (
  review_id INT PRIMARY KEY,
  review_body TEXT
);

Date and time types

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Boolean handling

MySQL commonly represents booleans with TINYINT(1). TRUE is stored as 1 and FALSE as 0.

CREATE TABLE email_preferences (
  customer_id INT PRIMARY KEY,
  accepts_marketing BOOLEAN DEFAULT TRUE
);

ENUM

ENUM restricts a column to one of a fixed set of values.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  status ENUM("pending", "paid", "cancelled") NOT NULL
);

ENUM can be convenient for small stable lists. If the list changes often or needs extra metadata, use a lookup table instead.

Choosing types

• Choose the smallest type that still represents the business reality.
• Use DECIMAL for money.
• Use date/time types instead of storing dates as text.
• Keep identifiers consistent across related tables.
• Add constraints when the type alone does not fully describe the rule.