CODEWITHSUNDEEP
mysqlmariadb
theking2
theking2

Reputation: 2841

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

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?

Upvotes: 0

Views: 163

Answers (1)

Akina
Akina

Reputation: 42728

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

fiddle

Upvotes: 1

Related Questions