8.2. Creating Tables

MySQL provides several ways to create tables:

• You can create an empty table, either by specifying its definition explicitly or by using the definition of an existing table.

• You can create a table populated from the result of a SELECT statement.

• You can create temporary tables.

The following discussion describes each of these table-creation methods. All of them use the CREATE TABLE statement in one form or another.

8.2.1. Creating Tables Using an Explicit Definition

A new table can be created from an explicit definition by using a CREATE TABLE statement that includes the table name and a list of columns. Each column has its own name and definition. The table definition may also include index definitions.

This section describes basic CREATE TABLE syntax using columns that have relatively simple definitions. For more information on the available data types and attributes for column definitions, see Chapter 5, "Data Types."

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

 CREATE TABLE table_name (column_definitions); 

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 attributes that 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); 

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); 

However, MySQL does not perform any check on the table structure when you add the IF NOT EXISTS clause. If a table with the given name exists but has a structure different from the one you've defined in the CREATE TABLE statement, MySQL will issue no warning.

More complex tables have multiple columns, with the column definitions separated by commas. The following table definition includes, in addition to an id column, two 30-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 is not 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 (column_definitions); 

Use of a database qualifier for the table name 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 speeding up queries by reducing record lookup time. Here's a simple table 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 8.6.2, "Creating Indexes," discusses index creation further.

8.2.2. Specifying the Storage Engine for a Table

Every table is created using one of the storage engines supported by the server. The set of storage engines available depends both on how the server was compiled when it was built and on the options used at startup:

• The MyISAM, MERGE, and MEMORY storage engines are always available.

• The InnoDB storage engine is included in all binary distributions.

• Additional storage engines are included in MySQL Max binary distributions.

To see which storage engines your server supports, use the SHOW ENGINES statement.

To specify a storage engine when you create a table, include an ENGINE = engine_name option in the CREATE TABLE statement. The following statement creates t as an InnoDB table:

 CREATE TABLE t (i INT) ENGINE = InnoDB; 

The ALTER TABLE statement also understands the ENGINE option. This allows you convert a table from one storage engine to another. The following statement changes t to use the MyISAM storage engine:

 ALTER TABLE t ENGINE = MyISAM; 

If a CREATE TABLE statement includes no ENGINE option, MySQL creates the table using the default storage engine, which is determined from the value of the storage_engine system variable. The built-in default value of storage_engine is MyISAM. However, depending on how MySQL was installed or configured, storage_engine might be set to a different storage engine. Make sure to double-check the setting to ensure that it is really what you expect.

The default storage engine can be changed at server startup or at runtime:

• The default storage engine can be specified at server startup with the --default-storage-engine option.

• For a running server, an administrator who has the SUPER privilege can change the default storage engine globally for all clients by setting the global storage_engine system variable:

   SET GLOBAL storage_engine = engine_name; 

  
  

  Setting the storage engine this way affects any client that connects after the statement executes. Clients that are connected at the time of statement execution are unaffected.

• Any client can change its own default storage engine by issuing either of these statements:

   SET SESSION storage_engine = engine_name; SET storage_engine = engine_name; 

  
  

If an ENGINE clause names a storage engine that is legal but not available, the server uses the storage_engine system variable to determine which engine to use. (A storage engine might be unavailable if it was not compiled in or was disabled at startup time.) If the server uses the default storage engine rather than the one specified in the CREATE TABLE statement, it issues a warning. For example, ISAM is a legal storage engine name, but is no longer supported in MySQL 5. (ISAM was the predecessor to MyISAM.) The following example shows what happens if the default storage engine is InnoDB and you issue a request to create an ISAM table:

 mysql> SET storage_engine = InnoDB; mysql> CREATE TABLE t (i INT) ENGINE = ISAM; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> SHOW WARNINGS\G *************************** 1. row ***************************   Level: Warning    Code: 1266 Message: Using storage engine InnoDB for table 't' 

8.2.3. Creating Tables Based on Existing Tables

MySQL provides two ways to create a table based on another table:

