YouTip

# MySQL Clone Tables If we need to completely clone a MySQL table, including its structure, indexes, default values, etc. Using only the `CREATE TABLE ... SELECT` command cannot achieve this. This chapter will introduce how to completely clone a MySQL table. The steps are as follows: * Use the **SHOW CREATE TABLE** command to get the `CREATE TABLE` statement, which contains the structure, indexes, etc., of the original table. * Copy the SQL statement displayed by the following command, modify the table name, and execute the SQL statement. This will completely clone the table structure. * If you want to clone the table's content, you can use the **INSERT INTO ... SELECT** statement. ### Example Try the following example to clone the table `tutorial_tbl`. **Step 1:** Get the complete structure of the table. mysql> SHOW CREATE TABLE tutorial_tbl G; *************************** 1. row *************************** Table: tutorial_tbl Create Table: CREATE TABLE `tutorial_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`)) ENGINE=InnoDB 1 row in set (0.00 sec) ERROR:No query specified **Step 2:** Modify the table name in the SQL statement and execute it. mysql> CREATE TABLE `clone_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`)-> ) ENGINE=InnoDB;Query OK, 0 rows affected (1.80 sec) **Step 3:** After completing Step 2, you will have created a new clone table `clone_tbl` in the database. If you want to copy the table's data, you can use the **INSERT INTO... SELECT** statement. mysql> INSERT INTO clone_tbl (tutorial_id, -> tutorial_title, -> tutorial_author, -> submission_date) -> SELECT tutorial_id,tutorial_title, -> tutorial_author,submission_date -> FROM tutorial_tbl;Query OK, 3 rows affected (0.07 sec)Records: 3 Duplicates: 0 Warnings: 0 After executing the above steps, the table's content, including its structure and data, will be completely cloned. * * * ## Using the mysqldump Command The `mysqldump` command can be used to back up and restore MySQL databases. If you only want to clone a single table, you can use `mysqldump` to export the table's structure and data, and then import it into a new database or a new table. Here is a simple example. Back up table data: mysqldump -u username -p dbname old_table > old_table_dump.sql This will export the structure and data of the table named `old_table` to a SQL file named `old_table_dump.sql`. You need to provide the MySQL username and password, and replace `username`, `dbname`, and `old_table` with the actual values. Restore to a new database: mysql -u username -p new_dbname < old_table_dump.sql This will create a table named `old_table` in the new database (`new_dbname`) and import the previously exported structure and data into the new table.