【www.bbyears.com--php常用代码】
代码如下 public function import_users(){
$members = $user = $user_contact = $user_ext = $user_last_active = $user_tag = array();
$mid = 10000;
$page_size = 3000;
foreach(array(1,2,3,4,5) as $i)
{
//连接db
$this->crm_db = $this->get_crm_db_handle();
$this->db = get_db("default");
$follow_table = "follows_" . $i;
$total = $this->db->count_all_results($follow_table);
$page_total = ceil($total/$page_size);
for($page = 1; $page < $page_total; $page++)
{
//连接db
$this->crm_db = $this->get_crm_db_handle();
$this->db = get_db("default");
//获取会员
$members = $this->db->select("*")
->from($follow_table)
->limit($page_size,($page-1)*$page_size)
->order_by("follow_id", "asc")
->get()
->result_array();
//开始导入
foreach($members as $m)
{
//导入到user表
if(empty($m["mid"]))
{
$user = array(
"mid" => $mid,
"uid" =>$m["uid"],
"is_member" => 0,
"identify" => $m["telephone"],
"name" => $m["name"],
"nick" => $m["mark_name"],
"gender" => $m["sex"],
"update_time" => date("Y-m-d H:i:s", $m["create_time"]),
"status" => 0
);
$this->crm_db->insert("user", $user);
//导入到user_contact表
if($m["qq"] OR $m["email"] OR $m["telephone"])
{
if($m["qq"]) //2
{
$user_contact[] = array("uid"=>$m["uid"],"mid"=>$mid,"contact_id"=>2,"value"=>$m["qq"]);
}
if($m["email"]) //3
{
$user_contact[] =array("uid"=>$m["uid"],"mid"=>$mid,"contact_id"=>3,"value"=>$m["email"]);
}
if($m["telephone"]) //1
{
$user_contact[] = array("uid"=>$m["uid"],"mid"=>$mid,"contact_id"=>1,"value"=>$m["telephone"]);
}
if($user_contact)
{
//$this->crm_db->insert_batch("user_contact", $user_contact);
foreach($user_contact as $uc)
{
$this->crm_db->insert("user_contact", $uc);
}
}
}
//导入到user_ext表
if($m["avatar"] OR $m["city"] OR $m["province"] OR $m["country"])
{
$user_ext = array("uid"=>$m["uid"],"mid"=>$mid,"avatar"=>$m["avatar"], "country"=>$m["country"],"province"=>$m["province"],"city"=>$m["city"]);
$this->crm_db->insert("user_ext", $user_ext);
}
//导入到user_last_active表
if($m["last_talktime"] AND $m["talk_cnt"])
{
$user_last_active = array("uid"=>$m["uid"],"mid"=>$mid,"last_active_time"=>date("Y-m-d H:i:s",$m["last_talktime"]), "active_count"=>$m["talk_cnt"]);
$this->crm_db->insert("user_last_active", $user_last_active);
}
//导入到user_tag表
$user_tags = $this->db->select("uid, gid as tag_id")->where(array("uid"=>$m["uid"], "follow_id"=>$m["follow_id"]))->get("fcate")->result_array();
if($user_tags)
{
foreach ($user_tags as $ut)
{
$ut["mid"] = $mid;
$this->crm_db->insert("user_tag", $ut);
}
}
//导入到platform表
$this->crm_db->insert("platform", array("uid"=>$m["uid"],"mid"=>$mid,"platform"=>$m["platform"],"platform_user_id"=>$m["unique"],"create_time"=>date("Y-m-d H:i:s", $m["create_time"])));
www.111cn.net
$mid++;
$user = $user_contact = $user_ext = $user_last_active = $user_tag = array();
}
}
//关闭数据库
$this->crm_db->close();
$this->db->close();
usleep(100000);
}
}
}
CI db会将所有的查询sql和和sql执行时间保存下来，为了debug。关键是这个$save_queries为TRUE，在config中并没有关闭开关。
再看看我上面的执行脚本，400万的数据，每条记录会执行7条sql，也就是说，如果要执行完这个脚本，2800万条sql记录会被保存在内存中，MB，10G内存都不够。
解决办法：
当你执行大数量的db操作时，记得：
$this->db->save_queries = FALSE;