STUDY WHILE YOU ARE BORING


  • 首頁

  • 搜索

  • 技术

  • Tags

  • 关于我

PHP实现的归档小程序

發表於 April 27, 2014   |   作者: Tim   |   分类: 技术   |   评论

支持:

  • 多个关联表归档(有相同的键)
  • 设置归档间隔(速度)
  • 删除源数据开关
  • 异常错误跳过开关

用法:

archive('stu','backup.stu','id <10','sid');
archive(array('stu','stu_detail'),array('backup.stu','backup.stu_detail'),'id<10','sid');
/**
 * 归档助手,归档数据表1到表2,支持跨库
 * @param string|array $fromNameArr        源数据表
 * @param string|array $toNameArr        目标数据表
 * @param string $where                    筛选语句
 * @param string $primaryKey            主键,如果元数据表和目标数据表有多个,主键需要在所有表中存在
 * @param bool $deleted                    归档数据之后是否删除源表中的数据
 * @param number $step                    归档速度,如果数据小于归档速度的10分之一,则不归档
 */
function archive($fromNameArr,$toNameArr,$where=1,$primaryKey='id',$deleted =true,$step=4000,$skipDbError=false){
    $fromNameArr=normalize($fromNameArr);
    $toNameArr=normalize($toNameArr);
    $fromName=$fromNameArr[0];
    $toName=$toNameArr[0];
    
    $conn=DB::getConn();
    
    foreach ($toNameArr as $i=> $_tn){
        $_fn=$fromNameArr[$i];
        try {
            $conn->execute("create table {$_tn} like {$_fn}");
        }catch (Exception $ex){
            log("{$_tn} existed");
        }
    }
    
    $conn->execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
    $conn->execute('SET binlog_format = MIXED');
    log('archive from '.$fromName. ' to '.$toName);
    
    $begin=$bcount=0;
    while (true){
        log($fromName.' '.$bcount);
        $ids=$conn->execute("select {$primaryKey} from {$fromName} FORCE INDEX(`PRIMARY`) where {$where} limit {$begin},{$step} ")
            ->fetchAll();
        $ids=getCols($ids, $primaryKey);
        
        if (count($ids) < $step/10){
            return ;
        }
        $keys=implode(',', $ids);
        
        foreach ($fromNameArr as $i => $_fn){
            $_tn=$toNameArr[$i];
            try {
                $conn->execute("insert into {$_tn} select * from {$_fn} where {$primaryKey} in ({$keys})");

                log($_fn.' archived');
                
                
            }catch (Exception $ex){
                log($ex->getMessage());
            }
            
            
            if ($deleted ){
                $conn->execute("delete from {$_fn} where {$primaryKey} in ({$keys})");
                log('deleted');
            }
        }
        if (!$deleted){
            $begin+=$step;
        }
        $bcount+=$step;
        
    }
}
function normalize($input, $delimiter = ',')
{
    if (!is_array($input))
    {
        $input = explode($delimiter, $input);
    }
    $input = array_map('trim', $input);
    return array_filter($input, 'strlen');
}
function getCols($arr, $col)
{
    $ret = array();
    foreach ($arr as $row) 
    {
        if (isset($row[$col])) { $ret[] = $row[$col]; }
    }
    return $ret;
}
function log($str){
    println ( '[' . date ( 'Ymd His' ) . ']' . $str );
}
php, 归档
尾兽超萌
快速清理binlog

钜添

54 文章
2 分類
1 页面
GitHub 日记技术
php apache centos macos mysql https shadowsocks mac 微信 ups svn javascript outline accesskey Chrome Extension letsencrypt ssl free certification certbo wildcard renew cloudflare pgsql 归档 binlog isset array_key_exists redmine axure
© 2021 钜添   |   文章 RSS     |  登录
由 Typecho 强力驱动
主題 - NexT
Send message encrypted and private - Msg2
Build your Under Construction Page without hosting - UnderConstruct.IO