PHP实现的归档小程序

支持：

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

用法：

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 );
}