MySQL Data Types and Constraints

35 min

MySQL provides a comprehensive set of data types for storing different kinds of data efficiently. Data types include numeric types (INT, DECIMAL, FLOAT), string types (VARCHAR, TEXT, CHAR), date and time types (DATE, DATETIME, TIMESTAMP), and specialized types (JSON, ENUM, SET). Choosing appropriate data types is essential for efficient storage, performance, and data integrity. Understanding data types enables you to design optimal database schemas.

Numeric types include integers (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT) with optional UNSIGNED modifier, fixed-point decimals (DECIMAL, NUMERIC) for precise numeric values, and floating-point types (FLOAT, DOUBLE) for approximate values. Integer types vary in storage size and range. DECIMAL provides exact precision for financial data, while FLOAT and DOUBLE provide approximate values with larger ranges. Understanding numeric types enables appropriate data storage.

String types include fixed-length CHAR, variable-length VARCHAR, and text types (TEXT, MEDIUMTEXT, LONGTEXT). CHAR is faster for fixed-length strings but wastes space for shorter values. VARCHAR is more space-efficient for variable-length strings. TEXT types are for large text data. Understanding string types enables efficient text storage and retrieval.

Date and time types include DATE (date only), TIME (time only), DATETIME (date and time), TIMESTAMP (automatic timestamp), and YEAR. TIMESTAMP automatically updates on row modification, making it useful for tracking changes. DATETIME provides a larger range but doesn't have timezone awareness. Understanding date/time types enables proper temporal data handling.

Constraints enforce data integrity rules. PRIMARY KEY ensures unique identification, FOREIGN KEY maintains referential integrity, NOT NULL prevents null values, UNIQUE ensures uniqueness, CHECK validates values, and DEFAULT provides default values. Constraints prevent invalid data from being stored, ensuring database consistency. Understanding constraints enables robust database design.

Additional data types include JSON for storing JSON documents, ENUM for predefined value lists, SET for multiple value selections, and spatial types (GEOMETRY, POINT) for geographic data. These specialized types enable storing complex or structured data efficiently. Understanding all data types enables you to choose the most appropriate type for each field.

Key Concepts

MySQL supports various data types for different data kinds.
Numeric types include integers, decimals, and floating-point numbers.
String types include CHAR, VARCHAR, and TEXT for text data.
Date/time types handle temporal data with various precision levels.
Constraints enforce data integrity and validation rules.

Learning Objectives

Master

Understanding MySQL data types and their use cases
Creating tables with appropriate data types
Implementing constraints for data integrity
Choosing optimal data types for performance

Develop

Understanding database schema design
Designing efficient database structures
Ensuring data integrity through constraints

Tips

Use INT for integers, DECIMAL for precise numbers, VARCHAR for variable strings.
Use TIMESTAMP for automatic date tracking: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
Add constraints: PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK.
Use appropriate data type sizes: don't use BIGINT when INT suffices.

Common Pitfalls

Using wrong data types, wasting storage or causing precision issues.
Not using constraints, allowing invalid data to be stored.
Using VARCHAR(255) for all strings, not optimizing for actual needs.
Not understanding TIMESTAMP vs DATETIME differences, causing timezone issues.

Summary

MySQL provides comprehensive data types for various data kinds.
Choosing appropriate data types is essential for efficiency and integrity.
Constraints enforce data integrity and prevent invalid data.
Understanding data types enables optimal database design.

Exercise

Create a table with various data types and constraints.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    stock INT UNSIGNED DEFAULT 0,
    category ENUM('electronics', 'clothing', 'books') NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_category (category),
    INDEX idx_price (price)
);

-- Insert sample data
INSERT INTO products (name, description, price, stock, category) VALUES
('Laptop', 'High-performance laptop', 999.99, 50, 'electronics'),
('T-Shirt', 'Cotton t-shirt', 19.99, 100, 'clothing'),
('Programming Book', 'Learn SQL', 29.99, 25, 'books');

Exercise Tips

Use AUTO_INCREMENT for primary keys: id INT AUTO_INCREMENT PRIMARY KEY.
Use CHECK constraints: price DECIMAL(10,2) CHECK (price > 0).
Use ENUM for fixed value lists: category ENUM('electronics', 'clothing', 'books').
Use DEFAULT values: is_active BOOLEAN DEFAULT TRUE.

MySQL Data Types and Constraints