VMware ESXi and vSphere Cluster Management

How to Create a Table in MySQL

Learn how to create MySQL tables with CREATE TABLE, columns, data types, constraints, storage engines, and verification commands.

A MySQL table is a structured database object that stores records. Each record is a row, and each attribute of that record is a column. A database is a logical container that can hold multiple tables and other MySQL objects.

Creating a database does not automatically create the tables your application needs. After selecting or creating a database, you define its tables with SQL statements such as CREATE TABLE.

This lesson assumes that you can connect to MySQL, access an existing database, run SQL statements, and end statements with semicolons.

How databases, tables, columns, and rows relate

  • Database: A logical container for tables and other objects.
  • Table: A structured object that organizes related records.
  • Column: A named field with a data type and optional constraints.
  • Row: One stored record in a table.

For example, a people table might have columns named person_id, name, surname, and birth_year. One row could contain the information for a single person.

Select the database for the new table

Use the USE statement to select an existing database:

USE testdb;

After this command succeeds, an unqualified table name is created inside testdb. In other words, the following statement creates testdb.testtable:

CREATE TABLE testtable (
  name VARCHAR(128),
  surname VARCHAR(128),
  birth_year CHAR(4)
);

You can also qualify the table name directly, which is useful when you do not want to change the current database:

CREATE TABLE testdb.testtable (
  name VARCHAR(128),
  surname VARCHAR(128),
  birth_year CHAR(4)
);

CREATE TABLE statement structure

CREATE TABLE is a DDL statement. DDL, or Data Definition Language, is SQL used to create or change database structures.

The general form is:

CREATE TABLE table_name (
  column_name data_type [column_attributes],
  column_name data_type [column_attributes]
) [ENGINE = engine_name];
  • CREATE TABLE tells MySQL to create a new table.
  • table_name is the name of the table.
  • The parenthesized definition contains comma-separated column declarations.
  • Each column declaration includes a column name and data type, followed by optional attributes.
  • The optional ENGINE clause selects the table storage engine.

Every column declaration except the final one is followed by a comma. The complete statement ends with a closing parenthesis and a semicolon.

Define columns and constraints

Every column needs a name and an appropriate data type. A data type declares what kind of value the column can store and helps MySQL handle storage, comparison, sorting, and validation.

Common column attributes

  • NULL permits the special NULL marker, which represents an unknown or absent value. Unless restricted by a constraint or configuration, columns commonly permit NULL.
  • NOT NULL requires a value for the column.
  • DEFAULT supplies a value when an INSERT statement does not provide one.
  • PRIMARY KEY identifies each row uniquely. A primary key can contain one column or a set of columns.
  • AUTO_INCREMENT generates successive numeric identifiers when a new row is inserted. It is commonly used with an integer primary key.
  • UNIQUE prevents duplicate values, subject to MySQL's handling of NULL values and the exact index definition.

For a real-world table, use a primary key even if a minimal teaching example does not need one. Required fields should usually be declared NOT NULL rather than relying only on application code.

Choose suitable data types and lengths

VARCHAR

VARCHAR stores variable-length character strings. It is suitable for values such as names, email addresses, and titles when the values can have different lengths.

name VARCHAR(128)

The 128 is the declared maximum character length for that column. It does not mean that every stored value occupies 128 characters.

CHAR

CHAR stores fixed-length character values. For example:

birth_year CHAR(4)

The 4 indicates a four-character representation. A fixed four-character field can suit a year stored as text in the form 1984, especially when preserving a fixed format is important.

However, a text representation is not always the best design. If the application needs numeric year validation, numeric comparisons, or date-aware behavior, YEAR may be more appropriate. If the value represents a complete calendar date, use DATE. Choose a type based on the value's meaning, expected range, formatting requirements, sorting behavior, and validation needs.

Other useful choices

  • YEAR is intended for year values. Confirm that its supported range and behavior match the application's requirements.
  • DATE stores a calendar date such as 1984-06-17.
  • INT stores whole numbers and is commonly used for identifiers or quantities.

Do not choose a type based on a display-width assumption. Character length and numeric storage behavior are separate design concerns.

Create a minimal people table

This introductory example uses two variable-length text columns and a fixed-width four-character birth-year field. It explicitly selects MyISAM so that the engine clause is visible:

USE testdb;

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

The table has three columns:

  • name VARCHAR(128) stores a variable-length given name up to 128 characters.
  • surname VARCHAR(128) stores a variable-length family name up to 128 characters.
  • birth_year CHAR(4) stores a four-character year representation.

For a new application, selecting MyISAM should be intentional. It is a legacy or specialized engine and does not provide the same transaction and foreign-key capabilities as InnoDB.

Choose a table storage engine

A MySQL storage engine controls important aspects of how a table stores and accesses data. Engine capabilities include transaction behavior, locking, indexes, crash recovery, and foreign-key support.

InnoDB is generally the default engine in modern MySQL installations when no engine is explicitly selected. It is the typical recommended choice for transactional tables and relationships enforced with foreign keys.

MyISAM is an older engine with different locking and transaction characteristics. It does not provide the same transaction and foreign-key capabilities as InnoDB, so it is usually not the first choice for new transactional designs.

