MySQL Create Table

MySQL CREATE TABLE: Defining Database Schema

The CREATE TABLE statement defines a new database table, including column names, data types, constraints, and engine settings.

Syntax Overview

CREATE TABLE IF NOT EXISTS table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    PRIMARY KEY (column1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Practical SQL Code Example

CREATE TABLE IF NOT EXISTS orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_status VARCHAR(20) DEFAULT 'PENDING',
    total_amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Best Practices & Gotchas

• Use utf8mb4 Encoding: Always use utf8mb4 character set to support full Unicode (including emojis and international text).
• Set Appropriate Constraints: Enforce NOT NULL on required fields to maintain data integrity.

Self-Check Challenge

Why is utf8mb4 preferred over plain utf8 in modern MySQL database configurations?