4.6 CREATE TABLE

Use the CREATE TABLE statement to create a new table. A table's definition includes its name and a list of columns, each of which has a name and a definition. The table definition may also include index definitions.

This section describes basic CREATE TABLE syntax using simple column definitions. More information on column datatypes and properties can be found in section 4.10, "Column Types."

To create a table, give its name followed by a list of column definitions within parentheses:

CREATE TABLE table_name (definition1, definition2, ...);

In the simplest case, a table contains only one column. The following statement creates a table named t with a single column named id that will contain INT (integer) values:

CREATE TABLE t (id INT);

A column definition may include options to define the column data more precisely. For example, to disallow NULL values in the column, include NOT NULL in the definition:

CREATE TABLE t (id INT NOT NULL);

More complex tables have multiple columns, with the column definitions separated by commas. The following table definition includes, in addition to the id column, two 30-byte character columns for storing last names and first names, and a column for storing date values. All columns are declared NOT NULL to indicate that they require non-NULL values.

CREATE TABLE t

(

    id         INT NOT NULL,

    last_name  CHAR(30) NOT NULL,

    first_name CHAR(30) NOT NULL,

    d          DATE NOT NULL

);

Every table must belong to a database. That is, you cannot create a table that is not located within some database. If the table named in the CREATE TABLE statement isn't qualified with a database name, the table is created in the default database. To indicate explicitly where to create the table, you can qualify the table name with the name of the desired database, using db_name.table_name syntax. For example, if you want to create a table called mytable in the test database, write the CREATE TABLE statement like this:

CREATE TABLE test.mytable (i INT);

The qualified identifier syntax is helpful when there's no default database or when some other database is currently selected as the default. (If test happens to be the default database, the statement still works. In that case, the database name is unnecessary but harmless.)

When you create a table, you can provide index definitions in addition to the column definitions. Indexes are useful for increasing query performance by reducing lookup time. Here's a simple example that includes two index definitions. The first creates an index on the id column and requires each id value to be unique. The second index definition creates a two-column index on the last_name and first_name columns of the table:

CREATE TABLE t

(

    id         INT NOT NULL,

    last_name  CHAR(30) NOT NULL,

    first_name CHAR(30) NOT NULL,

    UNIQUE (id),

    INDEX (last_name, first_name)

);

Section 4.9, "Creating and Dropping Indexes," discusses index creation further.

If you try to create a table that already exists, an error occurs. If you simply want to ensure that the table exists, add an IF NOT EXISTS clause to the statement:

CREATE TABLE IF NOT EXISTS t (i INT);

Note, however, that MySQL does not perform any check on the table structure when you add this clause. In particular, MySQL will issue no warning if a table with the given name exists but has a structure different from the one you've defined in the CREATE TABLE statement.

A temporary table can be created by adding the keyword TEMPORARY to the CREATE TABLE statement:

CREATE TEMPORARY TABLE t (i INT);

Temporary tables exist only for the duration of the current connection. The server drops temporary tables when you disconnect, if you haven't already dropped them explicitly. This is convenient because you need not remember to remove the table yourself. A temporary table is visible only to the client that creates it, so different clients can create temporary tables in the same database, using the same name, without conflicting with one another.