Fix Error with TYPE=HEAP for temporary tables in MySQL?

The TYPE=HEAP deprecated in newer MySQL versions. You can use ENGINE=HEAP instead of TYPE. Following is the syntax −

ENGINE=HEAP;

Let us first create a table. Here, we have set Engine=HEAP −

mysql> create TEMPORARY table DemoTable
   -> (
   -> StudentId int,
   -> StudentName varchar(30)
   -> )Engine = HEAP;
Query OK, 0 rows affected (0.00 sec)

Let us check the definition of table −

mysql> show create table DemoTable;

Output

This will produce the following output −

+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table
|
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DemoTable | CREATE TEMPORARY TABLE `DemoTable` (`StudentId` int(11) DEFAULT NULL,`StudentName` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)