How to create a table in MySQL

Creating a table in MySQL involves using the Structured Query Language (SQL) to define the table's structure, including its name, columns, data types, and any constraints. This process is foundational for organizing data in a relational database. Let's explore the basics of creating tables in MySQL, advance into more complex examples, and finally look at the data types available for columns.

Basic Command Structure

To create a simple table in MySQL, you use the CREATE TABLE statement, followed by the table name and a list of columns, each with its own data type and optional constraints. Here is the basic syntax:

CREATE TABLE table_name (

    column1 datatype constraints,

    column2 datatype constraints,



Example: Creating a Simple Table

Suppose we want to create a table named Employees that contains three columns: EmployeeID, FirstName, and LastName. Here's how we could do it:

CREATE TABLE Employees (


    FirstName VARCHAR(100),

    LastName VARCHAR(100)


In this example, EmployeeID is an integer that automatically increments for each new record and acts as the primary key. FirstName and LastName are variable character fields up to 100 characters in length.

Advanced Use Cases

Adding Constraints

You can add various constraints to your table's columns to enforce data integrity. Some common constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and AUTO_INCREMENT. Here's an example that uses some of these constraints:



    OrderNumber VARCHAR(20) NOT NULL UNIQUE,

    EmployeeID INT,

    OrderDate DATE NOT NULL,

    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)


This Orders table includes a foreign key constraint that links EmployeeID to the EmployeeID in the Employees table, ensuring referential integrity.

Setting Default Values

You can also specify default values for columns using the DEFAULT keyword. This is useful for columns that should have a predetermined value if none is provided:



    ProductName VARCHAR(255) NOT NULL,

    Price DECIMAL(10, 2),



In this Products table, InStock is a Boolean column that defaults to TRUE if no value is specified.

Data Types

MySQL supports a wide range of data types. Choosing the right data type for each column is crucial for optimizing performance and storage. Here's a table of common MySQL data types:

Data TypeDescriptionExample Value
INTA medium-range integer12345
BIGINTA large-range integer123456789012
DECIMAL(M, N)A fixed-point number where M is the total digits and N is the digits after the decimal123.45
VARCHAR(L)A variable-length string (up to L characters)'Hello, World!'
TEXTA long text string, up to 65,535 characters'Long text...'
DATEA date, without time'2024-03-21'
DATETIMEA date and time combination'2024-03-21 15:30:00'
TIMESTAMPA timestamp, auto-updates to the current date and time'2024-03-21 15:30:00'
TIMEA time without date'15:30:00'
YEARA year in four-digit format2024
CHAR(L)A fixed-length string (up to L characters)'ABC'
TINYTEXTA tiny text string, up to 255 characters'Tiny text'
MEDIUMTEXTA medium-length text string, up to 16,777,215 characters'Medium text...'
LONGTEXTA long text string, up to 4,294,967,295 characters'Very long text...'
BLOBA binary large object, up to 65,535 bytes(binary data)
MEDIUMBLOBA medium-sized binary large object, up to 16,777,215 bytes(binary data)
LONGBLOBA large binary large object, up to 4,294,967,295 bytes(binary data)
TINYINTA very small integer85
SMALLINTA small-range integer1234
MEDIUMINTA medium-range integer, larger than SMALLINT123456
FLOATA single-precision floating point number123.45
DOUBLEA double-precision floating point number123.456789
BOOLEANA synonym for TINYINT(1), represents a true or false valueTRUE (or 1)
ENUMA string object that can only have one value, chosen from a list of possible values'small', 'medium', 'large'
SETA string object that can have 0 or more values, chosen from a list of possible values'red', 'green', 'blue'

This overview provides the foundational knowledge needed to start creating tables in MySQL. Experimenting with different data types and constraints will help you to effectively organize and manage your data.

Turn your SQL into Beautiful Dashboards

Create amazing KPI dashboards directly from your SQL database with Dashase

  • Create charts, tables, and widgets
  • Collaboration - Shared Dashboards
  • AI assisted query generation - GPT-4
  • Supports PostgreSQL, MySQL, SQL Server, and more

MySQL Snippets