douqin6785
douqin6785
2017-01-03 22:01
浏览 69

如何使用PHP在不同的数据库中创建MySQL表结构

I have 2 separate and different databases:

  • SOURCE_DATABASE
  • TARGET_DATABASE

I am trying to copy a table from SOURCE_DATABASE to TARGET_DATABASE using PHP and not phpMyAdmin (as it works fine in phpMyAdmin).

I have the following php:

$linkSource = mysql_connect( SERVER, SOURCE_USERNAME, SOURCE_PASSWORD );
mysql_select_db( SOURCE_DATABASE, $linkSource );
$linkTarget = mysql_connect( SERVER, TARGET_USERNAME, TARGET_PASSWORD );
mysql_select_db( TARGET_DATABASE, $linkTarget );

mysql_query( 'CREATE TABLE `targetDB.targetTable` LIKE `sourceDB.sourceTable` ) or die( mysql_error() );

Is it possible to create a table in a 2nd database (target) using the structure of a table in a 1st database (source)?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • douyun3799
    douyun3799 2017-01-03 22:10

    You could use MySQL's "SHOW CREATE TABLE" function to get the structure of the source table, and then execute the CREATE TABLE statement it gives you in the target connection.

    $result = mysql_query("SHOW CREATE TABLE sourceTable", $linkSource);
    $create_table = mysql_result($result, 0, 1);
    mysql_query($create_table, $linkTarget);
    
    点赞 评论
  • dongpo2014
    dongpo2014 2017-01-04 13:09

    PhpMyAdmin and PHP is wrong tools for this purposes. Use command line shell. Something like this:

    mysqldump -u root -p database1 > database1.sql
    
    mysql -u root -p database < database1.sql
    

    It will work in 10 times more faster, I'm guarantee it. Also, database will take care about data consistency instead of you.

    If you are realy want to do it in PHP, use php command line shell.

    If you are still want to do it in PHP WITHOUT command line shell, I can suggest to do this kind of trick:

    $query = "show tables from source_database";
    $tables = $dbSource->getColumn($query)//get one column from database
    foreach($tables as $tableName) {
      $query = "show create table ".$tableName;//← it will return query to clone table structure
      $createTableSchema = $dbSource->getRow($query);//← get assoc array of one row
      $dbTarget->query($createTableSchema['Create Table']);//← here is your silver bullet
    }
    

    PS and also, when (if) you will copy data from one table to another table, you should know that statement

    insert into table () values (values1), (values2), (values3);
    

    much more faster than

    insert into table () values (values1);
    insert into table () values (values2);
    insert into table () values (values3);
    

    But insert multy rows is related to max_allowed_packet propery field. In case, when your query will more large than this field (yes, query string can allocate 1 gb of memory), MySQL will throw exception. So, to build Insert multy query you should get max_allowed_packet and generate this query according to this size. Also to speed up performance you can disable keys from target table. (do not forgot to enable keys, lol)

    点赞 评论

相关推荐