CREATE TABLE LIKE (CTL)
Duplicating a table actually contains three phases, if you're using CREATE TABLE LIKE (CTL) clause:
Metadata Duplication
Metadata duplication means copying the structure from the source table to the target table, but with no data.
mysql> CREATE TABLE b LIKE a;
The syntax is CREATE TABLE target LIKE source;
Data Duplication
Data duplication is doing the plain old INSERT job. A real data copying process.
mysql> INSERT INTO b SELECT * FROM a;
The data of the whole table is copying from table a to table b by INSERT INTO.
Constraints and Triggers Duplication
Constraints and Triggers duplication means that you have to add constraints (like UNIQUE and FOREIGN KEY) and triggers back to the target table by yourself if any, but with different trigger names.
mysql> CREATE TRIGGER different_trigger_name BEFORE INSERT ON b FOR EACH ROW begin ...;
CREATE TABLE AS SELECT (CTAS)
Don't mix up CTL with CTAS (CREATE TABLE AS SELECT), they are different in some features:
- CTAS can copy both metadata (partly) and data in one command, but CTL can only copy metadata. This could be an advantage, because some operations are not required data copied.
- Both CTAS and CTL create tables without adding constraints and triggers, this can make the copying process run smoothly. So you have to add them back by yourself.
- CTL can keep unconstrained indexes like NORMAL indexes.
You have to choose different names when you add triggers back to the target table in order to avoid any conflicts of naming. For further readings about naming conflicts of trigger, please refer to the following post: How to Resolve ERROR 1359 (HY000): Trigger already exists.