Mysql copy column data type to another table

Question

Is there a way to copy the column structure from an already filled table to a new table that is empty? I only ask for a copy structure without data

Example: We have a table

CREATE TABLE `animals` ( `animal` varchar(11) NOT NULL, `food` varchar(11) NOT NULL, PRIMARY KEY (`animal`) ) ENGINE=InnoDB INSERT INTO `animals` (`animal`, `food`) VALUES ('cat', 'chips'), ('dog', 'bones'), ('shark', 'ppl'); 

And a new table called predators , for which I want to make only one column, but with the same data type as the column type for animals.

Is there a way to combine SHOW COLUMNS / SQUARE with CREATE TABLE or create a table with a column that has some type like VARCHAR (17) and then ALTER change it to the same type as the animal column?

I know this is a simple question, but I was not lucky to find an answer to it

Answer 1

If you want to copy data:

 INSERT INTO newTable (col1, col2) SELECT col1, col2 FROM otherTable 

If you want to copy the table structure:

Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the source table:

 CREATE TABLE new_tbl LIKE orig_tbl; 

A copy is created using the same version of the table storage format as the original table. The source table requires the SELECT privilege.

Documentation

If you want to copy structure and data:

 CREATE TABLE animals2 AS SELECT * FROM animals ; 

And if you want to copy the structure (but not all columns) without data:

 CREATE TABLE animals2 AS SELECT animal -- only the columns you want FROM animals WHERE FALSE; -- and no data