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

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 str开发者_JAVA技巧aight 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

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;

You can use CREATE TABLE foo LIKE bar and an INSERT...SELECT statement.

http://dev.mysql.com/doc/refman/5.1/en/create-table.html says:

Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:

CREATE TABLE new_tbl LIKE orig_tbl;

The copy is created using the same version of the table storage format as the original table. The SELECT privilege is required on the original table.

LIKE works only for base tables, not for views.

CREATE TABLE ... LIKE does not preserve any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table, or any foreign key definitions.