mysql copy table

[netrookie]

I'm trying to copy a table with sample records using this syntax: (mysql 4.1)

CREATE TABLE mytest.alert_reason_1 select * from urldb2.alert_reason_1 limit 10;

It works, but it creates the table as default MyISAM for all tables even if the table I copied from is an InnoDB table. I'm trying to avoid using mysqldump since I want a limited number of records copied from the source table. Also, I notice that PK, and other keys are not also copied.

Is there a way to copy the table as is?

Here are some ddl dumps to compare:

Original:

CREATE TABLE `alert_reason_1` (
  `ar_id` int(11) NOT NULL auto_increment,
  `a_id` int(10) unsigned NOT NULL default '0',
  `rule_name` varchar(20) NOT NULL default '',
  `user` varchar(128) default NULL,
  `vote_count` int(11) NOT NULL default '0',
  `vote_v_volumn` float default NULL,
  `comment` text,
  PRIMARY KEY  (`a_id`),
  UNIQUE KEY `ar_id` (`ar_id`),
  KEY `rule` (`rule_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Test:

CREATE TABLE `alert_reason_1` (
  `ar_id` int(11) NOT NULL default '0',
  `a_id` int(10) unsigned NOT NULL default '0',
  `rule_name` varchar(20) NOT NULL default '',
  `user` varchar(128) default NULL,
  `vote_count` int(11) NOT NULL default '0',
  `vote_v_volumn` float default NULL,
  `comment` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

[r937]

yes, there is a way to copy the table as is

first, you need the complete CREATE TABLE statement of the table being copied

then just change the name of the table and run the CREATE TABLE using the CREATE SELECT syntax...

CREATE TABLE [red]alert_reason_1_copy[/red]
( ar_id INTEGER NOT NULL AUTO_INCREMENT
, a_id INTEGER UNSIGNED NOT NULL default 0
, rule_name VARCHAR(20) NOT NULL default ''
, user VARCHAR(128) default NULL
, vote_count INTEGER NOT NULL default 0
, vote_v_volumn FLOAT DEFAULT NULL
, comment TEXT
, PRIMARY KEY (a_id)
, UNIQUE KEY ar_id (ar_id)
, KEY rule (rule_name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SELECT * FROM [red]alert_reason_1[/red];

r937.com | rudy.ca

 

[netrookie]

This worked for me, using LIKE:

CREATE TABLE mytest.alert_reason_1_TST LIKE urldb2.alert_reason_1;
INSERT INTO mytest.alert_reason_1_TST SELECT * FROM urldb2.alert_reason_1 limit 10;

mysql> show create table mytest.alert_reason_1_TST\G
*************************** 1. row ***************************
       Table: alert_reason_1_TST
Create Table: CREATE TABLE `alert_reason_1_TST` (
  `ar_id` int(11) NOT NULL auto_increment,
  `a_id` int(10) unsigned NOT NULL default '0',
  `rule_name` varchar(20) NOT NULL default '',
  `user` varchar(128) default NULL,
  `vote_count` int(11) NOT NULL default '0',
  `vote_v_volumn` float default NULL,
  `comment` text,
  PRIMARY KEY  (`a_id`),
  UNIQUE KEY `ar_id` (`ar_id`),
  KEY `rule` (`rule_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)