MySQL "CREATE TABLE LIKE" include triggers

Question

I'm using MySQL 5.6 (5.6.29-76.2-56-log Percona XtraDB Cluster)

When I issue a CREATE TABLE LIKE the triggers on the tables are not copied - is it possible to get them on the new table as well?

Answer 1

The long and the short answer is no! This is yet another "non-feature" of MySQL. This construct is known more commonly as CTAS (CREATE TABLE AS.... SELECT) - the CREATE TABLE LIKE syntax is non-standard - like so much of MySQL!

MySQL's documentation argues here - that this lacuna is to ensure "flexibility" - you'd laugh if it wasn't so sad!

CREATE TABLE ... SELECT does not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible.

You can create them (indexes) by declaring them before the SELECT as follows

If you want to have indexes in the created table, you should specify these before the SELECT statement:

> mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

But you're out of luck for TRIGGERs, FUNCTIONs and STORED PROCEDUREs.

If this is important to you, you could try your luck with PostgreSQL - which has a table inheritance mechanism, whereby schema changes are propaged, but I can't see why a WITH INDEXES, WITH FUNCTIONS, WITH STORED PROCEDURES syntax couldn't be introduced.

If you put in a feature request (good luck with that! MySQL doesn't even have CHECK CONSTRAINTs yet), I'd be happy to file a "me-too" if you post the link back here for my attention.

The CREATE TABLE LIKE is a similarly crippled option - from here:

CREATE TABLE ... LIKE does not preserve any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table, or any foreign key definitions.

• user1822: A side note: Postgres does have an option to copy indexes when using create table like but there is no way functions can be included as there is no real direct dependency between a function and a table (like e.g. between a trigger and a table). But theoretically triggers (actually the trigger definition) _could be included though