Я использую скрипт в PHP для вставки больших данных в цикле в MySQL
каждый цикл выбирает 10000 строк из одной таблицы и вставляет эти строки в другую таблицу
Но в соответствии с моей текущей конфигурацией, MySQL отключается после первого цикла запросов
означает, что он гаснет после вставки первых 10000 строк
Я использую Zend Server в Ubuntu и MySQL
я пытался изменить my.cnf и php.ini
но безуспешно
Спасибо заранее тем, кто предоставит решение.
<?php
include('db.php');
$conn = mysql_connect($SERVER, $USER, $PASSWORD) or die('cant connect to server'.mysql_error());
mysql_select_db($DATABASE);
//ps_orders
// truncate table ps_orders;
//select * from ps_customer order by id_customer desc limit 0,2;
//select * from ps_cart order by date_add desc limit 0,1
// to fecth cart from sss database
//where id_customer >56020"$conn1 = mysql_connect($SERVER,$NEW_USER,$NEW_PASSWORD);
mysql_select_db($DATABASE_NewDB,$conn1);
$sql="truncate table `ps_orders`";
if(mysql_query($sql,$conn1))
{
echo "table truncated";
}
else
{
mysql_errno();
}
$page =0;
$total_rows = 0 ;
while(true)
{
$limit1 = ($page*9600)+1;
$limit2 = ($page*9600)+9600;
$queryContactID="SELECT id_order, reference, id_shop_group, id_shop, id_carrier, id_lang, id_customer, id_cart, id_currency, id_address_delivery, id_address_invoice, current_state, secure_key, payment, conversion_rate, module, recyclable, gift, gift_message, shipping_number, total_discounts, total_discounts_tax_incl, total_discounts_tax_excl, total_paid, total_paid_tax_incl, total_paid_tax_excl, total_paid_real, total_products, total_products_wt, total_shipping, total_shipping_tax_incl, total_shipping_tax_excl, carrier_tax_rate, total_wrapping, total_wrapping_tax_incl, total_wrapping_tax_excl, invoice_number, delivery_number, invoice_date, delivery_date, valid, date_add, date_upd, courier_complete from $DATABASE.ps_orders limit $limit1,$limit2";
$result = mysql_query($queryContactID) or die('query fetch sss address id error'.mysql_error());
if(mysql_num_rows($result)>0){
$carrier_query_arr = array();
while($row = mysql_fetch_object($result)){
$id_order = $row->id_order;
$reference = $row->reference;
$id_shop_group = $row->id_shop_group;
$id_shop = $row->id_shop;
$id_carrier = $row->id_carrier;
$id_lang = $row->id_lang;
$id_customer = $row->id_customer;
$id_cart =$row->id_cart;
$id_currency =$row->id_currency;
$id_address_delivery =$row->id_address_delivery;
$id_address_invoice = $row->id_address_invoice;
$current_state =$row->current_state;
$secure_key =$row->secure_key ;
$payment =$row->payment;
$conversion_rate =$row->conversion_rate;
$module =$row->module;
$recyclable =$row->recyclable;
$gift =$row->gift;
$gift_message =$row->gift_message;
$shipping_number =$row->shipping_number;
$total_discounts =addslashes(trim($row->total_discounts));
$total_discounts_tax_incl =addslashes(trim($row->total_discounts_tax_incl));
$total_discounts_tax_excl =addslashes(trim($row->total_discounts_tax_excl));
$total_paid =addslashes(trim($row->total_paid));
$total_paid_tax_incl =addslashes(trim($row->total_paid_tax_incl));
$total_paid_tax_excl =addslashes(trim($row->total_paid_tax_excl));
$total_paid_real =addslashes(trim($row->total_paid_real));
$total_products =addslashes(trim($row->total_products));
$total_products_wt =addslashes(trim($row->total_products_wt));
$total_shipping =addslashes(trim($row->total_shipping));
$total_shipping_tax_incl =addslashes(trim($row->total_shipping_tax_incl));
$total_shipping_tax_excl =addslashes(trim($row->total_shipping_tax_excl));
$carrier_tax_rate =addslashes(trim($row->carrier_tax_rate));
$total_wrapping =addslashes(trim($row->total_wrapping));
$total_wrapping_tax_incl =addslashes(trim($row->total_wrapping_tax_incl));
$total_wrapping_tax_excl =addslashes(trim($row->total_wrapping_tax_excl));
$invoice_number =addslashes(trim($row->invoice_number));
$delivery_number =addslashes(trim($row->delivery_number));
$invoice_date =addslashes(trim($row->invoice_date));
$delivery_date =addslashes(trim($row->delivery_date));
$valid =addslashes(trim($row->valid));
$date_add =addslashes(trim($row->date_add));
$date_upd =addslashes(trim($row->date_upd));
$courier_complete_count = addslashes(trim($row->courier_complete));
$cart_query_arr[] = "('".$id_order."','".$reference."','".$id_shop_group."','".$id_shop."','".$id_carrier."','".$id_lang."','".$id_customer."','".$id_cart."','".$id_currency."','".$id_address_delivery."','".$id_address_invoice."','".$current_state."','".$secure_key."','".$payment."','".$conversion_rate."','".$module."','".$recyclable."','".$gift."','".$gift_message."','".$shipping_number."','".$total_discounts."','".$total_discounts_tax_incl."','".$total_discounts_tax_excl."','".$total_paid."','".$total_paid_tax_incl."','".$total_paid_tax_excl."','".$total_paid_real."','".$total_products."','".$total_products_wt."','".$total_shipping."','".$total_shipping_tax_incl."','".$total_shipping_tax_excl."','".$carrier_tax_rate."','".$total_wrapping."','".$total_wrapping_tax_incl."','".$total_wrapping_tax_excl."','".$invoice_number."','".$delivery_number."','".$invoice_date."','".$delivery_date."','".$valid."','".$date_add."','".$date_upd."')";
++$total_rows;
}
$cart_single_insert = "INSERT into $DATABASE_NewDB.ps_orders(id_order, reference, id_shop_group, id_shop, id_carrier, id_lang, id_customer, id_cart, id_currency, id_address_delivery, id_address_invoice, current_state, secure_key, payment, conversion_rate, module, recyclable, gift, gift_message, shipping_number, total_discounts, total_discounts_tax_incl, total_discounts_tax_excl, total_paid, total_paid_tax_incl, total_paid_tax_excl, total_paid_real, total_products, total_products_wt, total_shipping, total_shipping_tax_incl, total_shipping_tax_excl, carrier_tax_rate, total_wrapping, total_wrapping_tax_incl, total_wrapping_tax_excl, invoice_number, delivery_number, invoice_date, delivery_date, valid, date_add, date_upd) values ";
$email_value = implode(',',$cart_query_arr);
$cart_single_insert .= ' '.$email_value;
$cart_query_arr = array();
mysql_query($cart_single_insert,$conn1);
++$page;
}
else
{
echo "queries are done";
break;
}
}
// first while loop ends here
// $i=0;
// if(mysql_num_rows($result)>0){
// $j=0;
// $total_address = 0;
// $carrier_query_arr = array();
// /* Start of While 1 */
// while($row = mysql_fetch_object($result)){
// $id_order = $row->id_order;
// $reference = $row->reference;
// $id_shop_group = $row->id_shop_group;
// $id_shop = $row->id_shop;
// $id_carrier = $row->id_carrier;
// $id_lang = $row->id_lang;
// $id_customer = $row->id_customer;
// $id_cart =$row->id_cart;
// $id_currency =$row->id_currency;
// $id_address_delivery =$row->id_address_delivery;
// $id_address_invoice = $row->id_address_invoice;
// $current_state =$row->current_state;
// $secure_key =$row->secure_key ;
// $payment =$row->payment;
// $conversion_rate =$row->conversion_rate;
// $module =$row->module;
// $recyclable =$row->recyclable;
// $gift =$row->gift;
// $gift_message =$row->gift_message;
// $shipping_number =$row->shipping_number;
// $total_discounts =addslashes(trim($row->total_discounts));
// $total_discounts_tax_incl =addslashes(trim($row->total_discounts_tax_incl));
// $total_discounts_tax_excl =addslashes(trim($row->total_discounts_tax_excl));
// $total_paid =addslashes(trim($row->total_paid));
// $total_paid_tax_incl =addslashes(trim($row->total_paid_tax_incl));
// $total_paid_tax_excl =addslashes(trim($row->total_paid_tax_excl));
// $total_paid_real =addslashes(trim($row->total_paid_real));
// $total_products =addslashes(trim($row->total_products));
// $total_products_wt =addslashes(trim($row->total_products_wt));
// $total_shipping =addslashes(trim($row->total_shipping));
// $total_shipping_tax_incl =addslashes(trim($row->total_shipping_tax_incl));
// $total_shipping_tax_excl =addslashes(trim($row->total_shipping_tax_excl));
// $carrier_tax_rate =addslashes(trim($row->carrier_tax_rate));
// $total_wrapping =addslashes(trim($row->total_wrapping));
// $total_wrapping_tax_incl =addslashes(trim($row->total_wrapping_tax_incl));
// $total_wrapping_tax_excl =addslashes(trim($row->total_wrapping_tax_excl));
// $invoice_number =addslashes(trim($row->invoice_number));
// $delivery_number =addslashes(trim($row->delivery_number));
// $invoice_date =addslashes(trim($row->invoice_date));
// $delivery_date =addslashes(trim($row->delivery_date));
// $valid =addslashes(trim($row->valid));
// $date_add =addslashes(trim($row->date_add));
// $date_upd =addslashes(trim($row->date_upd));
// $courier_complete_count = addslashes(trim($row->courier_complete));
// $cart_query_arr[] = "('".$id_order."','".$reference."','".$id_shop_group."','".$id_shop."','".$id_carrier."','".$id_lang."','".$id_customer."','".$id_cart."','".$id_currency."','".$id_address_delivery."','".$id_address_invoice."','".$current_state."','".$secure_key."','".$payment."','".$conversion_rate."','".$module."','".$recyclable."','".$gift."','".$gift_message."','".$shipping_number."','".$total_discounts."','".$total_discounts_tax_incl."','".$total_discounts_tax_excl."','".$total_paid."','".$total_paid_tax_incl."','".$total_paid_tax_excl."','".$total_paid_real."','".$total_products."','".$total_products_wt."','".$total_shipping."','".$total_shipping_tax_incl."','".$total_shipping_tax_excl."','".$carrier_tax_rate."','".$total_wrapping."','".$total_wrapping_tax_incl."','".$total_wrapping_tax_excl."','".$invoice_number."','".$delivery_number."','".$invoice_date."','".$delivery_date."','".$valid."','".$date_add."','".$date_upd."')";
// $j++;
// ++$total_address;
// if($total_address > 5000){
// $total_address = 0;
// $cart_single_insert = "INSERT into $DATABASE_NewDB.ps_orders(id_order, reference, id_shop_group, id_shop, id_carrier, id_lang, id_customer, id_cart, id_currency, id_address_delivery, id_address_invoice, current_state, secure_key, payment, conversion_rate, module, recyclable, gift, gift_message, shipping_number, total_discounts, total_discounts_tax_incl, total_discounts_tax_excl, total_paid, total_paid_tax_incl, total_paid_tax_excl, total_paid_real, total_products, total_products_wt, total_shipping, total_shipping_tax_incl, total_shipping_tax_excl, carrier_tax_rate, total_wrapping, total_wrapping_tax_incl, total_wrapping_tax_excl, invoice_number, delivery_number, invoice_date, delivery_date, valid, date_add, date_upd) values ";
// $email_value = implode(',',$cart_query_arr);
// $cart_single_insert .= ' '.$email_value;
// $cart_query_arr = array();
// mysql_query($cart_single_insert,$conn1);
// }
// print "Total records ".$j;
// }
// }/* End of While (1) */
?>
что я пробовал до сих пор:
в php.ini у меня есть изменения max_execution_time на 60000
Вы можете вставить несколько строк одновременно. Попробуйте вставить так:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
Итерируйте свой цикл и создайте запрос, подобный этому, и вставляйте после каждых 2К или 4К.
Для получения дополнительной информации: http://dev.mysql.com/doc/refman/5.6/en/insert.html
Других решений пока нет …