定时从一个数据库往另一个数据库插入数据

发布时间:2015-09-09 14:24 | 人气数:458

mtSlaveMain.php源文件

<?php
header("Content-Type:text/html;charset=utf8");
set_time_limit(300);
error_reporting(0);
//$intervalgo= 60*3;
//do{
$slCon = mysql_connect("localhost","root","root") or die("数据库连接出错:" . mysql_error());
mysql_select_db("belle_img",$slCon);
$slCon_get_sql = "select id, onclick, isgood, titleurl, title, newstime, titlepic, smalltext, diggtop from phome_ecms_article where isgood IN (2,3) and ispic =1 order by lastdotime desc Limit 5";
mysql_query("SET NAMES 'utf8'");
$slCon_get_rs = mysql_query($slCon_get_sql,$slCon);
mysql_close($slCon);
$mlCon = mysql_connect("localhost","root","root") or die("数据库连接出错:" . mysql_error());
while($slCon_get_row = mysql_fetch_assoc($slCon_get_rs)){
    $classid = 24;
    $newspath = date("Y-m-d",$slCon_get_row['newstime']);
    $isgood = $slCon_get_row['isgood']==3?5:0;
    $hfviews = mt_rand(500,2500);   //$slCon_get_row[hfviews]
    $diggtop = mt_rand(20,450);     //$slCon_get_row[diggtop]
    $smalltext = "请在美图秀子站管理此文章内容";  //$slCon_get_row[smalltext]
    $mlId = 0;
    $titlepic = "http://meitukk.com".$slCon_get_row['titlepic'];
if($slCon_get_row[id]>0){
    $mlCon_into_sql = "INSERT INTO phome_ecms_news(classid, ttid, onclick, newspath, userid, username, isgood, ispic, isurl, truetime, lastdotime, havehtml, titleurl, title, newstime, titlepic, smalltext, diggtop, hfviews, sontid) SELECT '$classid', '0', '0', '$newspath', '13', 'meitukk', '$isgood', '1', '1', '$slCon_get_row[newstime]', '$slCon_get_row[newstime]', '1', '$slCon_get_row[titleurl]', '$slCon_get_row[title]', '$slCon_get_row[newstime]', '$titlepic', '$smalltext', '$diggtop', '$hfviews', '$slCon_get_row[id]' FROM DUAL WHERE NOT EXISTS(SELECT id FROM phome_ecms_news WHERE classid  = '$classid' AND sontid = '$slCon_get_row[id]' ) limit 1;";
    mysql_select_db("wifijunData",$mlCon);
    mysql_query("SET NAMES 'utf8'");
    mysql_query("BEGIN");
    //mysql_query("LOCK TABLES phome_ecms_news WRITE,phome_ecms_news_data_1 WRITE,phome_ecms_news_index WRITE;",$mlCon);
    $mlCon_into_rs = mysql_query($mlCon_into_sql,$mlCon);
    $mlId = mysql_insert_id($mlCon);
 
    if($mlCon_into_rs){
 
        if($mlId > 0 && $slCon_get_row['id']>0){
        mysql_select_db("wifijunData",$mlCon);
        mysql_query("SET NAMES 'utf8'");
        $mlCon_into_data_sql = "INSERT INTO `phome_ecms_news_data_1` (`id`, `classid`, `keyid`, `dokey`, `newstempid`, `closepl`, `haveaddfen`, `infotags`, `writer`, `befrom`, `newstext`)VALUES ('$mlId', '$classid', '', '1', '0', '0', '0', '', 'meitukk', '美图秀', '');";
        $mlCon_into_data_rs = mysql_query($mlCon_into_data_sql,$mlCon);
 
        mysql_select_db("wifijunData",$mlCon);
        mysql_query("SET NAMES 'utf8'");
        $mlCon_into_index_sql = "INSERT INTO `phome_ecms_news_index` (`id`, `classid`, `checked`, `newstime`, `truetime`, `lastdotime`, `havehtml`)VALUES ('$mlId', '$classid', '1', '$slCon_get_row[newstime]', '$slCon_get_row[newstime]', '$slCon_get_row[newstime]', '1');";
        $mlCon_into_index_rs = mysql_query($mlCon_into_index_sql,$mlCon);
 
        mysql_select_db("wifijunData",$mlCon);
        mysql_query("SET NAMES 'utf8'");
        $mlCon_update_news_sql = "UPDATE `phome_ecms_news` SET `filename` = '$mlId', `sontid` = '$slCon_get_row[id]' WHERE `id` = '$mlId' AND classid  = '$classid';";
        $mlCon_update_news_rs = mysql_query($mlCon_update_news_sql,$mlCon);
 
        }
    }
    if($mlCon_into_rs && $mlCon_into_data_rs && $mlCon_into_index_rs && $mlCon_update_news_rs){
          mysql_query("COMMIT");
          if($mlId > 0){echo "文章《{$slCon_get_row[title]}》已经同步到微时段!<br />";}
       }else{
          mysql_query("ROLLBACK");
          //echo "文章《{$slCon_get_row[title]}》未同步到微时段!<br />";
       }
    mysql_query("END");
    //mysql_query("UNLOCK TABLES;",$mlCon);
    }
}
unset($slCon_get_rs);
mysql_close($mlCon);
echo "<br />提交成功,新文章已经发布到微时段! <a href='javascript:window.close()'>关闭本窗口</a>";
//sleep($intervalgo);
//}while(true);
?>

在前台或后台用JS或框架调用执行,查询表的锁定状态`show status like 'table%';`

关键词:MYSQL,定时插入数据, PHP源代码, 表锁定, 事务处理, 表事处