How to create a temporary table using the MEMORY engine like another table-CodePudding

I need a temporary table with the same layout as existing one. This throws a syntax error at the engine line:

create table t
like r
engine = MEMORY

Or is this not possible?

CodePudding user response：

Create temptable like existing table then alter its engine. Remember that some column datatypes (TEXT/BLOB/JSON) are not supported by MEMORY engine.

CREATE TABLE test1 (id SERIAL PRIMARY KEY, val VARCHAR(255));

CREATE TEMPORARY TABLE test2 LIKE test1;

SHOW CREATE TABLE test2;

Table	Create Table
test2	CREATE TEMPORARY TABLE `test2` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `val` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

ALTER TABLE test2 Engine = MEMORY;

SHOW CREATE TABLE test2;

Table	Create Table
test2	CREATE TEMPORARY TABLE `test2` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `val` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci