用php写脚本编写数据迁移程序实战案例
背景：
客户之前的一个商城是tpshop的开源商城系统，由于php的开源商城极易被挂马，然后篡改首页。所以给客户新做了一套商城。新商城上线需要迁移旧系统的会员，会员的账户余额变更记录，订单以及订单详情信息。由于关联关系都是自增的id，所以预计是先把会员都迁移过来，然后会员的在新数据库的新id，作为关联关系，在会员订单以及余额变更记录里面使用的是新插入到数据的会员的id。
新商城使用thinkphp5开发，把tpshop的几个有关系的表，导出来（tp_users tp_account_log tp_order tp_order_goods tp_recharge_log tp_delivery_doc tp_region），然后导入到新的商城数据库，然后通过thinkphp的chunk分块操作数据。需要命令行方式启动，http方式web服务器会超时，启动命令,cli方式启动。
php index.php qianyi/qianyi
命令行方式启动，php index.php qianyi/qianyi 入口文件下面 控制器/下面的迁移方法
具体的方法我记录到下面：
public function qianyi()
{
//$list=Db::table('tp_users')->select();
//print_r($list);die;
$clientkeynum="0724323738B03A3209BA4AE5570B87F2";
echo "欢迎来到数据迁移程序！\r\n\r\n";
$cishu=0;
//闭包开始
Db::table('tp_users')->where("1=1")->chunk(100, function ($users) use ($clientkeynum, &$cishu) {
//循环之前
$start_time=msectime();
$num1=0;
$erro1=0;
$cishu++;
foreach ($users as $user) {
//迁移会员数据
if ($user['mobile']) {
$name=$user['mobile'];
} else {
$name=$user['email'];
}
$member['name']=$name;
$member['phone']=$user['mobile'];
$member['email']=$user['email'];
$member['integral']=$user['user_money'];
$member['password']=$user['password'];
$member['add_time']=$user['reg_time'];
$member['state']="1";
$member['reg_from']="4";
$member['remark']="旧商城导入会员";
$member['nickname']=$user['nickname'];
$member['realname']="";
$member['keynum']=create_guid();
//0是平台 还有null还有空的未知 还有一个其他的为崇行的
if ($user['clientkeynum']=='0') {
$merchantkeynum="AE1911FD56D9BFDBBAD4CFD08D37D479";
} elseif ($user['clientkeynum']!='') {
$merchantkeynum="29F504F1DC296875A9DB3025B1ED9FBB";
} else {
//未知放入主站
$merchantkeynum="AE1911FD56D9BFDBBAD4CFD08D37D479";
}
$member['merchantkeynum']=$merchantkeynum;
$member['clientkeynum']=$clientkeynum;
$member['remark1']=$user['paypwd'];
$member['remark2']=$user['user_id'];
$member['remark3']=$user['user_money'];
//查询是否存在不存则新增
$uid=$user['user_id'];
$member_info=Db::table('client_merchant_member')->where("remark2='$uid'")->find();
//echo Db::table('client_merchant_member')->getLastSql();
if (empty($member_info)) {
$insert_id=Db::table('client_merchant_member')->insertGetId($member);
//echo Db::table('client_merchant_member')->getLastSql();
if ($insert_id) {
//新增会员成功之后，迁移会员积分变更记录
$member_log_list=Db::table('tp_account_log')->where("user_id='$uid'")->select();
foreach ($member_log_list as $k1=>$v1) {
if ($v1['user_money']>0) {
$integral=$v1['user_money'];
$leixing=1;
} else {
$integral=abs($v1['user_money']);
$leixing=0;
}
$member_log['integral']=$integral;
$member_log['type']="-1";
$member_log['time']=$v1['change_time'];
$member_log['mid']=$insert_id;
$member_log['leixing']=$leixing;
$member_log['desc']=$v1['desc']."订单号".$v1['order_sn'];
$member_log['merchantkeynum']=$merchantkeynum;
$member_log['clientkeynum']=$clientkeynum;
$member_log['in_remark']="订单id".$v1['order_id'];
Db::table('client_merchant_member_integral_change_log')->insertGetId($member_log);
}
//会员变更日志结束
//迁移订单开始
$order_list=Db::table('tp_order')->where("user_id='$uid'")->select();
foreach ($order_list as $k2=>$v2) {
//获取old的order_id
$old_order_id=$v2['order_id'];
$order_info['order_sn']=$v2['order_sn'];
$order_info['mid']=$insert_id;
//订单状态修正
if($v2['order_status']=='0'){
$order_status="0";
}else if ($v2['order_status']=='1'){
$order_status="1";
}else if ($v2['order_status']=='2'){
$order_status="3";
}else if ($v2['order_status']=='3'){
$order_status="5";
}else if ($v2['order_status']=='4'){
$order_status="4";
}else if ($v2['order_status']=='5'){
$order_status="5";
}
if($v2['shipping_status']=='0'){
$shipping_status="0";
}else if ($v2['shipping_status']=='1'){
$shipping_status="1";
}else if ($v2['shipping_status']=='2'){
$shipping_status="0";
}
if($v2['pay_status']=='0'){
$pay_status="0";
}else if ($v2['pay_status']=='1'){
$pay_status="1";
}else if ($v2['pay_status']=='2'){
$pay_status="0";
}else if ($v2['pay_status']=='3'){
$pay_status="3";
}else if ($v2['pay_status']=='4'){
$pay_status="3";
}
$order_info['order_status']=$order_status;
$order_info['shipping_status']=$shipping_status;
$order_info['pay_status']=$pay_status;
$order_info['consignee']=$v2['consignee'];
$order_info['phone']=$v2['mobile'];
//处理中文名称 town 没用
$address_arr=$this->get_address_name($v2['province'],$v2['city'],$v2['district']);
$order_info['area']=$address_arr['dname']?$address_arr['dname']:'未知';
$order_info['province']=$address_arr['pname']?$address_arr['pname']:'未知';
$order_info['city']=$address_arr['cname']?$address_arr['cname']:'未知';
$order_info['detail_address']=$v2['address'];
$order_info['shipping_code']=$v2['shipping_code'];
$order_info['shipping_name']=$v2['shipping_name'];
$order_info['shipping_num']=Db::table('tp_delivery_doc')->where("order_id='$old_order_id'")->order("id desc")->value("invoice_no");
if ($v2['pay_code']=='weixin') {
$pay_method="wx";
} elseif ($v2['pay_code']=='alipayMobile') {
$pay_method="zfb";
} elseif ($v2['pay_code']=='alipay') {
$pay_method="zfb";
} elseif ($v2['pay_code']=='' && $v2['pay_name']=='余额支付') {
$pay_method="yue";
} else {
$pay_method="";
}
$order_info['pay_method']=$pay_method;
$order_info['goods_total_money']=$v2['goods_price'];
$order_info['shipping_fee']=$v2['shipping_price'];
$order_info['member_money']=$v2['user_money'];
$order_info['online_money']=$v2['order_amount'];
$order_info['order_total_money']=$v2['total_amount'];
$order_info['all_money']=$v2['total_amount'];
$order_info['add_time']=$v2['add_time'];
$order_info['shipping_time']=$v2['shipping_time'];
$order_info['confirm_time']=$v2['confirm_time'];
$order_info['pay_time']=$v2['pay_time'];
$order_info['remark']=$v2['user_note'];
$order_info['admin_remark']=$v2['admin_note'];
//都改成已打印吧，因为我看旧商城都是未打印，
//$order_info['is_print']=$v2['is_print'];
$order_info['is_print']=1;
$order_info['print_time']=$v2['print_time'];
$order_info['merchantkeynum']=$merchantkeynum;
$order_info['clientkeynum']=$clientkeynum;
$order_info['keynum']=create_guid();
//通过字表获取产品信息
$goodsinfo_arr=$this->get_order_goodsinfo($old_order_id);
$order_info['goodssku']=$goodsinfo_arr['goodssku'];
$order_info['goodsinfo']=$goodsinfo_arr['goodsinfo'];
$insert_order_id=Db::table('client_order_info')->insertGetId($order_info);
if ($insert_order_id) {
//订单字表开始
//获取订单字表列表
$order_goods_list=Db::table('tp_order_goods')->where("order_id='$old_order_id'")->select();
foreach ($order_goods_list as $k3=>$v3) {
$order_goods['order_sn']=$v2['order_sn'];
$order_goods['goodsid']=$v3['goods_id'];
$order_goods['goodssku']=$v3['goods_sn'];
$order_goods['goodsname']=$v3['goods_name'];
$order_goods['goodsintegral']=$v3['goods_price'];
$order_goods['goodsimg']="";
$order_goods['number']=$v3['goods_num'];
$order_goods['order_id']=$insert_order_id;
$order_goods['order_keynum']=$order_info['keynum'];
$order_goods['clientkeynum']=$clientkeynum;
$order_goods['merchantkeynum']=$merchantkeynum;
Db::table('client_order_orderdetail')->insertGetId($order_goods);
}
//订单字表结束
}
}
//迁移订单结束
//卡号充值日志记录开始
$recharge_log_list=Db::table('tp_recharge_log')->where("uid='$uid'")->select();
foreach ($recharge_log_list as $k4=>$v4) {
$card_uselog['cardnum']=$v4['reacher_card'];
$card_uselog['cardpwd']="";
$card_uselog['time']=$v4['addtime'];
$card_uselog['uid']=$insert_id;
$card_uselog['content']=$v4['reacher_card']."充值到会员".v4['recharge']."充值前".$v4['old_point']."充值后".$v4['new_point'];
$card_uselog['integral']=$v4['recharge'];
$card_uselog['old_integral']=$v4['old_point'];
$card_uselog['new_integral']=$v4['new_point'];
$card_uselog['merchantkeynum']=$merchantkeynum;
$card_uselog['clientkeynum']=$clientkeynum;
$card_uselog['in_remark']="迁移数据，不可逆操作";
Db::table('client_merchant_kace_card_uselog')->insertGetId($card_uselog);
}
//卡号充值日志记录结束
$num1++;
} else {
$erro++;
}
} else {
//里面是修改
}
}
$end_time=msectime();
//循环完毕
echo $last_msg= $cishu."次，新增会员成功".$num1."个！失败".$erro1."个，耗时".($end_time-$start_time)."毫秒\r\n";
logRes($last_msg, "qianyi");
});
//闭包结束
echo $last_msg1="~^o^~整体都执行完毕了~^o^~\r\n";
logRes($last_msg1, "qianyi");
}
//获取订单商品信息
public function get_order_goodsinfo($old_order_id)
{
//获取订单字表列表
$order_goods_list=Db::table('tp_order_goods')->where("order_id='$old_order_id'")->select();
$rt=array();
if(empty($order_goods_list)){
$rt['goodssku']="";
$rt['goodsinfo']="";
return $rt;
}
foreach ($order_goods_list as $k3=>$v3) {
$goodssku_str.=$v3['goods_sn']."(".$v3['goods_num'].")+";
$goodsinfo_str.=$v3['goods_name']."(".$v3['goods_num'].")+";
}
$rt['goodssku']=substr($goodssku_str,0,-1);
$rt['goodsinfo']=substr($goodsinfo_str,0,-1);
return $rt;
}
//根据id获取地址中文名称
public function get_address_name($p=0,$c=0,$d=0){
$pname = Db::table('tp_region')->where("id='$p'")->value("name");
$cname = Db::table('tp_region')->where("id='$c'")->value("name");
$dname = Db::table('tp_region')->where("id='$d'")->value("name");
$rt['pname']=$pname;
$rt['cname']=$cname;
$rt['dname']=$dname;
return $rt;
}
版权声明：若无特殊注明，本文皆为《菜鸟站长》原创，转载请保留文章出处。
本文链接：用php写脚本编写数据迁移程序实战案例 - http://www.wlphp.com/?post=340
