mysql - how to copy table and keep same keys and encoding?

Question

Hi,

Just wrote a short script of copying all my tables into another local database. was quite easy but after checking it I found that the keys are gone and also the encoding for text fields are set to some default encoding.

How can I use my script and have the same keys and same encoding duplicated after copying, but not manually define for each table. I have to get this done automatically.

my code is pretty straight forward:

$first_db = 'first_db';
$second_db = 'second_db';


$result = mysql_query("SHOW TABLES FROM $first_db");

while ($row = mysql_fetch_array($result)){
    $current_table = $row["Tables_in_$first_db"];
    $result2 = mysql_query("DROP TABLE IF EXISTS $second_db.$table_name");
    $result2 = mysql_query("CREATE TABLE $second_db.$table_name SELECT * FROM $first_db.$current_table");
} 

I think I should use ALTER TABLE, but can it find and define the keys and encoding automatically ?

Thanks

Answer 1

Have you tried copying the table first then populate the data aftewards?

create table second_db.table_name like first_db.table_name;

And then start populating the data afterwards.

insert into second_db.table_name select * from first_db.table_name;