MySQL CREATE TABLE: Syntax, Columns and Examples

A table is where your data lives. CREATE TABLE defines its name, its columns and the rules for them.

query.sql

SQL

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DESCRIBE users;

Output

OUTPUT

+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| id         | int          | NO   | PRI | NULL              | auto_increment    |
| name       | varchar(100) | NO   |     | NULL              |                   |
| email      | varchar(255) | NO   | UNI | NULL              |                   |
| is_active  | tinyint(1)   | NO   |     | 1                 |                   |
| created_at | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
5 rows in set

The shape of the statement

Text

PLAIN

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type [column rules],
    column_name data_type [column rules],
    ...
    [table-level rules]
) [table options];

Each column line has a name, a data type, and optional rules:

Piece	Meaning
`INT`, `VARCHAR(100)`, `DATETIME`	The data type (Data Types chapter)
`NOT NULL`	The column must always have a value
`DEFAULT 1`	Value used when you do not provide one
`AUTO_INCREMENT`	MySQL numbers new rows automatically (AUTO_INCREMENT)
`PRIMARY KEY`	The unique ID of each row
`UNIQUE`	No two rows may have the same value

Use it

query.sql

SQL

1
2

INSERT INTO users (name, email) VALUES ('Asha', 'asha@example.com');
SELECT id, name, is_active, created_at IS NOT NULL AS has_created_at FROM users;

Output

OUTPUT

+----+------+-----------+----------------+
| id | name | is_active | has_created_at |
+----+------+-----------+----------------+
|  1 | Asha |         1 |              1 |
+----+------+-----------+----------------+
1 row in set

You gave only name and email. MySQL filled in id, is_active and created_at from the rules.

IF NOT EXISTS

query.sql

SQL

1
2

CREATE TABLE IF NOT EXISTS users (id INT);
SHOW WARNINGS;

Output

OUTPUT

+-------+------+------------------------------+
| Level | Code | Message                      |
+-------+------+------------------------------+
| Note  | 1050 | Table 'users' already exists |
+-------+------+------------------------------+
1 row in set

The table already exists, so nothing happened except a note. Be careful: IF NOT EXISTS does not check that the structure is the same. If the old table has different columns, you now have a table that does not match your script.

Table-level rules

You can write a primary key, a unique key or an index after the columns:

query.sql

SQL

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrolled_on DATE NOT NULL,
    PRIMARY KEY (student_id, course_id),
    KEY idx_course (course_id)
);
SHOW INDEX FROM enrollments;

Output

OUTPUT

+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table       | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| enrollments |          0 | PRIMARY    |            1 | student_id  | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| enrollments |          0 | PRIMARY    |            2 | course_id   | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| enrollments |          1 | idx_course |            1 | course_id   | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set

A composite primary key (two columns) says that each student can enrol in each course only once.

Table options

query.sql

SQL

CREATE TABLE audit_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(50) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'Who did what';

You rarely need to set ENGINE, because InnoDB is the default and the right choice (Storage Engines). The character set and collation are worth stating so that the table does not depend on server settings.

A checklist for a good table

Give it a primary key, almost always an auto-incremented integer.
Choose the smallest suitable type for each column.
Mark columns NOT NULL unless “unknown” is a real state.
Add a UNIQUE rule for values that must not repeat (emails, usernames, SKUs).
Name things in lowercase_with_underscores.
Keep timestamps (created_at, updated_at) on tables that matter.

lightbulb

Keep CREATE TABLE statements in files

Store your table definitions in version control as SQL files or migrations, not only in the database. A teammate (or you, in six months) will need to rebuild the schema from scratch.