How to copy MySQL table structure to a table in memory?

Question

How to create a table in memory that is identical to a "regular" table? I want a copy without indexes and restrictions. Pure SQL (no external tools).

This works (however indexes are created):

create table t_copy (like t_original) 

It does not mean:

 create table t_copy (like t_original) engine=memory 

Answer 1

 CREATE TABLE t_copy ENGINE=MEMORY SELECT * FROM t_original; 

I really tried it, it works !!!

 mysql> show create table queue\G *************************** 1. row *************************** Table: queue Create Table: CREATE TABLE `queue` ( `ndx` int(11) NOT NULL AUTO_INCREMENT, `folderid` int(11) NOT NULL, PRIMARY KEY (`ndx`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 1 row in set (0.02 sec) mysql> create table queue_memory engine=MEMORY as select * from queue; Query OK, 0 rows affected (0.05 sec)<BR> Records: 0 Duplicates: 0 Warnings: 0<BR> lwdba@localhost (DB test) :: show create table queue_memory\G *************************** 1. row *************************** Table: queue_memory Create Table: CREATE TABLE `queue_memory` ( `ndx` int(11) NOT NULL DEFAULT '0', `folderid` int(11) NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 

Give it a try !!!

Answer 2

This should give you an empty table without any indexes.

 create table t_copy select * from t_original where 0 

Answer 3

To create a table in memory with the same structure AND indices as the first table, try:

 create table t_copy like t_original; alter table t_copy engine=mem;