MySQL: Copy only the structure of table

Você pode ler este post em português também.

Hello!

Last month I had to copy the structure of a table to a new one. More than two people were surprised with the way and ease which I did it, so I decided to post it here instead of another performance comparison inside PHP.

Below is an example. Enjoy and feel free to share ideas about it.

[arglbr@t64 ~]$ mysql -AD test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 20
Server version: 5.1.47 Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> CREATE TABLE `test`.`t1` (
-> `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> `name` VARCHAR(50) NOT NULL,
-> PRIMARY KEY (`id`),
-> INDEX `idx_01`(`name`)
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t1 values (1, 'Adriano'), (2, 'Andréa'), (3, 'Arlete');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+----+---------+
| id | name |
+----+---------+
| 1 | Adriano |
| 2 | Andréa |
| 3 | Arlete |
+----+---------+
3 rows in set (0.00 sec)

mysql> create table t2 like t1;
Query OK, 0 rows affected (0.10 sec)

mysql> select * from t2;
Empty set (0.00 sec)

mysql> show create table t1;
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| t1 | CREATE TABLE `t1` ( |
| `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, |
| `name` varchar(50) NOT NULL, |
| PRIMARY KEY (`id`), |
| KEY `idx_01` (`name`) |
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t2;
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| t2 | CREATE TABLE `t2` ( |
| `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, |
| `name` varchar(50) NOT NULL, |
| PRIMARY KEY (`id`), |
| KEY `idx_01` (`name`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql>

As you can see, it’s an interesting method because it gets all the structure (índex, engine, encoding) e naturally, reset the AUTO_INCREMENT atribute.

Cheers!

About Adriano Laranjeira

Software engineer & developer See more at http://about.me/arglbr
This entry was posted in English and tagged . Bookmark the permalink.

1 Response to MySQL: Copy only the structure of table

  1. Pingback: Adriano Laranjeira » Blog Archive » MySQL: Copiar somente estrutura de uma tabela

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s