MySQLMySQL create exact copy of innodb table

MySQL create exact copy of innodb table

Creating table copies can be done in various ways in MySQL – however when it comes to innodb, we have to take special care because foreign keys are involved and we dont want to lose them when we make an “exact” copy

This is for MySQL version 5.1.41

For MyISAM using the below sql statements will accomplish the task

CREATE TABLE table2 LIKE table1; //copies only the structure of the table1 into table2 including index information
INSERT INTO table2 SELECT * FROM table1; //copies all the data from table1 to table 2

Now try to issue the same set of commands on an innodb table which has foreign keys – do you see the difference in copy?

Below is a small function that uses SHOW CREATE query to accomplish the task of making an “exact” copy of the innodb table
This is what it does

  1. Takes 2 input parameters, original_table_name and new_table_name
  2. Gets the create statement for the original table by using the SHOW CREATE command
  3. in this sql, it replaces the original table name with the new table name
  4. returns the sql

This shows how to use the function in php

$create_table_sql = create_innodb_table_copy_sql($original_table_name, $new_table_name); 
$query = mysql_query($create_table_sql, $mysql_conn);
$query = mysql_query("INSERT INTO $new_table_name SELECT * FROM $original_table_name", $mysql_conn);

 * The function
function create_innodb_table_copy_sql($original_table_name, $new_table_name)  {
    global $mysql_conn; //mysql connection
    $query = mysql_query("SHOW CREATE TABLE $original_table_name",$mysql_conn);

    if(mysql_num_rows($query) == 1) {
        $row_array = mysql_fetch_array($query);
        $create_sql = $row_array['Create Table'];

        //replace the original table name with the new table name
        $create_sql = str_replace($original_table_name, $new_table_name, $create_sql);

        return $create_sql;
     else return FALSE;

Categories: MySQL


No Comments Yet. Be the first?

Post a comment

Your email address will not be published. Required fields are marked *