PHP自动分表程序
http://blog.zol.com.cn/2210/article_2209590.html
/**
* 分表程序
*
* @author Shen Xi
[email protected]
* @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<$maxNum;$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);
}
?>
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。