mysql copy column data type to another table

Question

Is there a way to copy a column's structure from an already populated table to a new table which is empty? I'm only asking about copying the structure without the 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 just one column but with the same data type as the animals' column type.

Is there a way to combine the SHOW COLUMNS/FIELDS with the CREATE TABLE or create the table with a column that has some kind of 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 haven't had any luck finding the answer to it

Answer 1

If you wish to copy the data:

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

If you wish 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 original table:

CREATE TABLE new_tbl LIKE orig_tbl;

The copy is created using the same version of the table storage format as the original table. The SELECT privilege is required on the original table.

Documentation

If you want to copy the structure and the 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