lnlidawei

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
caipiao: 数据从一个数据库转移到另一个数据库（新旧数据库：表的结构不同；PHP）

caipiao:  数据从一个数据库转移到另一个数据库（新旧数据库：表的结构不同；PHP）

 

 

 

 

一、php源代码

  1 [wit@fedora dlt]$ cat  dltlibs_backup.php 
  2 #!/usr/bin/env php
  3 
  4 
  5 <?php
  6 
  7 
  8 
  9 
 10 // insert49() - insert $dlt_data to $dlt_tbl; 49 - 49 items.
 11 function insert49($tbl_name, $data)
 12 {
 13 
 14         // $sql = "insert into $tbl_name(uid, udate, f$data[2], f$data[3], f$data[4], f$data[5], f$data[6], b$data[7], b$data[8]) 
 15         //      values($data[0], $data[1], $data[2], $data[3], $data[4], $data[5], $data[6], $data[7], $data[8])";
 16 
 17         $sql = "insert into $tbl_name(uid, udate, f$data[2], f$data[3], f$data[4], f$data[5], f$data[6], b$data[7], b$data[8]) values($data[0], \"$data[1]\", $data[2], $data[3], $data[4], $data[5], $data[6], $data[7], $data[8])";
 18 
 19         /*
 20         echo "\n\n";
 21         echo $sql;
 22         echo "\n\n";
 23         */
 24 
 25         return $sql;
 26 }
 27 
 28 
 29 
 30 
 31 // insert9() - insert $dlt_data to $dlt_tbl; 9 - 9 items.
 32 function insert9($tbl_name, $data)
 33 {
 34 
 35         $sql = "insert into $tbl_name values($data[0], \"$data[1]\", $data[2], $data[3], $data[4], $data[5], $data[6], $data[7], $data[8])";
 36 
 37         /*
 38         echo "\n\n";
 39         echo $sql;
 40         echo "\n\n";
 41         */
 42 
 43         return $sql;
 44 
 45 }
 46 
 47 
 48 // test insert()
 49 
 50 /*
 51 $tbl_name = "dlt2023";
 52 $data = array("2023001", "2023-01-02", 18, 19, 24, 27, 34, 02, 07);
 53 
 54 insert49($tbl_name, $data);
 55 insert9($tbl_name, $data);
 56 */
 57 
 58 
 59 
 60 
 61 
 62 // db1todb2() - insert data  from db1  to db2. 
 63 // db1todb2() - data from db1 insert to db2.
 64 // db1todb2() - db1 to db2.
 65 // dtb1todb2() - $tbn1: table1_name, $tbn2: table2_name;
 66 function db1todb2($tbn1, $tbn2)
 67 {
 68 
 69         if (1) {
 70         // here, change by yourself.
 71         $server1 = "localhost";  // your ip of server1
 72         $user1 = "user1";       // your user1 of server1
 73         $passwd1 = "mima1";     // your password of user1 of server1
 74         $db1 = "db1";   // your database name of server1
 75 
 76 
 77         $server2 = "localhost";  // your ip of server2
 78         $user2 = "user2";       // your user1 of server2
 79         $passwd2 = "mima2";     // your password of user2 of server2
 80         $db2 = "db2";   // your database name of server2
 81         }
 82 
 83 
 84         try {
 85 
 86                 $conn1 = new PDO("mysql:host=$server1;dbname=$db1", $user1, $passwd1 );
 87                 $conn2 = new PDO("mysql:host=$server2;dbname=$db2", $user2, $passwd2 );
 88 
 89 
 90                 $conn1 -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 91                 $conn2 -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 92 
 93 
 94                 $conn2 ->query("truncate table $tbn2");
 95                 $tb2_name = "dlt2023";
 96                 $icount = 0;
 97                 foreach( $conn1->query("select * from $tbn1") as $row){
 98                         // $sql2 = insert49($tb2_name, $row);
 99                         $sql2 = insert49($tbn2, $row);
100                         echo "\nline_$icount: " .$sql2. "\n";
101                         $conn2 -> query($sql2);
102                         $icount = $icount + 1;
103                 }
104 
105 
106 
107                 $conn2 = null;
108                 $conn1 = null;
109 
110         } catch(PDOException $e) {
111                 echo "ERROR: ". $e -> getMessage();
112         }
113 
114 }
115 
116 
117 // test db1todb2($tbn1, $tbn2)
118 $tbn1="dlt";
119 $tbn2="dlt";
120 db1todb2($tbn1, $tbn2);
121 
122 
123 ?>
124 [wit@fedora dlt]$ 
125 [wit@fedora dlt]$

 

 

 

 

二、参考文档

 

　　1、PHP PDO | 菜鸟教程　　https://www.runoob.com/php/php-pdo.html

 

posted on 2023-11-19 01:20  lnlidawei  阅读(24)  评论(0)  编辑  收藏  举报