MySQL CREATE TABLE 语句详解

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 TABLE keywords.  The table name must be unique within a database. The IF NOT EXISTS  is 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 use IF NOT EXISTS in every CREATE TABLE statement 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_list section. 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 engine clause. 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_name specifies the name of the column. Each column always associates with  a specific data type and the size e.g.,   VARCHAR(255). 

• The  NOT NULL or NULL indicates that the column accepts NULL value or not.

• The DEFAULT value is used to specify the default value of the column.

• The AUTO_INCREMENT indicates that the value of column is increased by one whenever a new row is inserted into the table. Each table has one and only one AUTO_INCREMENT column.

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.

Related Tutorials

• MySQL Copy Table

• MySQL DROP TABLE - Removing Existing Tables

• Changing Table Structure Using MySQL ALTER TABLE

原文链接：http://outofmemory.cn/mysql/mysql-create-table