Summary: in this tutorial, we will show you how to create new tables in a particular database using MySQL CREATE TABLE statement.
MySQL CREATE TABLE syntax
In order to create a new table within a database, you use the MySQL CREATE TABLE statement. The CREATE TABLE statement is one of the most complex statement in MySQL.
The following illustrates the syntax of the CREATE TABLE statement in the simple form:
CREATE TABLE [IF NOT EXISTS] table_name( column_list ) engine=table_type
Let’s examine the syntax in greater detail:
First, you specify the name of table that you want to create after the
CREATE TABLEkeywords. The table name must be unique within a database. TheIF NOT EXISTSis an optional part of the statement that allows you to check if the table you are creating already exists in the database. If this is the case, MySQL will ignore the whole statement and it will not create any new table. It is highly recommended that you to useIF NOT EXISTSin everyCREATE TABLEstatement for preventing from an error of creating a new table that already exists.Second, you specify a list of columns for the table in the
column_listsection. Columns are separated by a comma (,). We will show you how to define columns in more detail in the next section.Third, you need to specify the storage engine for the table in the
engineclause. You can use any storage engine such as InnoDB, MyISAM, HEAP, EXAMPLE, CSV, ARCHIVE, MERGE FEDERATED or NDBCLUSTER. If you don’t declare the storage engine explicitly, MySQL will use InnoDBby default.
InnoDB became the default storage engine since MySQL version 5.5. The InnoDB table type brings many benefits of relational database management system such as ACID transaction, referential integrity and crash recovery. In the previous versions, MySQL used MyISAM as the default storage engine.
To define a column for the table in the CREATE TABLE statement, you use the following syntax:
column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT]
The most important components of the syntax above are:
The
column_namespecifies the name of the column. Each column always associates with a specific data type and the size e.g.,VARCHAR(255).The
NOT NULLorNULLindicates that the column acceptsNULLvalue or not.The
DEFAULTvalue is used to specify the default value of the column.The
AUTO_INCREMENTindicates that the value of column is increased by one whenever a new row is inserted into the table. Each table has one and only oneAUTO_INCREMENTcolumn.
If you want to set particular columns of the table as the primary key, you use the following syntax:
PRIMARY KEY (col1,col2,...)
Example of MySQL CREATE TABLE statement
Let’s practice with an example of creating a new table named tasks in our sample database as follows:
You can use the CREATE TABLE statement to create the tasks table as follows:
CREATE TABLE IF NOT EXISTS tasks ( task_id int(11) NOT NULL AUTO_INCREMENT, subject varchar(45) DEFAULT NULL, start_date DATE DEFAULT NULL, end_date DATE DEFAULT NULL, description varchar(200) DEFAULT NULL, PRIMARY KEY (task_id) ) ENGINE=InnoDB
In this tutorial, you have learned how to use MySQL CREATE TABLE to create a new tables within a database.