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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityThe 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 TRIGGER
s, FUNCTION
s and STORED PROCEDURE
s.
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 CONSTRAINT
s 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.
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
Dec 21, 2016 at 7:15