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

MySQLMySQLi Database

<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>
raja
Updated on 25-Jun-2020 08:20:11

Advertisements