Copying tables in MySQL


Whether you need to copy a test table to a production database, or you need to duplicate a table with only some selected rows or you just need to backup the original table, copying tables is a frequent task most of us regularly do. In this post we will see some quick methods to make copies of MySQL database tables.

For these examples I’ve used the following table structure.

id      username    password
-----------------------------------
1       admin       *************
2       sameer      *************
3       stewart     *************
 
 
CREATE TABLE IF NOT EXISTS `admin` (
  `id` int(6) unsigned NOT NULL auto_increment,
  `username` varchar(50) NOT NULL default '',
  `password` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Quickly copying tables.

The most common task is to copy the table structures. The following statement copies the structure of the ‘admin’ table to a new table called ‘newadmin’.

CREATE TABLE newadmin LIKE admin

Note that only the structure and not the data is copied to the new table. To also copy the data use the following statement.

CREATE TABLE newadmin AS
(
    SELECT *
    FROM admin
)

In the above examples it is assumed that the source and the destination tables which we want to copy are in the same database, but you can also copy tables from another database by prefixing the database name before the table. The following for example will copy the structure of the ‘admin’ table from the ‘shop’ database to the ‘newadmin’ table in the current database.

CREATE TABLE newadmin LIKE shop.admin

You can also specify the destination database in the query.

CREATE TABLE newshop.newadmin LIKE shop.admin

This will copy the ‘admin’ table from the ‘shop’ database to the new table ‘newadmin’ in the ‘newshop’ database.

Note: In all the above examples the DATA DIRECTORY or INDEX DIRECTORY table options are not copied to the new table. So if you need an exact replica of a table you have to create two separate statements.

CREATE TABLE newadmin LIKE admin;
INSERT INTO newadmin SELECT * FROM admin;

This will copy the table ‘admin’ exactly to the ‘newadmin’ table with all the correct table definitions, but we lose the flexibility we can have with the other method, which we will be seeing next.

To copy some select columns to our new table, specify the column names in a SELECT statement.

CREATE TABLE newadmin AS
(
    SELECT username, password FROM admin
)

This will only copy the ‘username’ and ‘password’ columns to the new table. This can be really useful when the original table contains over fifty columns and you want to copy only a few.

While copying a table you can also change the column names of the new table.

CREATE TABLE newadmin AS
(
    SELECT id, username AS uname, password AS pass FROM admin
)

By now you may have already guessed that you could copy any queried data to the new table using the SELECT statement. The following for example copies all the rows from ‘admin’ to the new table where the username starts with an ‘s’.

CREATE TABLE newadmin AS
(
    SELECT * FROM admin WHERE LEFT(username,1) = 's'
)

Copies the following two rows from the ‘admin’ table.

id      username    password
-----------------------------------
2       sameer      *************
3       stewart     *************

While copying you can also change the table structure of the new table by specifying the same in your query.

CREATE TABLE newadmin
(
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
) 
AS
(
    SELECT * FROM admin
)

This will copy the ‘admin’ table and also assign the appropriate primary key to the ‘id’ column in the new table.

In conclusion

So basically there are two methods of copying tables.
a. By using the LIKE and AS keywords in single sql statement.

CREATE TABLE newadmin AS
(
    SELECT *
    FROM admin
)

b. By using two different statements.

CREATE TABLE newadmin LIKE admin;
INSERT INTO newadmin SELECT * FROM admin;

The first one offers flexibility while copying tables but does not preserve any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table, or any foreign key definitions.

The second method truthfully copies the tables but does not offer the flexibility of the first method. By truthfully I mean only the DATA DIRECTORY or INDEX DIRECTORY table options are not copied.

Which method you use will depend on your specific requirement.

This site is a digital habitat of Sameer Borate, a freelance web developer working in PHP, MySQL and WordPress. I also provide web scraping services, website design and development and integration of various Open Source API's. Contact me at metapix[at]gmail.com for any new project requirements and price quotes.

2 Responses

1

Shantanu Oak

August 17th, 2009 at 11:29 pm

There is an illegal way of coping MyISAM tables.
copy the .frm .myd and .myi files with permissions.
Make sure that the count(*) matches, just in case :)

2

網站製作學習誌 » [Web] 連結分享

September 1st, 2009 at 5:46 am

[...] Copying tables in MySQL [...]

Your thoughts