Explicitly select an engine when a project requires a particular engine:

CREATE TABLE example_table (
  value_text VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

If you omit the clause, MySQL uses the server's configured default, which is generally InnoDB in modern installations:

CREATE TABLE default_engine_table (
  value_text VARCHAR(100) NOT NULL
);

Create a more practical modern table

A production-oriented people table should normally have a stable identifier and required name fields. This version uses an integer auto-increment primary key, a MySQL year type, and InnoDB:

CREATE TABLE people (
  person_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  surname VARCHAR(128) NOT NULL,
  birth_year YEAR
) ENGINE = InnoDB;
  • person_id uniquely identifies each row.
  • AUTO_INCREMENT generates an identifier when one is not supplied.
  • PRIMARY KEY makes the identifier the table's primary key.
  • NOT NULL requires both name fields.
  • birth_year YEAR represents a year as a year value rather than as four text characters.
  • ENGINE = InnoDB enables the usual transactional and foreign-key features associated with modern MySQL tables.

If the application needs a complete birth date, replace the year column with a suitable DATE column. If the business value is literally a formatted four-character code and must retain that representation, CHAR(4) can be reasonable.

Verify that the table was created

List tables in the selected database

Run SHOW TABLES to list tables in the current database:

SHOW TABLES;

Look for testtable or people in the result. This command checks the current database, so verify that the correct database is selected if the table is not listed.

Inspect column metadata with DESCRIBE

Use DESCRIBE, abbreviated as DESC, to inspect a table's columns:

DESCRIBE testtable;

The result commonly includes these fields:

  • Field: The column name.
  • Type: The data type and declared length or range.
  • Null: Whether the column permits NULL.
  • Key: Key information such as PRI for a primary key or UNI for a unique key.
  • Default: The default value, if one exists.
  • Extra: Additional attributes such as auto_increment.

View the complete generated definition

SHOW CREATE TABLE displays the SQL definition stored by MySQL:

SHOW CREATE TABLE testtable;

Use this command to compare your original statement with MySQL's generated definition. The result can reveal the selected engine, character set, collation, column types, indexes, and constraints.

Table inspection commands at a glance

  • SHOW TABLES;: Lists tables in the current database. Use it to confirm that a table exists.
  • DESCRIBE table_name;: Shows column metadata. Use it to check names, types, nullability, keys, defaults, and extra attributes.
  • SHOW CREATE TABLE table_name;: Shows the complete stored DDL. Use it to inspect the exact table definition and options.

Good schema-design practices

  • Use meaningful, consistent names such as birth_year instead of an ambiguous name such as value.
  • Use one naming convention consistently, such as lowercase names with underscores.
  • Avoid reserved words for table and column names. If an unavoidable identifier conflicts with a reserved word, quote it with MySQL identifier backticks, for example `order`, while recognizing that renaming is usually clearer.
  • Use NOT NULL for values that are required by the data model.
  • Give real-world tables a primary key.
  • Use UNIQUE where duplicate values would violate a business rule.
  • Choose character, numeric, and date types according to meaning and validation needs, not merely convenience.
  • Do not rely on display-width assumptions to control how character or numeric values are stored.
  • Prefer InnoDB for new transactional tables unless a specific, documented requirement calls for another engine.

Troubleshoot CREATE TABLE errors

No database selected

The statement did not identify a database and no database was selected. Run USE testdb; or create the table with a qualified name such as testdb.people.

Table already exists

A table with the requested name is already present. Run SHOW TABLES; and inspect the existing table. Choose another name or use a replacement workflow only after considering possible data loss. Do not drop a table casually.

SQL syntax error near a column definition

Check for a missing comma, unmatched parenthesis, invalid type syntax, or an identifier that conflicts with a reserved word. Compare the statement with the general CREATE TABLE form and ensure the final column is not followed by an unwanted comma.

Unknown storage engine

The requested engine may be unavailable or unsupported by the server. Check available engines:

SHOW ENGINES;

Use an available engine such as InnoDB when it matches the table's requirements.

Permission denied

The MySQL account may lack the CREATE privilege for the target database. Use an authorized account or ask an administrator to grant the required privilege.

The year field accepts or sorts unexpectedly

A character type may have been selected even though the application needs numeric or date-aware validation and behavior. Reconsider whether CHAR(4), YEAR, DATE, or another type represents the business value correctly.

Exam-relevant points

  • CREATE TABLE is a DDL statement that creates a table structure.
  • The selected database determines where an unqualified table name is created.
  • Column definitions are comma-separated and contain a name plus a data type.
  • VARCHAR(n) is variable-length text with a declared maximum length; CHAR(n) is fixed-length text.
  • InnoDB is generally the modern default and supports transactions and foreign keys.
  • SHOW TABLES lists tables, DESCRIBE shows column metadata, and SHOW CREATE TABLE shows the full stored definition.
  • A primary key uniquely identifies rows and is normally expected in a practical table design.

Summary

To create a MySQL table, select the target database, write CREATE TABLE, provide a name and comma-separated column definitions, choose suitable data types and constraints, and optionally specify an engine. Verify the result with SHOW TABLES, DESCRIBE, and SHOW CREATE TABLE. For most new transactional designs, use InnoDB and define a meaningful primary key.