Solved

clone a MySQL table without cloning key, index and auto_incremental item

Posted on 2014-10-27
14
245 Views
Last Modified: 2015-02-05
Dear all,

right now  try to clone a My SQL table using CREATE TABLE IF NOT EXISTS <table name> LIKE <source table>, but found that all index, primary key and auto increment attribute will goes to the new table too.

Any way to just clone the tabele schema with out all these?
0
Comment
Question by:marrowyung
  • 10
  • 4
14 Comments
 
LVL 7

Expert Comment

by:Stampel
ID: 40406789
what happens if you use create table newtable as (select * from oldtable;);
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40407731
I don't understand this ?

select * from old table only get the data but not the schema without constraint, primary key and index.

please assume that the new table don't exist at the beginning.
0
 
LVL 7

Expert Comment

by:Stampel
ID: 40409209
the new table dont exist and it gets created by the SQL statement.
Just wanted you to verify for the rest.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40410003
so you mean you didnt' try it before and you think it works, right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40410026
it seems working fine.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40410029
it can be use with IF NOT EXISTS  statement ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40410039
but one thing, the new table has ALL DATA from the original table, I don't want the data, is it possible ?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Accepted Solution

by:
Stampel earned 500 total points
ID: 40410158
Then use :
create table newtable as select * from oldtable limit 0;

you will get new empty table as you wish :)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40410471
I just done a test on this schema:

CREATE TABLE `SPI_INS_CS` (
  `GROUP_SR_ID` bigint(19) NOT NULL,
  `ORDER_TYPE` varchar(3) NOT NULL,
  `SERVICE_TYPE` varchar(100) NOT NULL,
  `PRODUCT_TYPE` varchar(50) NOT NULL,
  `CUSTOMER_ID` varchar(6) NOT NULL,
  `END_CUSTOMER_ID` varchar(6) DEFAULT NULL,
  `ACCT_NO` varchar(30) DEFAULT NULL,
  `MASTER_SR` bigint(19) DEFAULT NULL,
  `STATUS` varchar(2) NOT NULL,
  `CREATED_BY` varchar(12) NOT NULL,
  `CREATED_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `UPDATED_BY` varchar(12) DEFAULT NULL,
  `UPDATED_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `IS_OBSOLETE` int(1) NOT NULL DEFAULT '0',
  `SRC_UPDATED_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `PRODUCT_ID` varchar(3) NOT NULL DEFAULT ' ',
  PRIMARY KEY (`GROUP_SR_ID`),
  KEY `FK_CS_CUSTOMER` (`CUSTOMER_ID`),
  CONSTRAINT `FK_CS_CUSTOMER` FOREIGN KEY (`CUSTOMER_ID`) REFERENCES `SPI_INS_Customer` (`CUSTOMER_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Open in new window


and the result is :

CREATE TABLE `SPI_INS_CS_clonetest` (
  `GROUP_SR_ID` bigint(19) NOT NULL,
  `ORDER_TYPE` varchar(3) CHARACTER SET utf8 NOT NULL,
  `SERVICE_TYPE` varchar(100) CHARACTER SET utf8 NOT NULL,
  `PRODUCT_TYPE` varchar(50) CHARACTER SET utf8 NOT NULL,
  `CUSTOMER_ID` varchar(6) CHARACTER SET utf8 NOT NULL,
  `END_CUSTOMER_ID` varchar(6) CHARACTER SET utf8 DEFAULT NULL,
  `ACCT_NO` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
  `MASTER_SR` bigint(19) DEFAULT NULL,
  `STATUS` varchar(2) CHARACTER SET utf8 NOT NULL,
  `CREATED_BY` varchar(12) CHARACTER SET utf8 NOT NULL,
  `CREATED_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `UPDATED_BY` varchar(12) CHARACTER SET utf8 DEFAULT NULL,
  `UPDATED_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `IS_OBSOLETE` int(1) NOT NULL DEFAULT '0',
  `SRC_UPDATED_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `PRODUCT_ID` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ' '
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Open in new window


you can see that a lot of :

CHARACTER SET utf8

has been created, how is it and do it impact in any way ?
0
 
LVL 7

Expert Comment

by:Stampel
ID: 40410579
utf8 charset is the default encoding on most softwares, there is most chance it will have no impact.
But you will have to verify it for your applications using this table.

You will find more details on MySQL charset here.
http://dev.mysql.com/doc/refman/5.5/en/charset.html
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40412708
ok
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40480051
Dear all,


please help to answer this :"

http://www.experts-exchange.com/Database/MySQL/Q_28570501.html
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40499812
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40592894
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now