MySQL Specify Temp Table Engine When Using AS

Question

Is it possible to specify the Engine when using CREATE TEMPORARY TABLE xxx AS (SELECT ...) ? I tried but this fails:

CREATE TEMPORARY TABLE test AS (
SELECT myCol
FROM myTable
) ENGINE = MEMORY;

I've searched for an example of this but to no avail, using AS. :(

Answer 1

This is possible with simple syntax CREATE TABLE t AS SELECT ... - you will need to specify table columns in full statement, Sample:

CREATE TEMPORARY TABLE test (myCol varchar(100)) 
ENGINE=MEMORY 
AS 
SELECT myCol FROM myTable;

ref