content_image/735244.jpg
<img src='content_image/735244.jpg'>

## Important 

You cannot execute CREATE TABLE  or CREATE TABLE ... LIKE  while a LOCK TABLES  statement is in effect. 

CREATE TABLE ... LIKE  makes the same checks as CREATE TABLE . This means that if the current SQL mode is different from the mode in effect when the original table was created, the table definition might be considered invalid for the new mode and cause the statement to fail. 

For CREATE TABLE ... LIKE , the destination table preserves generated column information from the original table. 

For CREATE TABLE ... LIKE , the destination table preserves expression default values from the original table. 

For CREATE TABLE ... LIKE , the destination table preserves CHECK  constraints from the original table, except that all the constraint names are generated. 

CREATE TABLE ... LIKE  does not preserve any DATA DIRECTORY  or INDEX DIRECTORY  table options that were specified for the original table, or any foreign key definitions. 

If the original table is a TEMPORARY  table, CREATE TABLE ... LIKE  does not preserve TEMPORARY . To create a TEMPORARY  destination table, use CREATE TEMPORARY TABLE ... LIKE . 

Tables created in the mysql  tablespace, the InnoDB  system tablespace ( innodb_system ), or general tablespaces include a TABLESPACE  attribute in the table definition, which defines the tablespace where the table resides. Due to a temporary regression, CREATE TABLE ... LIKE  preserves the TABLESPACE attribute and creates the table in the defined tablespace regardless of the innodb_file_per_table setting. To avoid the TABLESPACE  attribute when creating an empty table based on the definition of such a table, use this syntax instead: 

## 13.1.20.4 CREATE TABLE ... SELECT Statement 

CREATE TABLE new_tbl  SELECT * FROM orig_tbl  LIMIT 0; 

CREATE TABLE ... LIKE  operations apply all ENGINE_ATTRIBUTE  and SECONDARY_ENGINE_ATTRIBUTE  values to the new table. 

You can create one table from another by adding a SELECT  statement at the end of the CREATE TABLE statement: 

CREATE TABLE new_tbl  [AS] SELECT * FROM orig_tbl ; 

MySQL creates new columns for all elements in the SELECT . For example: 

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (a), KEY(b)) -> ENGINE=InnoDB SELECT b,c FROM test2; 

This creates an InnoDB  table with three columns, a , b , and c . The ENGINE  option is part of the CREATE TABLE  statement, and should not be used following the SELECT ; this would result in a syntax error. The same is true for other CREATE TABLE  options such as CHARSET . 

Notice that the columns from the SELECT  statement are appended to the right side of the table, not overlapped onto it. Take the following example: 

mysql> SELECT * FROM foo; +---+ 

