i knowCREATE TABLE new LIKE old;,but it can't copy fields to a existing table
i want to add the field,type and collation to an existing table
is there any simple way to do it?

CodePudding user response：

You would have to SHOW CREATE TABLE old then copy & paste the columns you want to add to your existing table, and use them in an ALTER TABLE statement . You can use ADD COLUMN to add multiple columns in one statement.

There's no shortcut syntax for this. It's unlikely that you would want to copy all the columns. For example, you can't copy columns that conflict with column names in the existing table.

There are edge cases, like what if a column has NOT NULL but no DEFAULT? You can't add that to an existing table unless the table is empty.

You just have to build the ALTER TABLE statement to suit what you want to do. This is similar to most other custom programming scenarios.