When to CREATE TABLE AS SELECT versus CREATE TABLE LIKE?

Question

I can "copy" a table using:
CREATE TABLE copy LIKE original_table
and
CREATE TABLE copy as select * from original_table

In the latter case only the data are copied but not e.g primary keys etc.
So I was wondering when would I prefer using a select as?

Answer 1

These do different things. CREATE TABLE LIKE creates an empty table with the same structure as the original table.

CREATE TABLE AS SELECT inserts the data into the new table. The resulting table is not empty. In addition, CREATE TABLE AS SELECT is often used with more complicated queries, to generate temporary tables. There is no "original" table in this case. The results of the query are just captured as a table.

EDIT:

The "standard" way to do backup is to use . . . . backup at the database level. This backs up all objects in the database. Backing up multiple tables is important, for instance, to maintain relational integrity among the objects.

If you just want a real copy of a table, first do a create table like and then insert into. However, this can pose a challenge with auto_increment fields. You will probably want to drop the auto_increment property on the column so you can populate such columns.