Menu

MySQL CREATE TABLE: Syntax and Examples

Create MySQL tables with CREATE TABLE. See a practical InnoDB example with columns, defaults, AUTO_INCREMENT, primary and unique keys, and IF NOT EXISTS.

Updated on

Use MySQL’s CREATE TABLE statement to define a table’s columns, indexes, and constraints. The example below creates a database and an InnoDB table with an auto-incrementing primary key, a required unique SKU, a default price, and a creation timestamp.

CREATE DATABASE inventory;
USE inventory;

CREATE TABLE products (
    product_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    sku VARCHAR(64) NOT NULL,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (product_id),
    UNIQUE KEY uq_products_sku (sku)
) ENGINE = InnoDB;

After creating the table, inspect its definition with:

SHOW CREATE TABLE products;

CREATE TABLE syntax

This simplified form shows the common parts of a CREATE TABLE statement. MySQL supports additional column attributes, index types, table options, and constraints; see the complete MySQL 8.4 syntax.

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type [NOT NULL | NULL] [DEFAULT value] [AUTO_INCREMENT],
    ...,
    [table_constraint, ...]
) [ENGINE = engine_name];
  • The table is created in the current database. Select one with USE, or qualify the name as database_name.table_name.
  • Define each column with a name and data type, such as id BIGINT, name VARCHAR(100), or created_at TIMESTAMP.
  • Use NOT NULL when a value is required. If neither NULL nor NOT NULL is specified, MySQL treats the column as nullable.
  • Use DEFAULT to supply a value when an insert omits the column. The effective default depends on the data type and nullability; see MySQL data type default values.
  • An AUTO_INCREMENT integer column must be indexed, and a table can have only one. Omitting the column generates the next value; inserting NULL does so when the column is NOT NULL. Inserting 0 also generates a value unless NO_AUTO_VALUE_ON_ZERO is enabled. See MySQL’s AUTO_INCREMENT rules.
  • Add table constraints such as a PRIMARY KEY, FOREIGN KEY, CHECK, or UNIQUE key. MySQL enforces CHECK constraints starting with 8.0.16; earlier versions parse and ignore the limited CHECK syntax. See the MySQL 8.0.16 release notes.
  • ENGINE selects the storage engine. In MySQL 8.4, new tables use InnoDB by default unless the server is configured otherwise; InnoDB supports transactions and foreign keys.

Create a table only if it does not exist

IF NOT EXISTS suppresses the error when a table with that name already exists:

CREATE TABLE IF NOT EXISTS import_jobs (
    job_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    source_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

This clause does not compare or update the existing table’s definition. If the table exists with different columns or indexes, MySQL leaves it unchanged and issues a warning. For scripts that need to check the schema first, see how to check whether a MySQL table exists.

Create a table from another table

Use CREATE TABLE ... LIKE to create an empty table based on an existing table’s definition:

CREATE TABLE products_backup LIKE products;

Use CREATE TABLE ... SELECT when you want the query results inserted into a new table:

CREATE TABLE active_products AS
SELECT product_id, sku, name, price
FROM products
WHERE price > 0;

CREATE TABLE ... SELECT derives columns from the query and inserts its result rows. If the new table needs a particular primary key, index, or constraint, define it explicitly in the CREATE TABLE part or add it afterward; do not assume the query copies the source table’s complete definition.

Further reading