There may be a situation when you need an exact copy of a table, and CREATE
TABLE ... SELECT doesn't suit your purposes because the copy must include the
same indexes, default values, and so forth.
A D V E R T I S E M E N T
You can handle this situation by following steps.
Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the
source table's structure, indexes and all.
Modify the statement to change the table name to that of the clone table
and execute the statement. This way you will have exact clone table.
Optionally, If you need the table contents copied as well, issue an
INSERT INTO ... SELECT statement, too.
Example:
Try out following example to create a clone table for tutorials_tbl
Step 1:
Get complete structure about table
mysql> SHOW CREATE TABLE tutorials_tbl \G;
*************************** 1. row ***************************
Table: tutorials_tbl
Create Table: CREATE TABLE `tutorials_tbl` (
`tutorial_id` int(11) NOT NULL auto_increment,
`tutorial_title` varchar(100) NOT NULL default '',
`tutorial_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`tutorial_id`),
UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) TYPE=MyISAM
1 row in set (0.00 sec)
ERROR:
No query specified
mysql-> connect data1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id: 8394
Current database: data1
After executing step 2 you will a clone table in your database. If you want
to copy data from old table then you can do it by using INSERT INTO... SELECT
statement.
Share And Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
Keywords:
MySQL Clone Tables, Mysql Tutorial, Mysql tutorial pdf, history of mysql, basic mysql, syntax use in mysql, mysql software download, learn mysql, mysql insert, mysql delete, mysql data types, mysql administrator.
