How to clone a MySQL table, indexes, and data?

---

<p>To clone a MySQL table, indexes and data, we can use &ldquo;LIKE&rdquo;. I have a database &lsquo;business&rsquo; and in that many tables have been created. Here we can check all the tables.</p><pre class="result notranslate">mysql&gt; USE business; Database changed</pre><p>Let us display all the tables &minus;</p><pre class="prettyprint notranslate" style="">mysql&gt; SHOW tables; +------------------------+ | Tables_in_business &nbsp; &nbsp; | +------------------------+ | addcolumntable &nbsp; &nbsp; &nbsp; &nbsp; | | autoincrement &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | autoincrementtable &nbsp; &nbsp; | | bookindexes &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | chardemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | columnvaluenulldemo &nbsp; &nbsp;| | dateadddemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | deletedemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | deleterecord &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | demo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | demo1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | demoascii &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | demoauto &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | demobcrypt &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | demoemptyandnull &nbsp; &nbsp; &nbsp; | | demoint &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | demoonreplace &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | demoschema &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | distinctdemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | duplicatebookindexes &nbsp; | | duplicatefound &nbsp; &nbsp; &nbsp; &nbsp; | | employeetable &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | existsrowdemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | findandreplacedemo &nbsp; &nbsp; | | foreigntable &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | foreigntabledemo &nbsp; &nbsp; &nbsp; | | groupdemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | groupdemo1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | incasesensdemo &nbsp; &nbsp; &nbsp; &nbsp; | | int1demo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | intdemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | latandlangdemo &nbsp; &nbsp; &nbsp; &nbsp; | | limitoffsetdemo &nbsp; &nbsp; &nbsp; &nbsp;| | milliseconddemo &nbsp; &nbsp; &nbsp; &nbsp;| | modifycolumnnamedemo &nbsp; | | modifydatatype &nbsp; &nbsp; &nbsp; &nbsp; | | moneydemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | moviecollection &nbsp; &nbsp; &nbsp; &nbsp;| | mytable &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | nonasciidemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | nthrecorddemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | nulldemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | nullwithselect &nbsp; &nbsp; &nbsp; &nbsp; | | pasthistory &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | presenthistory &nbsp; &nbsp; &nbsp; &nbsp; | | primarytable &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | primarytable1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | primarytabledemo &nbsp; &nbsp; &nbsp; | | sequencedemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | smallintdemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | spacecolumn &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | student &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | tblstudent &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | tbluni &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | textdemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | texturl &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | trailingandleadingdemo | | unsigneddemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | varchardemo &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | varchardemo1 | | varchardemo2 | | varcharurl | | whereconditon | +------------------------+ 63 rows in set (0.25 sec)</pre><p>Now, I am using the table STUDENT from the above database. The query is as follows &minus;</p><pre class="result notranslate">mysql&gt; DESC student; </pre><p>The following is the output</p><pre class="result notranslate" style="">+-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | | Name | varchar(100) | YES | MUL | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.20 sec)</pre><p>Let us now check the records of the student table:</p><pre class="prettyprint">SELECT * from student </pre><p>Here is the output:</p><pre class="prettyprint" style="">+------+------+ | id | Name | +------+------+ | 1 | John | | 2 | Bob | +------+------+ 2 rows in set (0.00 sec)</pre><p>Here is the query; we can make clone of indexes as well as data using LIKE. The query is as follows &minus;</p><pre class="result notranslate">mysql&gt; CREATE table cloneStudent like student; Query OK, 0 rows affected (1.57 sec) mysql&gt; insert cloneStudent SELECT *from student; Query OK, 2 rows affected (0.60 sec) Records: 2 Duplicates: 0 Warnings: 0</pre><p>Therefore, I have successfully created clone as well as data. Now, we can prove that we have created clone of indexes as well as data with the help of DESC command, which is as follows &minus;</p><pre class="result notranslate">mysql&gt; DESC cloneStudent; </pre><p>The following is the output</p><pre class="result notranslate" style="">+-------+--------------+------+-----+---------+-------+ | Field | Type &nbsp; &nbsp; &nbsp; &nbsp; | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id &nbsp; &nbsp;| int(11) &nbsp; &nbsp;| YES | MUL | NULL | | | Name | varchar(100) | YES | MUL | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)</pre><p>Let us check whether the table is cloned or not.</p><pre class="result notranslate">mysql&gt; SELECT * from cloneStudent; </pre><p>The following is the output:</p><pre class="result notranslate" style="">+------+------+ | id &nbsp; | Name | +------+------+ | 1 &nbsp; &nbsp;| John | | 2 &nbsp;| Bob | +------+------+ 2 rows in set (0.00 sec)</pre>

• Related Questions & Answers
• How can I clone/duplicate the table along with its data, trigger and indexes?
• How to create a MySQL table with indexes?
• How can we make a MySQL clone table?
• Add new MySQL table columns and create indexes?
• How do I clone the structure of a table in MySQL?
• How to get a list of MySQL indexes?
• How can we check the indexes created by a UNIQUE constraint on a MySQL table?
• How to create a clone of a data frame in R without data values?
• How to find out all the indexes for a DB2 table TAB1?
• How to write MySQL procedure to insert data into a table?
• How to clone a GitHub repository?
• How can we insert data into a MySQL table?
• MySQL statement to copy data from one table and insert into another table
• Show/view indexes in a MySQL Database
• How to Clone a List in Java?