php代码：处理大量mysql表记录

有一mysql表user(user_id)，共100万记录：

 

可用procedure来插入100万:

 

CREATE PROCEDURE `sp_insert_mytest`(IN number int(10))
BEGIN
 declare i int(10);
 set i = 1;
 WHILE i <= number DO
    if mod(i,2000)=1 then
      set @sqltext =concat('(''',ceil(1000000000*rand()),''',',mod(ceil(10*rand()),2),')');
    elseif mod(i,2000)=0 then
      set @sqltext=concat(@sqltext,',(''',ceil(1000000000*rand()),')');
      set @sqltext=concat('insert into mytest(user_id) values',@sqltext);
      prepare stmt from @sqltext;
      execute stmt;
      DEALLOCATE PREPARE stmt;
      set @sqltext='';
    else
      set @sqltext=concat(@sqltext,',(''',ceil(1000000000*rand()),')');
    end if;
      set i = i + 1;
 END WHILE;
 if @sqltext<>'' then
      set @sqltext=concat('insert into mytest(user_id) values',@sqltext);
      prepare stmt from @sqltext;
      execute stmt;
      DEALLOCATE PREPARE stmt;
      set @sqltext='';
 end if;
END

再求每一个user_id同本表中的其它50个user_id随机配对，将结果插入一个新表，user_new(user_id,user_friend_id)，共5000万个记录。

 

if ($conn=@mysql_connect("localhost","user","password"))
{
mysql_select_db("test");

$i = 0;
while ($i <= 1000000){

$user_data_array =array();

$sql = "select user_id from users limit ";
$sql.= "$i";
$sql.= ",1000;";

echo $sql;

$query = mysql_query($sql);

while ($row = mysql_fetch_assoc($query))
{
$user_data_array[] = $row['user_id'];
}

$new_array = array();

foreach ($user_data_array as $uid)
{
$new_array[$uid] = array();
while (count($new_array[$uid])<50)
  {
  $rand_userid_key = array_rand($user_data_array);
  $rand_userid = $user_data_array[$rand_userid_key];
  if ($rand_userid!=$uid && !in_array($rand_userid, $new_array[$uid]))
   {
   $new_array[$uid][] = $rand_userid;
   }
  }
}

$str = "insert into user_new(user_id, user_friend_id) values";
foreach ($new_array as $uid=>$v)
{
foreach ($v as $partnerid)
{
$str .= "($uid, $partnerid),";
}
}

$sql_insert = substr($str,0, -1);


$hd = fopen('/tmp/data.txt', 'a+');

fwrite($hd, $sql_insert);


$i=$i+1000;

}
fcolse($hd);
mysql_close();

}
else
{
printf("

can't connect DB! %s

\n",
mysql_error());

}

?>