How to copy MySQL table structure to table in memory?

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

This works (however indexes are created):

create table t_copy (like t_original)

This doesn't:

create table t_copy (like t_origin开发者_如何学Pythonal) engine=memory

CREATE TABLE t_copy ENGINE=MEMORY SELECT * FROM t_original;

I actually 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 !!!

This should get you an empty table without any indexes.

create table t_copy
    select *
        from t_original
        where 0

To create an in-memory table with the same structure AND indexes as the first table, try:

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