How to Create a Table in MySQL

Learn how to create a MySQL table, choose columns and data types, select a storage engine, and verify the result with SHOW TABLES and DESCRIBE.

A MySQL table is a structured collection of records. A database is a named organizational container that can hold tables and other database objects. Creating a database does not automatically create any tables inside it.

A table is organized into named columns, also called fields. Each column describes one kind of value, such as a name or date. Future rows, also called records, contain the actual values for each column.

Select the Database

Before creating a table, select the database that should contain it with the USE statement:

USE testdb;

The MySQL client normally responds with:

Database changed

USE makes testdb the active database for subsequent commands that use unqualified table names. You can check the selected database with:

SELECT DATABASE();

An alternative is to qualify the table name with the database name. For example, testdb.testtable identifies testtable inside testdb without relying on the currently selected database.

CREATE TABLE Syntax

The CREATE TABLE statement defines and creates a new table:

CREATE TABLE table_name (
  column_definition,
  column_definition
) ENGINE=storage_engine;

The table name identifies the new table. The column definitions appear inside parentheses and are separated by commas. Each definition normally contains a column name followed by a data type and optional constraints or attributes.

Element | Purpose | Example

table name | Identifies the table being created | testtable

column definition list | Lists the columns inside parentheses | name VARCHAR(128), year CHAR(4)

column name | Names one field in the table | surname

data type and optional length | Defines the kind and permitted size of values | VARCHAR(128)

ENGINE clause | Selects the table storage engine | ENGINE=InnoDB

Define Columns and Data Types

Every column needs a name and a data type. The type tells MySQL what form of value the column is expected to accept and influences validation, storage, comparisons, sorting, and other database behavior.

For example:

name VARCHAR(128)

This defines a column named name that stores variable-length character text with a maximum length of 128 characters. VARCHAR is useful when values can have different lengths, such as people's names, email addresses, or titles.

CHAR is a fixed-length character-string type. A four-character year representation is a simple example:

year CHAR(4)

CHAR(4) defines a capacity of four characters. It should not be described as universally equivalent to four bytes because storage details depend on the character set and encoding. Fixed-width values with a predictable length can be suitable for CHAR; values with variable lengths generally fit VARCHAR better.

Choose a type based on the nature and expected range of the data, not only on the example value. A complete birth date should normally use DATE, while a numeric identifier should use an integer type.

Nullable Columns

Unless you specify NOT NULL, a column accepts NULL. NULL means that a value is absent or unknown; it is different from an empty string and from zero.

nickname VARCHAR(128)

The column above is nullable. To require a value, add NOT NULL:

name VARCHAR(128) NOT NULL

Choose a Storage Engine

A storage engine is the MySQL table-handling implementation that controls how a table is stored and which features it supports.

You can select an engine explicitly with the ENGINE clause:

ENGINE=MyISAM

InnoDB is the normal default in current MySQL configurations when no engine is specified. It is the usual choice for transactional relational tables and supports features such as transactions and foreign keys. MyISAM is an older or specialized engine with different trade-offs and does not provide InnoDB's transaction and foreign-key capabilities. Use it deliberately rather than assuming it is the general-purpose default.

Create an Example Table

The following example creates a table named testtable in the selected testdb database. It has two variable-length text columns and a fixed four-character year column:

USE testdb;

CREATE TABLE testtable (
  name VARCHAR(128),
  surname VARCHAR(128),
  year CHAR(4)
) ENGINE=MyISAM;

If the statement succeeds, MySQL reports that the query completed and the table has been created. The explicit engine demonstrates the ENGINE clause. In a new design, consider using InnoDB unless you have a specific reason to use MyISAM.

Verify the New Table

List Tables

Use SHOW TABLES to list the tables in the currently selected database:

SHOW TABLES;

The result should include testtable. This confirms that the table belongs to the active database.

Inspect the Definition

Use DESCRIBE, often abbreviated as DESC, to inspect the columns:

DESCRIBE testtable;

A result resembles this:

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(128) | YES  |     | NULL    |       |
| surname | varchar(128) | YES  |     | NULL    |       |
| year    | char(4)      | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

Output column | Meaning

Field | The column name.

Type | The column's data type and length, when applicable.

Null | Whether the column can contain NULL; YES is expected when NOT NULL was not specified.

Key | Key information, such as whether the column is part of a primary or other index.

Default | The value MySQL uses when an insert does not provide a value, if a default is defined.

Extra | Additional attributes, such as automatic integer generation.

Type Selection at a Glance

Data characteristic | Recommended example type | Reason

Variable-length name text | VARCHAR(128) | Names may have different lengths.

Fixed four-character year code | CHAR(4) | A year represented exactly as four characters has predictable width.

Complete calendar date | DATE | MySQL can treat the value as a date for validation and date operations.

Numeric identifier | INT | Integer types represent numeric identifiers and support numeric comparisons.

A More Practical Person Table

The minimal example is useful for learning syntax, but a production table commonly needs a primary key. A primary key uniquely identifies each row. When complete birth dates are required, use DATE instead of a four-character year:

CREATE TABLE people (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  surname VARCHAR(128) NOT NULL,
  birth_date DATE
) ENGINE=InnoDB;

AUTO_INCREMENT generates a new integer identifier for inserted rows. The NOT NULL constraints require names, and InnoDB is explicitly selected. You can omit the engine clause when you want the server's configured default, which is typically InnoDB.

Table-Design Practices

Give every column a type that matches the nature and expected range of its values.
Add a primary key to tables that represent entities or records in a production application.
Use DATE when a complete date is needed. A CHAR(4) year cannot store month and day.
Choose explicit character set and collation settings when text comparison, sorting, language support, or Unicode behavior matters.
Use clear, valid identifiers. Avoid names that conflict with MySQL reserved words.
Use NOT NULL when missing values should not be allowed, and define defaults when an automatic value is appropriate.

Troubleshooting CREATE TABLE

No Database Is Selected

If MySQL reports that no database is selected, run USE with the intended database name and then repeat the statement:

USE testdb;

You can also qualify the table name, for example CREATE TABLE testdb.testtable (...).

Table Already Exists

A table with that name may already be present. Inspect it with:

SHOW TABLES;
DESCRIBE testtable;

Choose another name, remove the existing table only when that is safe, or use CREATE TABLE IF NOT EXISTS when silently keeping an existing table is the intended behavior.

Syntax Error in the Column List

Check that every column has a name and valid type, that definitions are separated by commas, and that the opening and closing parentheses match. Do not place an extra comma immediately before the closing parenthesis.

Unknown Storage Engine

The requested engine may be unavailable or disabled. List the engines supported by the server:

SHOW ENGINES;

Then choose an available engine, normally InnoDB.

Permission Denied

The connected account may lack the CREATE privilege for the selected database. Connect with an authorized account or ask an administrator to grant the required privilege.

Unexpected Text Behavior

Unexpected sorting, comparison, or character behavior can result from an unsuitable CHAR versus VARCHAR choice or from character set and collation settings. Review those settings and choose a type and length appropriate for the stored values.

Next Steps

After creating a table, you can add rows with INSERT statements, learn more about MySQL data types, and study primary keys. To change an existing definition, use ALTER TABLE.