php mysql 自动分表_PHP自动分表程序

http://blog.zol.com.cn/2210/article_2209590.html

/**

* 分表程序

*

* @author Shen Xi shen.xi@zol.com.cn

* @date 2010-08-19

*/

set_time_limit(0);

$con = mysql_connect("127.0.0.1","root","111111");#创建数据库连接

mysql_select_db ("test",$con);#选择连接数据库“test”

mysql_query("set names gbk");#设置获取数据得字符集

$slctTable = "tb_test";

#获取被分表结构

$sql = "show create table $slctTable";

$result = mysql_query($sql);

$row = mysql_fetch_array($result);

$createTable = $row['Create Table'];

//echo "$createStr

";

#获取被分表字段名

$field = mysql_list_fields("test",$slctTable,$con);

$filedNum = mysql_num_fields($field);

$fieldStr = "";

for($i=0;$i < $filedNum;$i++){

$fieldStr.= "`". mysql_field_name($field, $i)."`,";

}

if(strlen($fieldStr) == 0){

echo "获取表字段有误！";

exit;

}

$fieldStr = "(".substr($fieldStr,0,-1).")";

//echo "$fieldStr

";

//exit;

#查找最大用户id

$sql = "SELECT max(id) as maxid

FROM  $slctTable";

$result =mysql_query($sql);

$row = mysql_fetch_row($result);

$maxid = (int)$row[0];

// var_dump($maxid);

//exit;

if($maxid > 1){

$count = ceil($maxid / 1000);

}else{

echo "用户最大id有误";

exit;

}

$per = 500;#每次取得的记录条数

$prefix = "tb_test";

for($i=0;$i<=$count;$i++){

$table = $prefix.$i;

#生成创建新表的sql语句

$createStr = str_replace("CREATE TABLE `$slctTable`","CREATE TABLE IF NOT EXISTS `$table`",$createTable);

$filehandle = fopen ($table.".sql", "a+");

fwrite($filehandle, $createStr."\r\n");

//    mysql_query($createStr);

$sql="SELECT COUNT(*) AS num

FROM $slctTable where CEILING(id / 1000)=$i";

$result =mysql_query($sql);

$row = mysql_fetch_row($result);

$num = (int)$row[0];

$maxNum= ceil((int)$num / $per);

for($j=0;$j

$lmt = $j * $per;

if($lmt <= (int)$num){

$sql = "select * from $slctTable where CEILING(id / 1000)=$i order by id limit $lmt,500";

//             fwrite($filehandle, $sql."");

$inStr="REPLACE INTO ".$table." ".$fieldStr." VALUES";

//            fwrite($filehandle, $inStr."\r\n");

$result = mysql_query($sql);

if($result){

$value="";

while ($row = mysql_fetch_row($result)){

$value.= "(";

for($n=0;$n < $filedNum;$n++){

$value.="'".addslashes($row[$n])."',";

}

if(strlen($value)>2){

$value = substr($value,0,-1)."),\r\n";

}

}

if(strlen($value)>2){

$value = substr($value,0,-3)."";

}

$inStr = $inStr.$value;

//                 echo $inStr."\r\n";

//                 mysql_query($inStr);

fwrite($filehandle, $inStr);

}

//             fclose($filehandle);

//             exit;

}else{

break;

}

}

fclose($filehandle);

}

?>