php 操作mysql 批量插入数据到表

最新推荐文章于 2023-06-17 15:52:02 发布

yangpjit

最新推荐文章于 2023-06-17 15:52:02 发布

阅读量1.6k

点赞数

本文链接：https://blog.csdn.net/yangpit/article/details/82494430

版权

<?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();
?>