• CREATE TABLE ... SELECT creates a table and populates it from the result set returned by an arbitrary SELECT statement. In this case, the "other table" is the set of rows and columns retrieved by the SELECT.

• CREATE TABLE ... LIKE creates an empty table using the definition of another existing table.

CREATE TABLE ... SELECT can create a table that is empty or non-empty, depending on what is returned by the SELECT part. The following statements create a table that contains the entire content of the City table, a table that contains partial content from City, and an empty copy of City:

 CREATE TABLE CityCopy1 SELECT * FROM City; CREATE TABLE CityCopy2 SELECT * FROM City WHERE Population > 2000000; CREATE TABLE CityCopy3 SELECT * FROM City WHERE 0; 

Using the LIKE keyword with CREATE TABLE creates an empty table based on the definition of another table. The result is a new table with a definition that includes all column attributes and indexes of the original table. Suppose that table t looks like this:

 mysql> CREATE TABLE t     -> (i INT NOT NULL AUTO_INCREMENT,     -> PRIMARY KEY (i))     -> ENGINE = InnoDB; 

The result of CREATE TABLE ... LIKE differs from the result of using CREATE TABLE ... SELECT to create an empty table. Either of the following statements will create an empty copy of the table t:

 mysql> CREATE TABLE copy1 SELECT * FROM t WHERE 0; mysql> CREATE TABLE copy2 LIKE t; 

However, the resulting copies differ in the amount of information retained from the original table structure:

 mysql> SHOW CREATE TABLE copy1\G; *************************** 1. row ***************************        Table: copy1 Create Table: CREATE TABLE `copy1` (   `i` int(11) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql> SHOW CREATE TABLE copy2\G; *************************** 1. row ***************************        Table: copy2 Create Table: CREATE TABLE `copy2` (   `i` int(11) NOT NULL auto_increment,   PRIMARY KEY  (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 

The CREATE TABLE ... SELECT statement copies the column name and data type from the original table, but does not retain the PRIMARY KEY index information or the AUTO_INCREMENT column attribute information. The new table also uses the default storage engine, rather than the storage engine utilized by table t. The copy created with CREATE TABLE ... LIKE has none of these problems.

Some table attributes are not copied, even when issuing CREATE TABLE ... LIKE. The most notable examples are:

• If the original table is a MyISAM table for which the DATA DIRECTORY or INDEX DIRECTORY table options are specified, those options are not copied to the new table. The data and index files for the new table will reside in the database directory for the chosen database.

• Foreign key definitions in the original table are not copied to the new table. If you wish to retain the foreign key definitions, they must be re-specified with ALTER TABLE after creating the copy.

8.2.4. Using TEMPORARY Tables

Each storage engine in MySQL implements tables with a particular set of characteristics. One characteristic held in common by all storage engines is that by default they create tables that exist until they are removed with DROP TABLE. This behavior may be changed by using CREATE TEMPORARY TABLE rather than CREATE TABLE. A TEMPORARY table differs from a non- TEMPORARY table in the following ways:

• It's visible only to the client that created it and may be used only by that client. This means that different clients can create TEMPORARY tables that have the same name and no conflict occurs.

• A TEMPORARY table exists only for the duration of the connection in which it was created. The server drops a TEMPORARY table automatically when the client connection ends if the client has not already dropped it. This is convenient because you need not remember to remove the table yourself.

• A TEMPORARY table may have the same name as a non-TEMPORARY table. The non- TEMPORARY table becomes hidden to the client that created the TEMPORARY table as long as the TEMPORARY table exists.

• A TEMPORARY table can be renamed only with ALTER TABLE. You cannot use RENAME TABLE.

A table created with TEMPORARY is not the same thing as a MEMORY table. A MEMORY table is temporary in the sense that its contents are lost if you restart the server, but the table definition continues to exist in its database. A TEMPORARY table exists only while the client that created it remains connected, and then disappears completely. Given that a server restart necessarily involves termination of all client connections, it also results in removal of all TEMPORARY tables. Another difference is that a MEMORY table is available to any client that has permission to access it, not just to the client that created it.