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