MySQL: Create table from select with engine different from source table

I would like to copy an existing table data to a new table with a different engine:

CREATE TABLE `t_backup` (SELECT * FROM `t`) ENGINE=MyIsam;

​x
 
CREATE TABLE `t_backup` (SELECT * FROM `t`) ENGINE=MyIsam;​

But it prompts syntax error. I know I can write the following statements as a workaround:

CREATE TABLE `t_backup` (SELECT * FROM `t1`);
ALTER TABLE `t_backup` ENGINE = MyIsam;

 
CREATE TABLE `t_backup` (SELECT * FROM `t1`);ALTER TABLE `t_backup` ENGINE = MyIsam;​

I am just wondering if there’s a statment that can copy an existing table data to a new table with a different engine, in just one SQL statement (MySQL or MariaDB variant statements are also okay). Thanks!

Answer

You can use next syntax:

CREATE TABLE t_backup ENGINE=MyIsam AS SELECT * FROM t;

 
CREATE TABLE t_backup ENGINE=MyIsam AS SELECT * FROM t;​

MariaDB fiddle

Advertisement

Answer