Copy column definition from one table to another?

Question

I want to copy a column (definition, not data) from one table to another. Add the column if it doesn't exist in destination table, or better yet, modify it accordingly if it exists.

Tried to google but everything seems to be about either copying the data rather than the definition, or about copying an entire table using CREATE TABLE ... LIKE ....

Thus far the closest I could find is this:

CREATE TABLE table2 AS 
SELECT field4, field7                -- only the columns you want
FROM table 
WHERE FALSE;                 -- and no data

But I need to copy columns to existing tables. So I tried:

ALTER TABLE table2 AS 
SELECT field4, field7                -- only the columns you want
FROM table 
WHERE FALSE;                 -- and no data

But apparently it refused to work.

Is there something like this?

ALTER TABLE table1 ADD COLUMN column2 LIKE table2.column5

Any way to achieve this in MySQL?

Answer 1

The best way is to Dump the table and then you can use ALTER command to create the new table.

Dumping the table gives back the query to CREATE the table.

CREATE TABLE IF NOT EXISTS customer ( id int(11) NOT NULL AUTO_INCREMENT, customerId varchar(50) NOT NULL, firstName varchar(30) NOT NULL, middleName varchar(30) DEFAULT NULL, lastName varchar(30) CHARACTER SET utf8 COLLATE utf8_estonian_ci DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

USE the same to ALTER an existing table, (say customerinfo)

`ALTER TABLE customerinfo ADD COLUMN (
  customerId varchar(50) NOT NULL,
  firstName varchar(30) NOT NULL,
  middleName varchar(30) DEFAULT NULL,
  lastName varchar(30) CHARACTER SET utf8 COLLATE utf8_estonian_ci DEFAULT NULL
)