?
Solved

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

Posted on 2014-10-27
14
Medium Priority
?
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

 
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
 
LVL 7

Accepted Solution

by:
Stampel earned 2000 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 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