Solved

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

Posted on 2014-10-27
14
242 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 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

10 Experts available now in Live!

Get 1:1 Help Now