php实现从mysql备份sql文件中提取特定数据

案例场景：玩家数据打包为一个blob存在mysql中，需要查看玩家特定时间的数据

策略：从dump sql文件中提取玩家数据块

实现：为了简单和跨平台，用php编写

具体步骤如图，1为简单版，2为优化版。

备注：关键点在于步长一定要大于玩家数据大小，这样如果搜索到起始位置，至多再有一次read就可以得到玩家数据.

另外重要的一点是：sql文件是mysqldump的输出文件，blob都是转义过的，不能拿来直接当作二进制数据做内存解析，只能再mysql导入到db中才能使用

源代码：

<?php
function write_data($uid, $data)
{
        $fp = fopen("$uid"."_dump.rar", 'wb');

		$real_data = "replace into kakag.t_player_data_1_1 values($uid,'".$data."');";	

        fwrite($fp, $real_data);
        fclose($fp);
		//echo md5($real_data)."\n";
}

function get_real_data($uid, $data, &$has_start_flag)
{
	$start_flag = "($uid,'";
	$start_pos = strpos($data, $start_flag);
	
	if($start_pos != false)
	{
		$has_start_flag = true;
	
		//first state
		$check_flag = "'),(";
		$end_pos = strpos($data, $check_flag, $start_pos);

		if($end_pos != false)
		{
			$new_start_pos = $start_pos + strlen($start_flag);
			$real_data = substr($data, $new_start_pos, $end_pos - $new_start_pos );
			write_data($uid, $real_data);
			
			return true;
		}
		
		//second state
		$check_flag = "');";
		$end_pos = strpos($data, $check_flag, $start_pos);

		if($end_pos != false)
		{
			$new_start_pos = $start_pos + strlen($start_flag);
			$real_data = substr($data, $new_start_pos, $end_pos - $new_start_pos );
			write_data($uid, $real_data);
			
			return true;
		}
	}
	else
	{
		return false;
	}
}

function read_data($uid, $file_name)
{
	$fp = fopen($file_name, 'r');
	$once_size = 1024 * 500;

	while(1)
	{
		$data = fread($fp, $once_size);

		if($data != "")
		{
			$has_start_flag = false;
			$ret = get_real_data($uid, $data, $has_start_flag);
			
			if($ret == true)
			{
				echo "load user data success\n";
				break;
			}
			else
			{
				if($has_start_flag == true)
				{
					$data_extra = fread($fp, $once_size);
					if($data_extra != "")
					{
						$new_data = $data.$data_extra;
						
						$has_start_flag = false;
						$ret = get_real_data($uid, $new_data, $has_start_flag);
						
						if($ret == true)
						{
							echo "load user data success\n";
							break;
						}
						else
						{
							break;
						}
					}
					else
					{
						break;
					}
				}
				else
				{
					continue;
				}	
			}
		}
		else
		{
			break;
		}
	}

	fclose($fp);
}

read_data($argv[1], $argv[2]);
?>