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!

Published At (Updated At)
Tagged with