Creating Tables in MySQL

Tables are the core storage structures in MySQL. The CREATE TABLE statement defines a table's columns, data types, and constraints. Good table design directly impacts query performance, data integrity, and how easy your database is to maintain over time.

Basic CREATE TABLE

At minimum, you need a table name and at least one column with a data type. The following example creates a simple users table with an auto-incrementing primary key.

CREATE TABLE users (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  username  VARCHAR(50)  NOT NULL,
  email     VARCHAR(100) NOT NULL UNIQUE,
  created_at DATETIME    DEFAULT CURRENT_TIMESTAMP
);

Common Data Types

Choosing the right data type saves storage and improves performance. Here are the most commonly used types in MySQL:

-- Numeric
INT, BIGINT, DECIMAL(10,2), FLOAT, DOUBLE, TINYINT

-- String
VARCHAR(255), CHAR(10), TEXT, MEDIUMTEXT, ENUM('a','b')

-- Date / Time
DATE, DATETIME, TIMESTAMP, TIME, YEAR

-- Binary / Other
BLOB, JSON, BOOLEAN (alias for TINYINT(1))

Constraints

Constraints enforce rules at the database level. They guarantee data quality regardless of which application or script writes to the table.

CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT          NOT NULL,
  total       DECIMAL(10,2) NOT NULL CHECK (total >= 0),
  status      ENUM('pending','paid','shipped','cancelled')
                DEFAULT 'pending',
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (customer_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Storage Engines

MySQL supports multiple storage engines. InnoDB is the default and supports transactions, foreign keys, and row-level locking. You rarely need to change the engine, but you can specify it explicitly.

CREATE TABLE sessions (
  id         CHAR(36) PRIMARY KEY,
  user_id    INT NOT NULL,
  expires_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Modifying Tables

After creation, use ALTER TABLE to add columns, change data types, or add indexes. DROP TABLE removes a table entirely. Both should be used with care in production.

-- Add a column
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) AFTER email;

-- Change a column type
ALTER TABLE users
MODIFY COLUMN username VARCHAR(100) NOT NULL;

-- Remove a table
DROP TABLE IF EXISTS temp_imports;

Best Practices

Always use utf8mb4 as your character set to support the full Unicode range. Define NOT NULL wherever possible to avoid ambiguous NULL values. Add foreign keys to enforce referential integrity. Use meaningful table and column names that describe the data they hold.