CREATE TABLE Statement 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: 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. 13.1.20.4 CREATE TABLE ... SELECT Statement 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>  CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (a), KEY(b)) -> ENGINE=InnoDB SELECT b,c FROM test2; MySQL creates new columns for all elements in the  SELECT . For example: 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; +---+ 2570