<?php
/**
* Created by PhpStorm.
* User: David
* Date: 2018/9/5
* Time: 16:14
*/
/**
*select * from order_info201808 limit 1;(order_sn)
*select * from lf_pos_indepot201808 limit 1;(externOrderKey)
*select * from pandora_order_log201808 limit 1;(tid)
*订单付款时间 order_info201808.pay_time
*推送LF时间 pandora_order_log201808.interface_time
*订单发货时间 lf_pos_indepot201808.effectiveDate
*/
header("Content-type:test/html;charsert=utf8");
//数据同步脚本方便数据库表的增删改查
class DATA_TB{
public function __construct(){
$this->db = new mysqli('xxx.xxx.xx','user','pwd','dbname');
if(mysqli_connect_errno()){
echo '数据库连接错误,错误信息:'.mysqli_connect_error();
exit();
}
// set_time_limit(0);
// ini_set("max_execution_time", "200000");
// $this->db->set_charset('utf8');
}
public function to_array($sql){
$result = $this->db->query($sql);
while ($row = $result->fetch_assoc()){
$res[] = $row;
}
return $res;
}
//下面方法可根据需求自行修改。
public function tb_data(){
// $sql = '
// SELECT O.order_sn,from_unixtime(O.pay_time, \'%Y-%m-%d %H:%i:%S\') as pay_time,T.interface_time as lf_time,from_unixtime(L.effectiveDate, \'%Y-%m-%d %H:%i:%S\') as shipping_time,
// FORMAT((L.effectiveDate - O.pay_time)/3600,2) as time_diff1,FORMAT((L.effectiveDate - unix_timestamp(T.interface_time))/3600,2) AS time_diff2 FROM
// order_info201808 as O INNER JOIN ((SELECT DISTINCT externOrderKey,effectiveDate FROM lf_pos_indepot201808) as L ) ON O.order_sn = L.externOrderKey
//INNER JOIN test02 AS T on T.order_sn = O.order_sn';
// 增加条件sql (order_info.order_note<>'换货追单' or order_info.order_note is null)
$sql = 'SELECT O.order_sn,from_unixtime(O.pay_time, \'%Y-%m-%d %H:%i:%S\') as pay_time,T.interface_time as lf_time,from_unixtime(L.effectiveDate, \'%Y-%m-%d %H:%i:%S\') as shipping_time,
FORMAT((L.effectiveDate - O.pay_time)/3600,2) as time_diff1,FORMAT((L.effectiveDate - unix_timestamp(T.interface_time))/3600,2) AS time_diff2 FROM
order_info201808 as O INNER JOIN ((SELECT DISTINCT externOrderKey,effectiveDate FROM lf_pos_indepot201808) as L ) ON O.order_sn = L.externOrderKey
INNER JOIN test02 AS T on T.order_sn = O.order_sn WHERE O.order_note <>\'换货追单\' or O.order_note is null';
$result = $this->to_array($sql);
print_r('终止insert sql 数据');die();
$str = '';
foreach($result as $k=>$v){
$str .= "( \"".$v['order_sn']."\",\"".$v['pay_time']."\",\"".$v['lf_time']."\",\"".$v['shipping_time']."\"
,\"".$v['time_diff1']."\",\"".$v['time_diff2']."\"),";
if($k % 1000 ==0){
$str2 = rtrim($str,',');
$insert_sql = 'insert into test01 (order_sn,pay_time,lf_time,shipping_time,time_diff1,time_diff2) values '.$str2;
$this->db->query($insert_sql);
$str ='';
}
}
if($str){
$str2 = rtrim($str,',');
$insert_sql = 'insert into test01 (order_sn,pay_time,lf_time,shipping_time,time_diff1,time_diff2) values '.$str2;
$this->db->query($insert_sql);
}
}
}
$obj = new DATA_TB();
$obj->tb_data();
?>
php 操作mysql 批量插入数据到表
最新推荐文章于 2023-04-26 12:29:40 发布